How to Create a PRIMARY KEY in SQL Server

A primary key is a column or a group of columns that uniquely identifies each record in a table. You can create a primary key for a table by using the PRIMARY KEY constraint. There are some features of Primary Key:

• It is a column or a group of columns that uniquely identifies each record/row in a table.
• One table can contains only one primary key.
• None of the columns that are part of the primary key can contain a null value.
• Column or group of columns that are part of the primary key are unique value.
• A primary key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement in T-SQL.
• MS SQL Server automatically sets the NOT NULL constraint for all the primary key columns if the NOT NULL constraint not specified for these columns as a column level constraint.
• All columns that participate in the Primary Key must be defined as NOT NULL before executing ALTER TABLE statement.
• SQL Server also automatically creates a unique clustered index (or a non-clustered index if specified as such) when you create a primary key.

Generally there are three methods to create Primary Key in MS SQL Server (T-SQL):

Method-1:
If the primary key consists of only one column, you can define use PRIMARY KEY constraint as a column constraint:

CREATE TABLE table_name (
    column1 data_type1 CONSTRAINT PRIMARY KEY (column1),
    column2 data_type2,
    column3 data_type3,
    ...
);

The following example creates a table with a primary key that consists of one column:

CREATE TABLE [DataAnalytics].[dbo].[tblt_emp]
(
id BIGINT CONSTRAINT pk_tblt_emp_id PRIMARY KEY(id),
emp_name VARCHAR(50) NOT NULL,
dob DATE,
age FLOAT,
income DECIMAL(10,2),
remarks NVARCHAR(500)
);

Method-2:
If the primary key build up with two or more columns, you have to use the PRIMARY KEY constraint as a table constraint. If the primary key consists of only one column, you can also use the PRIMARY KEY constraint as a table constraint.

CREATE TABLE table_name (
column1 data_type1,
column2 data_type2,
column3 data_type3,
...
CONSTRAINT primary_key_name PRIMARY KEY (column_1, column_2,...)
);

The following example creates a table with a constraint as a table constraint:

CREATE TABLE [DataAnalytics].[dbo].[tblt_emp]
(
id BIGINT NOT NULL,
emp_name VARCHAR(50) NOT NULL,
dob DATE,
age FLOAT,
income DECIMAL(10,2),
remarks NVARCHAR(500),
CONSTRAINT pk_tblt_emp_id PRIMARY KEY(id)
);

Method-3:

Generally, a primary key is defined in a table at the time of table creation. However, sometimes, there may be some situation for not creating primary key at the time of table creation or existing primary key may be deleted due to some reasons. In this case, you can add a primary key into a table by using the ALTER TABLE statement.

Consider the following statement that creates a table without a primary key:

CREATE TABLE [DataAnalytics].[dbo].[tblt_emp]
(
id BIGINT IDENTITY (1,1),
emp_name VARCHAR(50) NOT NULL,
dob DATE,
age FLOAT,
income DECIMAL(10,2),
remarks NVARCHAR(500)
);

To make the id column as the primary key, you can use the following structure:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY(column1,column2...);

Now, let’s see a real example of creating primary key using ALTER TABLE statement:

ALTER TABLE [DataAnalytics].[dbo].[tblt_emp]
ADD CONSTRAINT pk_tblt_emp_id PRIMARY KEY(id);

If tblt_emp table already has data, before creating the column as the primary key, you must ensure:

• There is no other primary key in the tblt_emp table
• id column has no null value and not to allow any null value. In a word id must be defined as a NOT NULL field. You can read my another article How to Change Column Not to Allow NULL in MS SQL Server to implement NOT NULL constraint into your table.
• Values in the id column are unique. If you have this kind of issue you have to delete duplicate values first.

In this tutorial, I have shown how to create primary key during the table creation as well as after table creation in MS SQL Server. Hope you have enjoyed the tutorial. If you want to get updated, like my facebook page http://www.facebook.com/freetechtrainer and stay connected.