FOREIGN KEY

Pre-requisits : General understanding of integrity constraints.

Constrains are used to prevent invalid data into the table.

Integrity constrains are business rules enforced on the data in a table. The enforced rules trigger whenever a row is inserted, updated, or deleted from that table and prevent data that does not meet the set rules.

FOREIGN KEY : Establishes and enforces a relationship between two tables or to the same table itself.

The foreign key or referential integrity constraint, designates a column or combination of columns as a foreign key and establishes a relationship between a primary key or a unique key in the same table or a different table.

 In the example below   Emp_child  table's deptno   has been defined as the foreign key ; it references the deptno column of the Dept_parent table.

Implementation:

Step 1

Create parent table DEPT_PARENT as follows:

Create table DEPT_PARENT

( Deptno number(2) primary key,

  Dname varchar2(15),

  Loc varchar2(15)

);

Step 2

Insert rows manually or from existing default table like Dept as follows:

Insert into Dept_Parent ( select * from dept);

Commit;

Verify your data in the table by a select statement as follows :

Select * from dept_parent.

Step 3

Create Child table emp_child as follows :

CREATE TABLE EMP_child

       (EMPNO NUMBER(4) NOT NULL,

        ENAME VARCHAR2(10),

        JOB VARCHAR2(9),

        MGR NUMBER(4),

        HIREDATE DATE,

        SAL NUMBER(7, 2),

        COMM NUMBER(7, 2),

        DEPTNO NUMBER(2) references dept_parent(deptno)

        );

While creating the child table , the last line ( references dept_parent(deptno) ) is the syntax that binds the parent and the child table.

Step 4

Having established foreign key / referential integrity between two tables, we can now test the functionality of referential integrity.

To test the following functionality , load emp_child table with the data in default table emp.

Insert into emp_child (select * from emp );

Commit;

Functionality of referential integrity : 

1) No record in table emp_child can be inserted without the deptno colum of the emp_child table having one of the values that exist in the deptno column of dept_parent.

INSERT INTO EMP VALUES

        (1111, 'SMITH',  'CLERK',     7902,

        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 21);

     2) No record in table dept_master can be deleted when corresponding records exist in emp_child.

DELETE FROM DEPT_PARENT WHERE DEPTNO= 20;

    3) Deptno column in emp_child table cannot be updated with any other value that does not exist in the deptno column of dept_parent.

UPDATE EMP_CHILD SET DEPTNO =11 WHERE DEPTNO =10;

4)      Any record in emp_child can be deleted irrespective of its parent record in Dept_parent.

DELETE FROM EMP_CHILD WHERE DEPTNO= 20;

Step 5

Understanding the option  ON DELETE CASCADE:

Deletes the dependent rows in the child table when a row in the parent table is deleted.

To practice this concept follow the below:

Drop table emp_child purge;

Create Child table emp_child as follows :

CREATE TABLE EMP_child

       (EMPNO NUMBER(4) NOT NULL,

        ENAME VARCHAR2(10),

        JOB VARCHAR2(9),

        MGR NUMBER(4),

        HIREDATE DATE,

        SAL NUMBER(7, 2),

        COMM NUMBER(7, 2),

        DEPTNO NUMBER(2) references dept_parent(deptno) on delete cascade

        );

Insert rows from emp table as follows :

Insert into emp_child ( select * from emp);

Commit;

Delete a record from dept_parent and check if corresponding records are deleted in the emp_child table.

Delete from dept_parent where deptno =10;

Commit;

Select * from dept_parent;

Select * from emp_Child ; -- you would not find records for deptno 10.

  

Step 6

Understanding the option  ON DELETE SET NULL:

Setting null to child date column DEPTNO  when a row in the parent table is deleted.

To practice this concept follow the below:

Drop table emp_child;

Create Child table emp_child as follows :

CREATE TABLE EMP_child

       (EMPNO NUMBER(4) NOT NULL,

        ENAME VARCHAR2(10),

        JOB VARCHAR2(9),

        MGR NUMBER(4),

        HIREDATE DATE,

        SAL NUMBER(7, 2),

        COMM NUMBER(7, 2),

        DEPTNO NUMBER(2) references dept_parent(deptno) on delete set null

        );

Insert rows from emp table as follows :

Insert into emp_child ( select * from emp );

Commit;

Delete a record from dept_parent and check if corresponding  column DEPTNO  in the emp_child table is set to NULL.

Delete from dept_parent where deptno =10;

Commit;

Select * from dept_parent;

Select * from emp_Child ; -- you would find DEPTNO column set to null for employees who  work for deptno 10.