Monday, June 13, 2016

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 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:

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.

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.

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:

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:

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.