How to create a SQL Server primary key?

Code examples for adding a primary key constraint to a SQL Server column to avoid inserting the same key several times.

How to create a table with a primary key in SQL Server? A database primary key is designed to allow one unique value to identify a line in a table. First, what is the purpose of a primary key? And moreover, what is the definition of a primary key? Check out the following examples to create a primary key and manage duplicates in database projects.

What is a SQL Server primary key?

In SQL Server, a primary key is a constraint that uniquely identifies each record within a database table. It ensures that no two rows have duplicate values in the primary key column(s) and enforces the integrity of the dataset. A primary key can consist of a single column or a combination of multiple columns (composite primary key). Additionally, the primary key automatically creates a unique index on the specified column(s) to speed up queries and enforce uniqueness. So in short, a primary key ensures that only one unique line as a specific ID.

To create a table and to add a primary key to it, follow these four steps:

1. Firstly, design the customers table using SQL code.
2. Secondly, add a constraint on the selected column to be the key.
3. Insert two lines with the same customer ID.
4. Check the result data in the table.

step 1: Design the customers table with a Customer ID column

Design and create a customer’s table with an ID column to store the customer number.

-- Créer la table des clients avec la colonne CustomerID déclarée comme NOT NULL
CREATE TABLE dbo.Customers (
   [CustomerID]   INT NOT NULL,
   [FirstName]    NVARCHAR(20),
   [LastName]     NVARCHAR(20),
   [City]         NVARCHAR(20),
   [Country]      NVARCHAR(50)
)
GO

step 2: Add the SQL Server primary key to the column

Here the primary key is the Customer ID column. Use the ADD CONSTRAINT PRIMARY KEY T-SQL command to create the constraint.

2.1 Use a T-SQL alter table statement to create the key

ALTER TABLE dbo.Customers 
   ADD CONSTRAINT [CustomersPrimaryKeyCustomerID] 
      PRIMARY KEY CLUSTERED ([CustomerID] ASC);
GO 

2.2 Create the key in the Create Table statement

Of course, another approach is ti add the primary key directly in the create table statement, like the code below for example.

-- Drop the customers table if it exists
DROP TABLE IF EXISTS dbo.Customers;
GO

-- Create the customers table with the column CustomerID declared as NOT NULL and set as the primary key
CREATE TABLE dbo.Customers (
   [CustomerID]   INT NOT NULL PRIMARY KEY CLUSTERED,
   [FirstName]    NVARCHAR(20),
   [LastName]     NVARCHAR(20),
   [City]         NVARCHAR(20),
   [Country]      NVARCHAR(50)
)
GO

step 3: Insert two lines with same ID to test the primary key

Indeed, the only way to verify the constraint is to use it. Insert two lines with the same customer ID to check if the primary key works as expected.

-- Insert two different lines with the same customer number
INSERT INTO dbo.Customers (CustomerID, FirstName, LastName, City, Country) 
VALUES ( 1, 'Ali','Ahmed','Cairo','Egypt');

INSERT INTO dbo.Customers (CustomerID, FirstName, LastName, City, Country) 
VALUES ( 1, 'Johnny','John','Toronto','Canada');

Note the violation of primary key constraint error :

Msg 2627, Level 14, State 1, Line 22
Violation of PRIMARY KEY constraint ‘CustomersPrimaryKeyCustomerID’. Cannot insert duplicate key in object ‘dbo.customers’. The duplicate key value is (1).
The statement has been terminated.

step 4: Check data and behaviour of the primary key

After that, only one unique line is available in the table with the ID one. It is now only possible to assign one value to a customer ID, it makes it unique in the table. To go further, please note a few things about the SQL Server Primary keys. However, these specific primary keys limitations and behaviours are fully part of how keys work :

1. First, only one primary key per table.
2. A primary key can be a compounded key using multiple columns. For example a concatenation of these columns : ID + First Name + Last Name.
3. In addition, a column used as a primary key cannot be NULL, so it is defined as NOT NULL.
4. Also, a primary key has per default it’s corresponding Clustered Index created automatically.
5. If specified in the script, the index can also be a non-clustered one.

Conclusion on SQL Server primary key

In conclusion, create a SQL Server table with a primary key constraint is a crucial step in maintaining the integrity of a database. The primary key ensures that only one unique value identifies a row in a table, and it cannot be NULL. By following the four steps outlined in this tutorial, users can easily add a primary key constraint to their SQL Server tables. Remember that a table can only have one primary key, but it can be a compounded key using multiple columns.

Additionally, a primary key constraints has their corresponding clustered index created automatically. Understanding the limitations and behaviours of primary keys is essential in designing effective database structures. To learn more about SQL objects, here is a step-by-step guide to create a SQL Server clustered index.

Clustered index in SQL Server