The PRIMARY KEY constraint in SQL Server is a field or column which ensures that each entry in a table is uniquely recognized.
Primary keys must have UNIQUE values and cannot have NULL values.
A table can only have one primary key, which can be made up of one or more columns (fields).
A primary key can be created at the column or table level. The following is an example to illustrate this:
What is the primary key in SQL Server?
In short, We use to create a primary key column to uniquely identify records and access data quickly and effectively from the database.
- When you create a primary key on a table column, the database engine automatically creates a unique index on that column.
- A primary key column doesn’t allow inserting
nullor duplicate values.
- A Primary key can consist of up to 16 columns.
- A table can have only one primary key which can be applied to single or multiple columns.
- The primary key which consists of multiple columns is known as the
Composite primary key.
- A Primary Key constraint can be applied to the columns having any data types.
- A primary key by default creates a
clustered indexon the column.
Syntax to create a primary key
The following is the syntax to create a primary key in the SQL server.
-- Column level Primary key CREATE TABLE TableName ( Column1 data_type [NOT NULL ] [ PRIMARY KEY ], Column2 data_type [ NULL | NOT NULL ], Column3 ... ); ------------------------------- -- Table level Primary key CREATE TABLE TableName ( Column1 data_type [ NULL | NOT NULL ], Column2 datatype [ NULL | NOT NULL ], Column3 ... CONSTRAINT ConstraintName PRIMARY KEY (Column1, Column2) );
Create Primary Key Constraint Using Create Table statement
The following SQL query will create a Primary Key on the ID column when the Employee table is created.
-- Column level Primary key CREATE TABLE Employee ( ID INT CONSTRAINT PK_ID PRIMARY KEY, NAME VARCHAR (50), EMAIL VARCHAR(60) ) -- OR -- Table level Primary key CREATE TABLE Employee ( ID INT NOT NULL, NAME VARCHAR (50), EMAIL VARCHAR(60) CONSTRAINT PK_ID PRIMARY KEY(ID) )
Let’s execute the above SQL query where we have created a primary key on the ID column of the employee table.
Let’s execute the following SQL insert statement to add a new record to the Employee table.
-- Insert a new record into the Employee table. INSERT INTO EMPLOYEE VALUES(1,'Shekh Ali','firstname.lastname@example.org');
Add duplicate value in the primary key column
Primary key columns do not accept duplicate values. Attempting to insert the same value that is already in the primary key column will result in an error.
Let’s execute the following SQL INSERT statement with duplicate values.
You can see in the above example that the primary key doesn’t allow to insert the duplicate records in the table.
Insert NULL value in the primary key column
The primary key column doesn’t accept the NULL value. If we try to insert the null value in the primary key column, It will throw an error.
-- Insert NULL value into the primary key column. INSERT INTO EMPLOYEE (ID) VALUES (NULL);
Drop primary key constraint in SQL server
Let’s drop a primary key constraint in the SQL server using the ALTER table statement.
-- Drop primary key constraint ALTER TABLE Employee DROP CONSTRAINT PK_ID ;
Create a composite primary key in the SQL server
The primary key consists of multiple columns or fields and is known as the Composite primary key. In the following example, we will create a composite primary key on multiple columns such as ID and EMAIL.
First, let’s make the EMAIL column non-nullable. Otherwise, an error will occur when creating the composite primary key.
-- Make EMAIL column not nullable. ALTER TABLE Employee ALTER COLUMN EMAIL VARCHAR(60) NOT NULL ;
Now let’s create a composite primary key.
-- Create composite primary key ALTER TABLE Employee ADD CONSTRAINT EMP_PK PRIMARY KEY (ID, EMAIL);
Here in the above image, we can see that the Composite primary key is applied over multiple columns such as ID and EMAIL.
Primary key Constraint Limitation in SQL Server
- Only one PRIMARY KEY constraint can exist in a table.
- If the primary key is a column, the value must be unique and not
NULL. If the primary key is made up of many columns, each combination of values in these columns must be unique.
- In SQL Server, a primary key cannot have more than 16 columns and a total key length of more than 900 bytes.
- The number of indexes on the table cannot exceed 999 nonclustered indexes and 1 clustered index because of the index created by the primary key constraint.
Reference: Primary Key Constraints
In this Article, we used multiple examples to show how to Create, Alter, and Drop Primary Key Constraints in SQL Server.
I hope you found this post useful. If you have any questions or comments, please leave them below.
- Top 10 Differences between Stored Procedure and Function in SQL Server
- Types of Joins in SQL Server
- Function vs Stored procedure in SQL Server
- Stored Procedure in SQL Server With Examples
- Create, Alter, and Drop Database In SQL Server
- SQL Comparison Operators (Less than, Greater than, Equal, Not Equal operators)
- View In SQL | Types Of Views In SQL Server
- SQL Server Trigger Update, Insert, Delete Examples