Key Constraints

What is Primary key ?

Primary key is the column value which uniquely identifies a record or row in a database table. It is a not NULL column, it means no NULL values are allowed. A table can have only one Primary key. But the primary key can also be the combination of columns. The purpose of primary key is to identify a record uniquely.

In the below table Emp_Id is the PK as each row is uniquely identified.

Emp_Id	Emp_Name	Dept_Id
E001	John	1
E002	Ken	2
E003	John	1
E004	marry	3

What is Composite key ?

Composite key is the combination of columns values which uniquely identify a record. That is if more than one column form a Primary key then it is also a Composite key.

In the below table Cust_Id and Prod_Id uniquely identifies the record. Since PK includes more than one column as PK this is called Composite PK.

Cust_Id	Prod_Id	Amt
C001	P001	4354
C001	P002	534
C002	P002	345
C003	P004	3456

What is Unique key and how it is different from Primary key ?

A Unique key is same as Primary key which uniquely identifies a record but Unique key allows one NULL value. This unique key is also called as Alternate key.

What is Foreign Key ?

Foreign key is used to link two tables. A column in one table will be the primary key in another table.Then such column is called Foreign key.
Lets take an example, Suppose we have an Employee table and Dept table. Employee table has Emp_Id as primary key and it also has Emp_Name, Dept_Id columns. Now in Dept table Dept_Id is the primary key and it also has Dept_Name  column. Now Dept_Id in Employee table is called Foreign key as this is primary key in Dept table.

Emp_Id	Emp_Name	Dept_Id
E001	John	1
E002	Ken	2
E003	John	1
E004	marry	3

Dept_Id	Dept_Name
1	HR
2	Sales
3	Finance

What is Candidate Key ?

Suppose a table has 5 columns and among them 3 columns are unique, then any 1 column among 3 can be primary key. Since 3 columns participate to be primary key, all 3 columns are called Candidate key.