Database - UNIQUE KEY & Foreign Key

'Unique Key(same as column level,table level,compsite unique key)'

The UNIQUE constraint uniquely identifies each record in a database table.  

Unique key does not allow duplicate values.

Unique allows null values,but only once.Any number of unique keys can be set

in a table.

create table emp3(empno int constraint uk_emp13 unique,

ename varchar(20),desg char(3),salary money,deptno int)

'FOREIGN KEY'

   Foreign key is an referential constraint.

A FOREIGN KEY in one table points to a PRIMARY KEY in another table. 

create table emp_det(empno int constraint pk_empdet primary key,

ename varchar(20),desg char(3),salary money,deptno int)

insert into emp_det values(111,'Vinoth','TRN',10000.00,10)

insert into emp_det values(112,'Karthik','TRN',20000.00,20)

insert into emp_det values(113,'Suresh','ACT',30000.00,30)

insert into emp_det values(114,'Suman','PRG',40000.00,40)

insert into emp_det values(115,'sachin','BAT',50000.00,50)

insert into emp_det values(116,'Yuvi','LFT',60000.00,60)

create table sal_det(salno int constraint fk_dno references emp_det(empno))

   Here, emp_det is called as parent table and sal_det is called as child table.

We can insert into the child table only those values that present only in

the parent table.

insert into sal_det values(111)

insert into sal_det values(112)

insert into sal_det values(113)

insert into sal_det values(114)

 If we insert other values in the child table,it is not allowed.

insert into sal_det values(214)

select *from emp_det

select *from sal_det

   If we want to delete a record first we must delete it from the

child table and then only must delete it from the parent table.

  delete from emp_det where empno=111--showa error

delete from sal_det where salno=111

delete from emp_det where empno=111