SQL Server Primary Key

SQL Server Primary key is a single field or combination of fields that is used to uniquely define a record. Any field of a primary key cannot contain a null value. A table can have only one primary key.

You can define a primary key either in a CREATE TABLE statement or an ALTER TABLE statement.

Create Primary Key Using CREATE TABLE Statement

Syntax:

CREATE TABLE table_name  

(   

  column1 datatype [ NULL | NOT NULL ] [ PRIMARY KEY ],  

  column2 datatype [ NULL | NOT NULL ],  

  ...  

);

Or

CREATE TABLE table_name  

(   

  column1 datatype [ NULL | NOT NULL ],  

  column2 datatype [ NULL | NOT NULL ],  

  ...  

  CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n)  

);

Example:

Create a table “cricketers” where “cricketer_id” is a primary key.

CREATE TABLE cricketers  

( cricketer_id INT PRIMARY KEY,  

  last_name VARCHAR(50) NOT NULL,  

  first_name VARCHAR(50) NOT NULL,  

  salary MONEY  

);

Output:

You can verify that created table by using SELECT command:

SELECT *   

FROM aaa.[dbo].[cricketers]

Output:

Here cricketer_id is the primary key.

You can also create a table with primary key by using the second syntax:

Example:

CREATE TABLE cricketers2  

( cricketer_id INT,  

  last_name VARCHAR(50) NOT NULL,  

  first_name VARCHAR(50) NOT NULL,  

  salary MONEY,  

  CONSTRAINT cricketers2_pk PRIMARY KEY (cricketer_id)  

);

Output:

You can verify that created table by using SELECT command:

SELECT *   

FROM .[dbo].[cricketers2]

Output:

Create a primary key in SQL Server where the primary key is more than one field:

Example:

CREATE TABLE cricketers3  

( last_name VARCHAR(50) NOT NULL,  

  first_name VARCHAR(50) NOT NULL,  

  salary MONEY,  

  CONSTRAINT cricketers3_pk PRIMARY KEY (last_name, first_name)  

);

Output:

You can verify that created table by using SELECT command:

SELECT *   

FROM .[dbo].[cricketers3]

Output:

Here, last_name and first_name both is primary key.

Create Primary Key Using ALTER TABLE statement

You can use an ALTER TABLE statement to create a primary key only on the column or columns that are already defined as NOT NULL.

If a column of a table has NULL values, you cannot add a primary key without dropping and recreating the table.

Syntax:

ALTER TABLE table_name  

ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);

Example:

Create a primary key “cricketer_id” in the table “cricketers2”.

ALTER TABLE .[dbo].[cricketers2]  

ADD CONSTRAINT cricketers2_pk PRIMARY KEY (cricketer_id);

Output:

It will create a primary key “cricketer_id” in the “cricketers2” table.