Primary Key,Unique and Foreign Key Concept?

Primary Key:-

It is used to provide a unique and not null value to a particular column.
The primary key must be defined in Table.


Primary key will be one in one table and it is eligible for the foreign key to providing referential integrity constraint.


Create Table tablename(columnname datatype primary key,...)
Create Table tablename(columnname int primary key auto_increment,...)


.................................................................................................................................................

Unique:-

It is only used to define unique value, unique can be multiple in one table, a single null value will be allowed in unique key.

Create Table Tablename(columnname datayype unique)

.....................................................................................................................................................

Foreign key:-

The primary key of the Master table will be integrated into the child table to provide referential integrity constraint.

A foreign key can be repeatable and null.

Create table table1(col1 int primary key,...)   //Master Table

create table table2(col1 int primary key,col2 int foreign key references table1(col1));  //Child Table

Country and state table:-

countryid will be primary key and this countryid will be used in state table as a foreign key.

if we create foreign key then the foreign key column value will be dependent on primary key column value of the master table.


countryid  countryname
10              IND
20              USA
null            AUS   //wrong because null value not allowed
20              ENG   //wrong because primary key value should be unique



stateid statename countryid
101       MP          10

102       UP            10

103        AP          null

104        HP           40   //wrong because of 40 not present in the master table