What is Primary Key in SQL

Hi ,today we are going to understand about SQL PRIMARY KEY. Primary Key is a column(s) in SQL Database table. It is SQL constraint which can not be NULL values .Primary Key uniquely identify each row in SQL database table. It must contain unique values . Two rows in a table can not have same value for Primary Key column(s).Primary Key in a table can be one column or a set of columns.A table can have maximum one Primary Key.

What is composite Key in SQL

If Primary Key defined on table contains more than one column then it is known as COMPOSITE KEY .

Don’t be confused let us understand it with an example

Let us create a table with Primary Key constraint

create table Employee
( Id int NOT NULL,
Name varchar(20) NOT NULL,
Age int,
Department varchar(20),
Address varchar(20),
Primary Key(Id)
)

primary key in sql
composite key in sql

As explained on above image,we crated a table EMPLOYEE with primary key Id.So Id column values in EMPLOYEE table must be unique.Id column can not have duplicate values.

Now Let us insert values to EMPLOYEE table

insert into Employee(Id,Name,Age,Department,Address) 
values (1,'Jacob',25,'HR','NewYork')

insert into Employee(Id,Name,Age,Department,Address) 
values (2,'Mary',38,'IT','Boston')
primary key in sql
composite key in sql

Let us check EMPLOYEE table


select * from Employee
primary key in sql
composite key in sql

Now i am going to insert Thomas from Marketing department to EMPLOYEE table as given below


insert into Employee(Id,Name,Age,Department,Address) 
values (1,'Thomas',42,'Marketing','Chicago')
primary key in sql
composite key in sql

Yes,We can not insert value 1 for Id column again .it will generate error as shown above .because value 1 is already assigned and inserted with employee Jacob (Jacob from HR Department).So if we try to insert value 1 for Id column for employee Thomas(Thomas from Marketing Department) ,SQL will generate an error

Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint ‘PK__Employee__3214EC071CF15040’. Cannot insert duplicate key in object ‘dbo.Employee’.
The statement has been terminated.

because Id is Primary Key for EMPLOYEE table.it can not be duplicated and it should be unique .

Now Let us check how to define COMPOSITE KEY in EMPLOYEE table

create table Employee
( Id int NOT NULL,
Name varchar(20) NOT NULL,
Age int,
Department varchar(20),
Address varchar(20),
Primary Key (Id,Name)
)

primary key in sql
composite key in sql

Now Let us insert values to EMPLOYEE table

insert into Employee(Id,Name,Age,Department,Address) 
values (1,'Jacob',25,'HR','NewYork')

insert into Employee(Id,Name,Age,Department,Address) 
values (2,'Mary',38,'IT','Boston')
primary key in sql
composite key in sql

Let us check EMPLOYEE table



select * from Employee
primary key in sql
composite key in sql

Now i am going to insert Jacob from Marketing department to EMPLOYEE table as given below


insert into Employee(Id,Name,Age,Department,Address) 
values (1,'Jacob',42,'Marketing','Chicago')
primary key in sql
composite key in sql

So we can not insert value 1 for Id column and value Jacob for Name column again, it will generate error as shown below.

Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint ‘PK__Employee__5523B44820C1E124’. Cannot insert duplicate key in object ‘dbo.Employee’.
The statement has been terminated.

because we have already one entry with Id=1 and Name=Jacob (Jacob from HR department).So we can not have duplicate value for Id and Name column .because it is our primary key .Id and Name column together defined as Primary key .it is also known as Composite Key.So id and Name columns together can not duplicated .

Leave a Reply

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