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.
SQL script to create a table with a primary key constraint
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
Step 2 : Add a primary key constraint to the selected column
Here the primary key is the Customer ID column.
Step 3 : Insert 2 lines with the same Customer ID to test the 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 is in place. 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.
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.
- 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.
- Is specified, the index can also be a non-clustered one.
To conclude, this article is about the definition of primary key that allows to maintain the integrity of a table and a database.