Foreign key constraint with example

A foreign key constraint (also called referential integrity constraint) on a column ensures that the value in that column is found in the primary key of another table.
If a table has a foreign key that references a table, that referenced table can be dropped with a drop table .. cascade constraints.
It is not possible to establish a foreign key on a global temporary table. If tried, Oracle issues a ORA-14455: attempt to create referential integrity constraint on temporary table.

Referential integrity constraint is that which depend upon the parent and child relationship. In this one of the column have the primary key constraint and one of the column of another table have the foreign key constraint. When you create a foreign key constraint, Oracle default to "on delete restrict" to ensure that a parent rows cannot be deleted while a child row still exists. If you tried to do this it will show you error related to referential integrity constraint.
But if you still want to delete value from child table but never want to delete parent table column value then that purpose you can use 'on delete cascade' option.

The referenced table is called the parent table while the table with the foreign key is called the child table. The foreign key in the child table will generally reference a primary key in the parent table.
A foreign key with a cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table with automatically be deleted. This is called a cascade delete.

A foreign key with a cascade delete can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

Below given an example for foreign key constraint

SQL> create table salary(
2 rollno number(5), name varchar(15), design varchar(15),
3 bpay number(7,2), da number(6,2), total number(8,2),
4 constraint salary_pk primary key(rollno));

Table created.

SQL> insert into salary values(&rollno , '&name','&design',&bpay,&da,&total)
2 ;
Enter value for rollno: 10001
Enter value for name: S.Krishnan
Enter value for design: HOD
Enter value for bpay: 25000
Enter value for da: 1500
Enter value for total: 26500
old 1: insert into salary values(&rollno , '&name','&design',&bpay,&da,&total)
new 1: insert into salary values(10001 , 'S.Krishnan','HOD',25000,1500,26500)

1 row created.

SQL>

Let insert some more records into salary table

SQL> select * from salary;

ROLLNO NAME DESIGN BPAY DA TOTAL
---------- --------------- --------------- ---------- ---------- ----------
10001 S.Krishnan HOD 25000 1500 26500
10002 K.K.Omana Asst.Manager 20000 1200 21200
10003 Anishkumar. K Asst.Manager 20000 1200 21200
10004 Girishkumar .K Asst.Manager 20000 1200 21200
10005 Arunkumar. K Programmer 15000 1000 17000

Now create another table named address with foreign key

SQL> create table address( rollno number(5) , address varchar(30),
2 pincode number(6),
3 constraint address_fk foreign key(rollno) references salary(rollno));

Table created.

SQL> insert into address values (&rollno,'&address',&pincode);
Enter value for rollno: 10001
Enter value for address: thaliyil veettil
Enter value for pincode: 670548
old 1: insert into address values (&rollno,'&address',&pincode)
new 1: insert into address values (10001,'thaliyil veettil',670548)

1 row created.

SQL> /
Enter value for rollno: 10002
Enter value for address: panakkada house
Enter value for pincode: 670581
old 1: insert into address values (&rollno,'&address',&pincode)
new 1: insert into address values (10002,'panakkada house',670581)

1 row created.

SQL> /
Enter value for rollno: 10004
Enter value for address: embron house
Enter value for pincode: 670481
old 1: insert into address values (&rollno,'&address',&pincode)
new 1: insert into address values (10004,'embron house',670481)

1 row created.

SQL> /
Enter value for rollno: 10009
Enter value for address: niya manzil
Enter value for pincode: 670426
old 1: insert into address values (&rollno,'&address',&pincode)
new 1: insert into address values (10009,'niya manzil',670426)
insert into address values (10009,'niya manzil',670426)
*
ERROR at line 1:
ORA-02291: integrity constraint (MAHI.ADDRESS_FK) violated - parent key not
found


SQL> select * from address;

ROLLNO ADDRESS PINCODE
---------- ------------------------------ ----------
10001 thaliyil veettil 670548
10002 panakkada house 670581
10004 embron house 670481

SQL>

Also you can't delete a parent table column value if corresponding column in the child table still exist.

For example,

SQL> delete from salary where rollno=10002;
delete from salary where rollno=10002
*
ERROR at line 1:
ORA-02292: integrity constraint (MAHI.ADDRESS_FK) violated - child record found
SQL>

By using the 'on delete cascade' you can avoid this problem , for that we have to alter the table with 'on delete cascade' option. so rearrange the table as follow,

SQL>alter table address drop constraint address_fk;

SQL>alter table address add constraint address_fk foreign key(rollno)
references salary(rollno) on delete cascade;

Now try to delete the record

SQL> delete from salary where rollno=10002;

1 row deleted.

SQL>