Primary Key in SQL Server

Primary Key in SQL Server with Example

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

  1. What is Primary Key in SQL Server?
  2. Examples of Primary Key.
  3. What is Composite Primary key in SQL Server?
  4. What are the differences between the primary key and unique key in SQL Server?
What is Primary Key in SQL Server?

The Primary Key in SQL Server is the combination of Unique and Not Null Constraint. That 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.

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

A table should contain only 1 Primary Key constraint which can be either on single or multiple columns i.e. the composite primary key. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table.

The SQL Server Primary Key can apply on any data type like integer, character, decimal, money, etc.

Example to understand SQL Server Primary Key:
CREATE TABLE Branches 
( 
    Bcode INT PRIMARY KEY, 
    Bname VARCHAR(40), 
    Bloc  CHAR(40) PRIMARY KEY 
) 

When we execute the above query it will display 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 Let’s insert some data into the Branches table

INSERT Branches VALUES (1021,’SBI’,’SRNAGAR‘) — ALLOWED

It executed successfully

INSERT Branches VALUES (1021,’SBI’,’SRNAGAR’) — NOT ALLOWED

When we execute the above query it will give us the error as Violation of PRIMARY KEY constraint ‘PK__Branches__8880509B1333792F’. Cannot insert duplicate key in object ‘dbo.Branches’. The duplicate key value is (1021).

The statement has been terminated.

INSERT Branches VALUES (NULL,’SBI’,’SRNAGAR’) –NOT ALLOWED

When we execute the above query it will give us the error as Cannot insert the value NULL into column ‘Bcode’, table ‘TestDB.dbo.Branches’; column does not allow nulls. INSERT fails.

The statement has been terminated.

INSERT Branches VALUES (1022,’SBI’,’SRNAGAR’) –ALLOWED

What is Composite Primary key in SQL Server?

When the primary key constructed with more than 1 column then that primary key is called the SQL Server Composite Primary Key. The maximum number of columns are including in composite primary key is 16 columns. Composite primary key need to define after all the columns definition i.e. at the end of the table definition.

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

Let us see an example to understand this concept:

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

Let’s insert some data to understand composite primary key.

Insert into BranchDetails (City, Bcode, Bloc) values(‘Mumbai’, 10, ‘Goregaon’) — Allowed

It executed successfully

Insert into BranchDetails (City, Bcode, Bloc) values(‘Mumbai’, 10, ‘Malad’) — Not Allowed

When we execute the above query it will display the error as 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.

Insert into BranchDetails (City, Bcode, Bloc) values('Mumbai', 20, 'Malad') – Allowed

Insert into BranchDetails (City, Bcode, Bloc) values('Hyderabad', 20, 'SR Nagar') -- Allowed

Insert into BranchDetails (City, Bcode, Bloc) values('Hyderabad', 20, 'Begumpet') --Not Allowed

When we execute the above query it will display the error as 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: Primary key is also called a candidate key.

What are the differences between the primary key and unique key in SQL Server?

As we already 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 doesn’t allow NULL values whereas the unique key column allows only one NULL value.

In the next article, I am going to discuss Foreign Key Constraint in SQL Server with examples. Here, in this article, I try to explain the SQL Server Primary Key step by step with some simple example. I hope you enjoy this article.

Leave a Reply

Your email address will not be published. Required fields are marked *