Primary Key Constraint In SQL Server (With Examples)

Last updated on April 12th, 2023 at 09:02 am

A PRIMARY KEY constraint in SQL Server is a field or column which ensures that each entry in a table is must be unique and not null.

A primary key can be created at the column or table level. The following is an example to illustrate this:

The primary key constraint ensures that the value(s) of the primary key column(s) is unique for every row in the table and cannot be null. In this article, we will explore the benefits and limitations of using primary keys in SQL Server and how to create and manage them effectively.

What is the primary key in SQL Server?

A primary key in SQL is a column or set of columns that uniquely identify each row in a table. The primary key column must contain unique and non-null values. It ensures that there are no duplicate rows in the table and provides a fast way to search for specific records.

• 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 null or 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 index on 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  ...
  );

 -------------------------------

-- Creating 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)
);

Creating Primary Key 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','shekh123@gmail.com');

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

Here, we can see in the above example that the primary key doesn’t allow us to insert the duplicate records in the table.

Can we 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);

How to 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 ;

How to Create a composite primary key in the SQL server?

The primary key consists of multiple columns or fields 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 (Ex: ID and EMAIL Columns).

Advantages of the primary key in SQL Server

1. Uniqueness: Primary key ensures the uniqueness of each record in a table, which helps maintain data integrity.
2. Indexing: SQL Server automatically creates a clustered index on the primary key column, which makes data retrieval faster.
3. Joining tables: Primary key is used to join tables in a database. When a table is joined with another table, the primary key of one table is matched with the foreign key of the other table.
4. Data validation: Primary key constraints can be used to validate data before it is entered into a table. This helps to ensure that the data entered into the table is valid and accurate.
5. Data integrity: Primary key constraints help to maintain data integrity by preventing duplicate records from being inserted into a table.
6. Simplifies database design: Primary keys provide a simple and straightforward way to identify records within a table. This makes it easier to design and maintain a database.
7. Supports normalization: Primary keys are an essential part of database normalization. Normalization helps to reduce data redundancy and improve data consistency.

Limitations of the primary key constraint in SQL Server:

The limitations of the primary key constraint in SQL Server are as follows:

1. Unique values only: The primary key constraint allows only unique and non-null values in the column(s). Therefore, it cannot have any duplicate values or NULL values.
2. Limited number of columns: A primary key can be created on one or more columns, but there is a limit to the number of columns that can be included. In SQL Server, a primary key can have a maximum of 16 columns.
3. Performance impact: As the primary key constraint enforces uniqueness, it can impact the performance of data insertion and updates. This is because the database has to check for duplicates each time data is inserted or updated.
4. Additional index creation: When a primary key constraint is created on a table, SQL Server automatically creates a unique index on the column(s) defined as a primary key. This index can take up additional disk space and can affect the performance of data retrieval operations.
5. Number of Indexes: The number of indexes on a table cannot exceed 999 nonclustered indexes and one clustered index, which includes the index created by the primary key constraint.

Reference: Primary Key Constraints

Conclusion

In conclusion, a primary key is a fundamental concept in SQL Server and is used to ensure the uniqueness and integrity of data in a table. By defining a primary key, you can prevent duplicate entries and enforce referential integrity between tables using foreign keys. A well-designed primary key can also improve the performance of database operations. 

Choosing the appropriate data type is important and ensuring that the primary key values are unique and immutable. You can create a robust and efficient database system by following best practices for primary key design.

FAQs

You might want to read this too:

• How to Create Foreign key in SQL?
• Mastering Database Normalization: Best Practices and Techniques
• Clustered Vs Non-Clustered Index in SQL
• Mastering SQL Inner Join
• Understanding CRM Databases
• Types of Joins in SQL Server
• Having VS Where Clause in SQL
• SQL EXISTS – Exploring EXISTS Operator in SQL Server
• Top 10 Differences between Stored Procedure and Function in SQL Server
• Like operator in SQL
• Different ways to delete duplicate rows in SQL Server
• SQL Comparison Operators (Less than, Greater than, Equal, Not Equal operators)
• View In SQL | Types Of Views In SQL Server
• Function vs Stored procedure in SQL Server

• Author
• Recent Posts

Shekh Ali

Meet Shekh Ali, a skilled Software engineer and passionate blogger with an MCA degree and 8+ years of experience in the field. He enjoys sharing his expertise in C#, Java, SQL, Design Patterns, and other related topics through his writing. When he's not coding, Shekh Ali can often indulge in his passions for reading books and fitness training.

Latest posts by Shekh Ali (see all)

• Understanding the SOLID Principle: Single Responsibility Principle in C# - June 7, 2023
• Difference between var and dynamic in C# - May 26, 2023
• Understanding the Chain of Responsibility Design Pattern in C# - May 11, 2023