Loading

Microsoft SQL Server

What is Primary Key Constraint in SQL Server?. The Complete Microsoft SQL Server Developer Course 2022 [Videos].

In this Video, I am going to discuss Primary Key Constraint in SQL Server with Examples. Please watch our previous Video where we discussed the basics of SQL Server Constraint. In our previous Video, we discussed Default, Not Null, Unique and Check Constraint in SQL Server with some examples. As part of this Video, we are going to discuss the following pointers.

What is Primary Key in SQL Server?

The Primary Key in SQL Server is the combination of Unique and Not Null ConstraintThat means it will not allow either NULL or Duplicate values into a column or columns on which the primary key constraint is applied. Using the primary key we can enforce entity integrity i.e. using the primary key value we should uniquely identify a record.

A table should contain only 1 Primary Key which can be either on a single or multiple columns i.e. the composite primary key. A table should have a primary key to uniquely identify each record. The Primary Key constraint can be applied to any data type like integer, character, decimal, money, etc.

Understanding the Primary Key Constraint in SQL Server:

We cannot apply more than one primary key in a table. Let us prove this. Please try to execute the below SQL Script where we used the Primary key on more than one column (Bcode and Blocation).

CREATE TABLE Branches
(
Bcode INT PRIMARY KEY,
Bname VARCHAR(40),
Bloc CHAR(40) PRIMARY KEY
)

When we try to execute the above query it will give us the error as: “Cannot add multiple PRIMARY KEY constraints to table "Branches".”

To overcome the above drawback rewrite the query like below

CREATE TABLE Branches
(
Bcode INT PRIMARY KEY,
Bname VARCHAR(40),
Bloc CHAR(40)
)

Now when we execute the above code it successfully executed and create the Branches table. Here, we marked Bcode column as the primary key. Lets execute the following insert statement to insert a record into the Branches table

INSERT Branches VALUES (1021, "SBI", "SRNAGAR") — It executed successfully.

Adding Duplicate value in the Primary Key column:

Try to insert a duplicate value into the Primary key column. Please execute the insert statement to insert a duplicate record and see what happens.

INSERT Branches VALUES (1021,"SBI","SRNAGAR") — Not Allowed

When we execute the same insert statement ]it will give us the following error. That means the Primary Key constraint will not accept duplicate values in it.

Primary Key in SQL Server

Inserting NULL in Primary Key Column in SQL Server:

Let us try to insert NULL into the primary key column by executing the below insert statement and see what happens.


When we execute the above insert statement it gives us the following error stating cannot insert null value which proves that Primary Key will not accept NULL.

SQL Server Primary Key

What is the Composite Primary key in SQL Server?

It is also possible in SQL Server to create the Primary Key constraint on more than one columns and when we do so, it is called a Composite Primary Key. The maximum number of columns is including in the composite primary key is 16 columns. It is only possible to impose the Composite Primary Key at the table level, it is not possible at the column level.

Note: In a composite primary key, each column can accept duplicate values but the duplicate combination should not be duplicated.

Let us understand this with an example.

Please have a look at the following create table statement. Here, we impose the Primary Key Constraint at the table level i.e. after all the columns are created. While creating the primary key we are providing two columns i.e. City and Bcode. As the Primary Key is created based on more than one column, it is termed as a composite primary key.

CREATE TABLE BranchDetails
(
City VARCHAR(40),
Bcode INT,
Bloc VARCHAR(30),
PRIMARY KEY(City, Bcode)
)

Please execute the following insert statement to insert a record into the BranchDetails table.


The above insert statement executed successfully. Let us try to execute the below Insert statement and see what happens.


When we execute the above insert query it will display the following error i.e. cannot insert duplicate value.

Violation of PRIMARY KEY constraint "PK__BranchDe__064CA565A64FECC6". Cannot insert duplicate key in object "dbo.BranchDetails" The duplicate key value is (Mumbai, 10).

The statement has been terminated.

Let try to execute the following two statements.


The above two statements are executed successfully in spite of the Bcode value is the same. This is because it will check only the combination of City and Bcode and here the combination is not duplicate.

Now try to execute the following insert statement.


When we execute the above insert query it will display the following error. This is because the combination of the City and Bcode value alwatchy exists in the database.

Violation of PRIMARY KEY constraint "PK__BranchDe__064CA565A64FECC6". Cannot insert duplicate key in object "dbo.BranchDetails". The duplicate key value is (Hyderabad, 20).

The statement has been terminated.

Note: The primary key is also called a candidate key.

When do we need to choose Primary Key?

When we need the following features on a column, then we need to make that column as the Primary Key

  1. NULLs should not be allowed.
  2. It should be unique
  3. It can not be modified.
What are the differences between the primary key and unique key in SQL Server?

As we alwatchy discussed both unique key and Primary Key handles the uniqueness of a column on which they are applied. But the difference is that by default in SQL Server the primary key creates a unique clustered index whereas the unique key creates a unique non-clustered index on the column. Another difference between them is that the primary key column doesnt allow NULL values whereas the unique key column allows only one NULL value.

The Primary Key Constraint in SQL Server is also used to make a relationship with a Foreign Key constraint on another table. We will discuss this in the next Video. 

See All

Comments (505 Comments)

Submit Your Comment

See All Posts

Related Posts

Microsoft SQL Server / Blog

What is SQL Server?

The SQL Server is a relational database management system (RDBMS) which is developed by Microsoft. It is also called MS SQL Server. This product is built on the basic function of storing, retrieving data as required by other applications. It can be run either on the same computer or on another across a network.
15-Feb-2022 /26 /505

Microsoft SQL Server / Blog

How to Creating Altering and Deleting Database in SQL Server?

In this article, I am going to discuss Creating Altering and Deleting Database in SQL Server with examples. Please read our previous article where we discussed how to connect to SQL Server Database using a client tool called SQL Server Management Studio. As part of this article, we are going to discuss the following pointers.
15-Feb-2022 /26 /505

Microsoft SQL Server / Blog

How to Creating Altering and Deleting Tables in SQL server?

In this article, I am going to discuss Creating Altering and Deleting Tables in SQL Server with examples. Along the way, we will also discuss the Data Definition Language in detail. Please read our previous article before proceeding to this article where we discussed the Creating Altering and Deleting Database in SQL Server with examples. As part of this article, we are going to discuss the following important points.
15-Feb-2022 /26 /505