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.
Script to create a SQL Server table with a primary key
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 an ID column
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 selected 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 Customer 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 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 :
- First, only one primary key per table.
- A primary key can be a compounded key using multiple columns. For example a concatenation of these columns : ID + First Name + Last Name.
- In addition, a column used as a primary key cannot be NULL, so it is defined as NOT NULL.
- Also, a primary key has per default it’s corresponding Clustered Index created automatically.
- If specified in the script, the index can also be a non-clustered one.
To conclude, this article is about how to add a primary key that allows to maintain the integrity of a table and a database.
To learn more about SQL objects, here is a step-by-step guide to create a SQL Server clustered index.