How to create a SQL Server table with a primary key constraint?

T-SQL code examples to add a primary key constraint to a column to avoid inserting multiple times the same record.

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

• Firstly, design the customers table using SQL code.
• Secondly, add a constraint on the selected column to be the key.
• Insert two lines with the same customer ID.
• 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 primary key constraint 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.

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

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

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 the inserted data and the correct behaviour of the constraint

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 primary key constraints

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