What is PRIMARY key constraint in SQL Server ?

It is unique constraint on columns, so all values from column must have unique and not null value. It is combination of NOT NULL and UNIQUE key constraints.

One table must have one primary key.
We can set primary key on one column and combination of column. When we set primary key on combination of column then that primary key also called as Composite key.

Primary key also called as cluster index. It is similar with index in book which help reader to find particular topic in faster time. In same way cluster index work to find the data from table in faster way.


1) Add PRIMARY key constraint on table by Query.
In below example we are creating new table of tblStudentDetails and create StudentId as Primary Key because every student should have one unique key to process data in future.

Example

CREATE TABLE tblStudentDetails
(
Id INT IDENTITY (1,1), --Auto increment table row
StudentId INT NOT NULL PRIMARY KEY , --Student Unique Id through Database
StudentName VARCHAR (50),
City VARCHAR (50)
)

Note : In above example we insert NOT NULL and PRIMARY KEY constraint on StudentID column. And Id is identity of the table which is not having any relation with StudentId column.


2) Add PRIMARY key constraint on table using table design.

Step 1 :


create primary key on sql table in sql server

Step 2 :


create primary key in sql server

Step 3 : Save table.

Let's try to insert duplicate record in primary key column that is StudentID. We already have below dataset.


violation of primary key in sql server

Example

INSERT INTO tblStudentDetails (StudentId, StudentName, City)
VALUES   (14, 'Gopal', 'Pune')

Output

Violation of PRIMARY KEY constraint in sql server


Note : If you observe above insert statement then you can find that student with 14 StudentID was already available and we are trying to insert record for 14 again in table.
So in primary key constraint all values should be unique and not null.



Add PRIMARY KEY constraint on existing columns of table. We already have another table called as tblStudentMarks which contain details of student marks.
Add primary key on Id column.

Example

ALTER TABLE tblStudentMarks
ADD PRIMARY KEY (Id)

Output

can not define primary key constraint on multiple column in table


Note : We can add primary key on column who not having NOT NULL constraint else it will give an below error.


Cannot define PRIMARY KEY constraint on nullable column in table