This article demonstrates how to add a primary key to an existing table in SQL Server using Transact-SQL.
A primary key is a column that has been configured as the unique identifier for a given table.
You would normally create a primary key constraint when you create the table, but you can also add a primary key to an existing table.
Note that a table can only have one primary key. So you can’t add a primary key if the table already has one.
Also primary keys can only be added to columns that are defined as NOT NULL.
Example 1 – Add a Primary Key Constraint
In this example I create a table, but I forget to add a primary key constraint. So I then go back and alter the table to have a primary key.
Create the table (but forget to create a primary key):
USE Test;
CREATE TABLE Colors
(
ColorId int IDENTITY (1,1) NOT NULL,
ColorName varchar(50)
);
Result:
Commands completed successfully. Total execution time: 00:00:00.058
Oops – I forgot to create the primary key!
No problem! We can add one now:
ALTER TABLE Colors ADD CONSTRAINT PK_Colors_ColorId PRIMARY KEY CLUSTERED (ColorId);
Result:
Commands completed successfully. Total execution time: 00:00:00.031
This has now added a PRIMARY KEY constraint for the ColorId column.
Example 2 – Check the Primary Key Constraint
Let’s run the following code to return a list of primary key constraints in the database:
SELECT name, type, unique_index_id, is_system_named FROM sys.key_constraints WHERE type = 'PK';
Result:
+------------------------------+--------+-------------------+-------------------+ | name | type | unique_index_id | is_system_named | |------------------------------+--------+-------------------+-------------------| | PK__MyTest__606C418F16F9CCCF | PK | 1 | 1 | | PK__Client__96ADCE1ACB91C2A9 | PK | 1 | 1 | | PK_Colors_ColorId | PK | 1 | 0 | +------------------------------+--------+-------------------+-------------------+
Your results will be different, depending on the primary keys in your database.
Also note that this system view returns more columns than what I’ve specified here, but you can use the * wildcard to return all columns if you wish.
Example 3 – Adding a Primary Key to a Column that allows NULL Values
A primary key can only be added to columns that are defined as NOT NULL. If you try to add a primary key to a column that is nullable, you’ll get an error.
To demonstrate this, let’s create another table, but this time, we’ll also forget to specify the column as NOT NULL:
USE Test;
CREATE TABLE Colors2
(
ColorId int,
ColorName varchar(50)
);
We can run the following query to check whether the column allows nulls or not:
SELECT t.name AS 'Table', c.name AS 'Column', c.is_nullable, c.is_identity FROM sys.columns c INNER JOIN sys.tables T ON c.object_id = t.object_id WHERE c.name = 'ColorId';
Result:
+---------+----------+---------------+---------------+ | Table | Column | is_nullable | is_identity | |---------+----------+---------------+---------------| | Colors | ColorId | 0 | 1 | | Colors2 | ColorId | 1 | 0 | +---------+----------+---------------+---------------+
We can see that the one we created earlier (in the Colors table) is nullable and is an identity column. The second one (in the Colors2 table) is nullable and is not an identity column.
Now let’s try to add a primary key constraint to the nullable column:
ALTER TABLE Colors2 ADD CONSTRAINT PK_Colors2_ColorId PRIMARY KEY CLUSTERED (ColorId);
Result:
Msg 8111, Level 16, State 1, Line 1 Cannot define PRIMARY KEY constraint on nullable column in table 'Colors2'. Msg 1750, Level 16, State 0, Line 1 Could not create constraint or index. See previous errors.
So in this case, we’ll need to alter the column to be NOT NULL before we try to define it as the primary key.
We can use ALTER COLUMN within an ALTER TABLE statement to set this column to NOT NULL:
ALTER TABLE Colors2 ALTER COLUMN ColorId int NOT NULL;
Let’s check the column again:
SELECT t.name AS 'Table', c.name AS 'Column', c.is_nullable, c.is_identity FROM sys.columns c INNER JOIN sys.tables T ON c.object_id = t.object_id WHERE c.name = 'ColorId';
Result:
+---------+----------+---------------+---------------+ | Table | Column | is_nullable | is_identity | |---------+----------+---------------+---------------| | Colors | ColorId | 0 | 1 | | Colors2 | ColorId | 0 | 0 | +---------+----------+---------------+---------------+
So we can see that Colors2 is now set to 0, which means that it’s not nullable (it can’t contain NULL values).
Also take note that the column is not an identity column. I’ll discuss this later.
Anyway, now that the column is defined as NOT NULL we can go ahead and add the primary key:
ALTER TABLE Colors2 ADD CONSTRAINT PK_Colors2_ColorId PRIMARY KEY CLUSTERED (ColorId);
Result:
Commands completed successfully. Total execution time: 00:00:00.048
To verify, let’s again check all primary key constraints for this table:
SELECT name, type, unique_index_id, is_system_named FROM sys.key_constraints WHERE type = 'PK';
Result:
+------------------------------+--------+-------------------+-------------------+ | name | type | unique_index_id | is_system_named | |------------------------------+--------+-------------------+-------------------| | PK__MyTest__606C418F16F9CCCF | PK | 1 | 1 | | PK__Client__96ADCE1ACB91C2A9 | PK | 1 | 1 | | PK_Colors_ColorId | PK | 1 | 0 | | PK_Colors2_ColorId | PK | 1 | 0 | +------------------------------+--------+-------------------+-------------------+
Our new primary key that we called PK_Colors2_ColorId has been added to the list.
Example 4 – Altering a Column to be an Identity Column
Primary keys are often applied to identity columns. Identity columns are defined as such with the IDENTITY keyword, followed by an optional seed and increment value within parentheses.
When a new row is added to the table, SQL Server provides a unique, incremental value for the identity column.
If you plan on using an identity column, you need to have done that already. You can’t alter an existing column to be an identity column.
When I ran the query earlier, we could see that the Colors2.ColorId column is not an identity column (we know this because is_identity is set to 0). This means I created the PK_Colors2_ColorId primary key on a non-identity column.
Here’s what happens if we try to alter the table to be an identity column:
ALTER TABLE Colors2 ALTER COLUMN ColorId int IDENTITY (1,1) NOT NULL PRIMARY KEY;
Result:
Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'IDENTITY'.
As mentioned, in order to overcome this, we need to drop the column and start again.
If the column already contains data, you’ll need to do some extra work. That’s outside the scope of this article, but here’s an example of dropping the above column and recreating it as an identity column:
USE Test;
DROP TABLE Colors2;
CREATE TABLE Colors2
(
ColorId int IDENTITY (1,1) NOT NULL PRIMARY KEY,
ColorName varchar(50)
);
Result:
Commands completed successfully. Total execution time: 00:00:00.049
Notice that I didn’t provide a name for the primary key constraint this time. In this case, the system will create a name for it.
Quickly check the column:
SELECT t.name AS 'Table', c.name AS 'Column', c.is_nullable, c.is_identity FROM sys.columns c INNER JOIN sys.tables T ON c.object_id = t.object_id WHERE c.name = 'ColorId';
Result:
+---------+----------+---------------+---------------+ | Table | Column | is_nullable | is_identity | |---------+----------+---------------+---------------| | Colors | ColorId | 0 | 1 | | Colors2 | ColorId | 0 | 1 | +---------+----------+---------------+---------------+
Yes, it is now an identity column.
Let’s take another look at the primary keys for this table:
SELECT name, type, unique_index_id, is_system_named FROM sys.key_constraints WHERE type = 'PK';
Result:
+-------------------------------+--------+-------------------+-------------------+ | name | type | unique_index_id | is_system_named | |-------------------------------+--------+-------------------+-------------------| | PK__MyTest__606C418F16F9CCCF | PK | 1 | 1 | | PK__Client__96ADCE1ACB91C2A9 | PK | 1 | 1 | | PK_Colors_ColorId | PK | 1 | 0 | | PK__Colors2__8DA7674D8F57294D | PK | 1 | 1 | +-------------------------------+--------+-------------------+-------------------+
So we now have a system-named primary key called PK__Colors2__8DA7674D8F57294D.