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

Since a primary key is nothing but a constraint you can use ALTER clause of SQL to add a primary key into existing table. Though it's an SQL and database best practice to always have a primary key in a table, many times you will find tables which don't have a primary key. Sometimes, this is due to lack of a column which 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 which can serve as primary key you can use identity columns for that purpose. Alternatively, you can also combine multiple columns to create a composite primary keys e.g. 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 into that how would you do that? what SQL command will you use? This is what you will learn in this Microsoft SQL server tutorial.  I am going to share you the T-SQL query you can use to add the primary key into an existing table in SQL SERVER.

It's 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 which are well documented by Bill Karwin in his excellent book, SQL Antipatterns: Avoiding the Pitfalls of Database Programming By Pragmatic Programmers.

How to add primary key into existing table in SQL

It's important for a programmer to identify 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 future when you try to undo the mistakes you have done during 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 at the time of creating the table itself. This way, you don't need to go through the hassle of altering an existing table with full of data. It's also tricky if data doesn't follow the NOT NULL and UNIQUE constraint required by 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 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

Above query first chose the StudentRecords database and then creates a new table called StudentMaster with default schema owner dbo (see Microsoft SQL Server 2012 T-SQL Fundamentals). This table has two columns StudentId and StudentName, we have made StudentId as the primary key.

How to add primary key into a new table.



SQL Query to add Primary key into 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 is first selecting Students database and then adding a primary key constraint on StudentMaster table. The Primary key is over 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 following error:

"Cannot define 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 primary key then you have two choices, find another column or group of columns which 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 primary key which is not clustered by using 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 PK_StudentId column in the StudentMaster table.

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

How to add primary key into a table in SQL


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

It's difficult to add the primary key into existing table because the data inside the table may not fulfill the requirement of primary key e.g. 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.


Related SQL Server Tutorials and Examples
  • How to increase the length of existing VARCHAR column in SQL Server? (answer)
  • How to replace NULL with empty String in Microsoft SQL Server? (answer)
  • How to delete records from a table by joining to 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 2008? (query)
  • How to find the Nth highest salary in SQL Server 2012? (answer)
  • What is the difference between CHAR, VARCHAR, and NVARCHAR in SQL Server? (answer)

No comments :

Post a Comment