How to add Primary key into a new or existing table in SQL Server? Example

Since a primary key is nothing but a constraint, you can use the ALTER clause of SQL to add a primary key into the existing table. Though it's an SQL and database best practice to always have a primary key in a table, you will often find tables that don't have a primary key. Sometimes, this is due to lack of a column that is both NOT NULL and UNIQUE (constraint require to be a primary key), but other times purely due to lack of knowledge or lack of energy. If you don't have a column that can serve as the primary key, you can use identity columns for that purpose. Alternatively, you can also combine multiple columns to create composite primary keys, like you can combine firstname and lastname to create a primary key name, etc.

It's not difficult to add the primary key into a new table, but if you have an existing table with data and you want to add the primary key, how would you do that? What SQL command will you use? This is what you will learn in this Microsoft SQL server tutorial.  I will share the T-SQL query with you to add the primary key into an existing table in SQL Server.

It's also a big mistake not to have a primary key in the table; in fact, it's a well-known mistake made by both beginner and experienced SQL programmers who are well documented by Bill Karwin in his excellent book, SQL Antipatterns: Avoiding the Pitfalls of Database Programming By Pragmatic Programmers.

A programmer needs to identify the right columns and ensure a table has a primary key at the time of creation. If you have not read that book, I strongly suggest you read it at least once. It will save a lot of time in the future when you try to undo the mistakes you have made during the database design phase.

SQL Query to add Primary key into a New table

Before telling you how to add a primary key into a new table, I am telling you again that It's best practice to add the primary key when creating the table itself. This way, you don't need to go through the hassle of altering an existing table full of data. 

It's also tricky if data doesn't follow the NOT NULL and UNIQUE constraint required by the primary key. Due to these reasons, it's best to add the primary key at the same time table is get created.

You can use the following SQL query to add a primary key into a new table in SQL Server 2008, 2012 and other versions:

USE StudentRecords
GO
CREATE TABLE dbo.StudentMaster
(
  StudentId INT NOT NULL,
  StudnetName VARCHAR(50) NOT NULL,
  CONSTRAINT PK_StudentMaster_StudentId PRIMARY KEY CLUSTERED  (StudentId)
);
GO

The above query first chose the StudentRecords database and then creates a new table called StudentMaster with default schema owner dbo (see these SQL Server online courses to learn more ). This table has two columns StudentId and StudentName, we have made StudentId the primary key.

SQL Query to add Primary key into an existing table

Here is the SQL query you can use to add the primary key into an existing table in SQL Server 2008, 2012, and other versions. This query first selects the Students database and then adds a primary key constraint on the StudentMaster table. The Primary key is over the StudentId column.

USE StudentRecords
GO
ALTER TABLE do.StudentMaster 
ADD CONSTRAINT PK_StudentId PRIMARY KEY CLUSTERED (StudentId);
GO

Btw, you should make sure that this column is both not null and unique. Also, existing data in this column should be according to NOT NULL and UNIQUE constraints i.e. no null or duplicate values.

Trying to add a primary key into a nullable column will throw the following error:

"Cannot define a PRIMARY KEY constraint on nullable column in table StudentMaster"

Similarly, if you have duplicates, then also you will get the error. If you face this problem in your table while adding a primary key, then you have two choices, find another column or group of columns that honor NOT NULL and UNIQUE constraint, or drop the existing table or truncate the table to remove all data and then add a primary key.

You can also create a primary key that is not clustered by using the command PRIMARY KEY NONCLUSTERED as shown below:

USE StudentRecords
GO
ALTER TABLE do.StudentMaster 
ADD CONSTRAINT PK_StudentId PRIMARY KEY NONCLUSTERED (StudentId);
GO

This would have created a non-clustered index on the PK_StudentId column in the StudentMaster table.

Here is how you can see the primary key of a table in SQL Server Management Studio:

That's all about adding the primary key into a new and an existing table in SQL Server. The SQL Query is valid for SQL Server version 2008, 2012, 2016, 2018, and higher versions. Actually, it should work with most MSSQL versions, but I have not tested against them. On adding a primary key, it's best to add the primary key when you are first creating the table, as advised in the SQL Antipatterns book.


Related SQL Server Tutorials and Examples

• How to increase the length of the existing VARCHAR column in SQL Server? (answer)
• How to replace NULL with an empty String in Microsoft SQL Server? (answer)
• How to delete records from a table by joining another table? (query)
• How to add columns to an existing table in SQL Server? (example)
• How to find the length of a string in SQL Server? (query)
• How to find the Nth highest salary in SQL Server? (answer)
• What is the difference between CHAR, VARCHAR, and NVARCHAR in SQL Server? (answer)

P .S. - It's difficult to add the primary key into the existing table because the data inside the table may not fulfill the primary key requirement like NOT NULL and unique. For example, you want to make StudentId primary key, but if any entry has StudentId null just because your process has not written it, then you can not add the primary key into such tables.