This article demonstrates how to create a primary key in SQL Server when creating a table using Transact-SQL.
A primary key is one or more columns that have been configured as the unique identifier for a given table. Primary keys can be used to enforce data integrity in the table.
A table can only have one primary key, and primary keys can only be added to columns that are defined as NOT NULL.
This article demonstrates how to create a primary key in a new table (i.e. when creating the table). If you need to create a primary key in an existing table, see How to Add a Primary Key to an Existing Table in SQL Server.
Example 1 – Create a Table with a Primary Key
First I’ll create a test database:
CREATE DATABASE PK_Test;
Now create a new table that includes a primary key constraint:
USE PK_Test;
CREATE TABLE Colors
(
ColorId int IDENTITY (1,1) NOT NULL PRIMARY KEY,
ColorName varchar(50)
);
This created a new table called Colors which has a primary key constraint on its ColorId column.
Example 2 – Check the Primary Key Constraint
We can 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__Colors__8DA7674DD34F4585 | PK | 1 | 1 | +------------------------------+--------+-------------------+-------------------+
I’ve narrowed the columns down for this example. The sys.key_constraints system view returns more columns than this. You can always use the * wildcard to return all columns if you wish.
We can see by the query result that this database has only one primary key (the one we just created).
In this case the primary key was automatically named by the system. You also have the option of providing your own name (more on that later).
Example 3 – Check the Index
By default a clustered index is created when you create the primary key. You can specify the clustered index explicitly or let it be created automatically. You also have the option of specifying a nonclustered index.
Here’s a query that returns the index that was created automatically when I created the above primary key:
SELECT * FROM sys.indexes WHERE name = 'PK__Colors__8DA7674DD34F4585';
Result (using vertical output):
object_id | 885578193 name | PK__Colors__8DA7674DD34F4585 index_id | 1 type | 1 type_desc | CLUSTERED is_unique | 1 data_space_id | 1 ignore_dup_key | 0 is_primary_key | 1 is_unique_constraint | 0 fill_factor | 0 is_padded | 0 is_disabled | 0 is_hypothetical | 0 is_ignored_in_optimization | 0 allow_row_locks | 1 allow_page_locks | 1 has_filter | 0 filter_definition | NULL compression_delay | NULL suppress_dup_key_messages | 0 auto_created | 0
In this case I narrowed the results to just the row that contains the same name of the primary key I just created. You can always remove the WHERE clause if you need more results to be returned.
We can see that this index has a type_desc of CLUSTERED.
Example 4 – Naming the Primary Key
The primary key that we created above was automatically named by the system. You can provide your own name if you prefer.
Here’s an example of specifying a name for the primary key. In this case I also specify a nonclustered index.
USE PK_Test;
CREATE TABLE Cats
(
CatId int IDENTITY (1,1) NOT NULL,
CONSTRAINT PK_Cats_CatId PRIMARY KEY NONCLUSTERED (CatId),
CatName varchar(50)
);
In this case I use the optional CONSTRAINT keyword to indicate the start of the definition of the primary key, followed by my chosen name for the primary key. I also use the NONCLUSTERED keyword to specify that an unclustered index.
Check the primary key:
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__Colors__8DA7674DD34F4585 | PK | 1 | 1 | | PK_Cats_CatId | PK | 2 | 0 | +------------------------------+--------+-------------------+-------------------+
Check the index:
SELECT * FROM sys.indexes WHERE name = 'PK_Cats_CatId';
Result (using vertical output):
object_id | 917578307 name | PK_Cats_CatId index_id | 2 type | 2 type_desc | NONCLUSTERED is_unique | 1 data_space_id | 1 ignore_dup_key | 0 is_primary_key | 1 is_unique_constraint | 0 fill_factor | 0 is_padded | 0 is_disabled | 0 is_hypothetical | 0 is_ignored_in_optimization | 0 allow_row_locks | 1 allow_page_locks | 1 has_filter | 0 filter_definition | NULL compression_delay | NULL suppress_dup_key_messages | 0 auto_created | 0
So we can see that this time the type_desc is NONCLUSTERED.
Note that when creating a table, CLUSTERED can be specified for only one constraint. If it’s specified for a UNIQUE constraint and a PRIMARY KEY constraint is also specified, the PRIMARY KEY defaults to NONCLUSTERED.
Example 5 – Create a Primary Key on a Nullable Column
A primary key can only be created for columns that are defined as NOT NULL. If you try to create a primary key on a column that is set to NULL, you’ll get an error.
However, if you don’t specify the nullability, the column is set to NOT NULL by default.
To demonstrate this, let’s create another table, but this time, we’ll set it to NULL:
USE PK_Test;
CREATE TABLE Dogs
(
DogId int IDENTITY (1,1) NULL PRIMARY KEY,
DogName varchar(50)
);
Result:
Msg 8147, Level 16, State 1, Line 3 Could not create IDENTITY attribute on nullable column 'DogId', table 'Dogs'. Msg 8111, Level 16, State 1, Line 3 Cannot define PRIMARY KEY constraint on nullable column in table 'Dogs'. Msg 1750, Level 16, State 0, Line 3 Could not create constraint or index. See previous errors.
As expected, we get an error.
Let’s remove NULL from the table definition and try again:
USE PK_Test;
CREATE TABLE Dogs
(
DogId int IDENTITY (1,1) PRIMARY KEY,
DogName varchar(50)
);
Result:
Commands completed successfully. Total execution time: 00:00:00.015
This time the table was created successfully.
Let’s take a look at it:
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 = 'DogId';
Result:
+---------+----------+---------------+---------------+ | Table | Column | is_nullable | is_identity | |---------+----------+---------------+---------------| | Dogs | DogId | 0 | 1 | +---------+----------+---------------+---------------+
So we can see that it’s not nullable, because the is_nullable flag is set to 0.
Example 6 – Primary Key on Multiple Columns
You can also create a primary key on multiple columns. Multicolumn primary keys are also known as composite primary keys. To create a composite primary key, simply separate the columns with a comma when defining the key.
Like this:
CONSTRAINT PK_Name PRIMARY KEY (Column1, Column2)
Here’s an example of a situation where a multicolumn primary key could be used:
CREATE TABLE Musician ( MusicianId int NOT NULL, FirstName varchar(60), LastName varchar(60), CONSTRAINT PK_Musician PRIMARY KEY (MusicianID) ); CREATE TABLE Band ( BandId int NOT NULL, BandName varchar(255), CONSTRAINT PK_Band PRIMARY KEY (BandId) ); CREATE TABLE BandMember ( MusicianId int NOT NULL, BandId int NOT NULL, CONSTRAINT PK_BandMember PRIMARY KEY (MusicianID, BandId), CONSTRAINT FK_BandMember_Band FOREIGN KEY (BandId) REFERENCES Band(BandId), CONSTRAINT FK_BandMember_Musician FOREIGN KEY (MusicianId) REFERENCES Musician(MusicianId) );
In this example, the BandMember table has a multicolumn primary key. In this case each column in the primary key is also a foreign key to the primary key of another table, but this is not a requirement.
See How to Create a Composite Primary Key in SQL Server for a more detailed explanation of this example.
Also see How to Create a Composite Foreign Key in SQL Server for an example that takes it a step further with a multicolumn foreign key that references the above composite primary key.