Create table with primary key in SQL Server

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

Create a customers table with a customer ID.

Step 2 : Add a primary key constraint to the selected column

Here the primary key is the Customer ID column.

Mopdify the customer table to add a primary key constraint.

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.

Insert into the table two different lines with the same customer ID.

Step 4 : check the inserted data and the correct behaviour of the constraint

After that, only one unique line is available in the table.

check the table content with only the first line inserted.

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.
  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. 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.

Learn here how to create a SQL Server partition step by step.