Referential integrity is a relationship between two tables one being the 'parent' and the other being the 'child'. The 'child' table references values in the 'parent' table's primary key to ensure all data between the two tables is properly related. Let's look at an example:
SQL> --
SQL> -- Create the EMP table
SQL> --
SQL> CREATE TABLE EMP
2 (EMPNO NUMBER(4) NOT NULL
3 ENAME VARCHAR2(10)
4 JOB VARCHAR2(9)
5 MGR NUMBER(4)
6 HIREDATE DATE
7 SAL NUMBER(7 2)
8 COMM NUMBER(7 2)
9 DEPTNO NUMBER(2)
10 constraint emp_pk
11 primary key(empno)
12 );
Table created.
SQL>
SQL> INSERT ALL
2 INTO EMP VALUES
3 (7369 'SMITH' 'CLERK' 7902
4 TO_DATE('17-DEC-1980' 'DD-MON-YYYY') 800 NULL 20)
5 INTO EMP VALUES
6 (7499 'ALLEN' 'SALESMAN' 7698
7 TO_DATE('20-FEB-1981' 'DD-MON-YYYY') 1600 300 30)
8 INTO EMP VALUES
9 (7521 'WARD' 'SALESMAN' 7698
10 TO_DATE('22-FEB-1981' 'DD-MON-YYYY') 1250 500 30)
11 INTO EMP VALUES
12 (7566 'JONES' 'MANAGER' 7839
13 TO_DATE('2-APR-1981' 'DD-MON-YYYY') 2975 NULL 20)
14 INTO EMP VALUES
15 (7654 'MARTIN' 'SALESMAN' 7698
16 TO_DATE('28-SEP-1981' 'DD-MON-YYYY') 1250 1400 30)
17 INTO EMP VALUES
18 (7698 'BLAKE' 'MANAGER' 7839
19 TO_DATE('1-MAY-1981' 'DD-MON-YYYY') 2850 NULL 30)
20 INTO EMP VALUES
21 (7782 'CLARK' 'MANAGER' 7839
22 TO_DATE('9-JUN-1981' 'DD-MON-YYYY') 2450 NULL 10)
23 INTO EMP VALUES
24 (7788 'SCOTT' 'ANALYST' 7566
25 TO_DATE('09-DEC-1982' 'DD-MON-YYYY') 3000 NULL 20)
26 INTO EMP VALUES
27 (7839 'KING' 'PRESIDENT' NULL
28 TO_DATE('17-NOV-1981' 'DD-MON-YYYY') 5000 NULL 10)
29 INTO EMP VALUES
30 (7844 'TURNER' 'SALESMAN' 7698
31 TO_DATE('8-SEP-1981' 'DD-MON-YYYY') 1500 0 30)
32 INTO EMP VALUES
33 (7876 'ADAMS' 'CLERK' 7788
34 TO_DATE('12-JAN-1983' 'DD-MON-YYYY') 1100 NULL 20)
35 INTO EMP VALUES
36 (7900 'JAMES' 'CLERK' 7698
37 TO_DATE('3-DEC-1981' 'DD-MON-YYYY') 950 NULL 30)
38 INTO EMP VALUES
39 (7902 'FORD' 'ANALYST' 7566
40 TO_DATE('3-DEC-1981' 'DD-MON-YYYY') 3000 NULL 20)
41 INTO EMP VALUES
42 (7934 'MILLER' 'CLERK' 7782
43 TO_DATE('23-JAN-1982' 'DD-MON-YYYY') 1300 NULL 10)
44 SELECT * FROM DUAL;
14 rows created.
SQL>
SQL> --
SQL> -- Create the DEPT table
SQL> --
SQL> CREATE TABLE DEPT
2 (DEPTNO NUMBER(2)
3 DNAME VARCHAR2(14)
4 LOC VARCHAR2(13)
5 constraint dept_pk
6 primary key(deptno)
7 );
Table created.
SQL>
SQL> INSERT ALL
2 INTO DEPT VALUES (10 'ACCOUNTING' 'NEW YORK')
3 INTO DEPT VALUES (20 'RESEARCH' 'DALLAS')
4 INTO DEPT VALUES (30 'SALES' 'CHICAGO')
5 INTO DEPT VALUES (40 'OPERATIONS' 'BOSTON')
6 SELECT * FROM DUAL;
4 rows created.
SQL>
SQL> COMMIT;
Commit complete.
SQL>
SQL> --
SQL> -- Add a foreign key to the EMP
SQL> -- table to prevent unreferenced
SQL> -- department numbers from being
SQL> -- used
SQL> --
SQL> ALTER TABLE EMP
2 ADD CONSTRAINT EMP_DEPT_FK
3 FOREIGN KEY(DEPTNO) REFERENCES DEPT;
Table altered.
The two tables EMP and DEPT are now 'related' using a foreign key as a referential integrity constraint. This will prevent any records from being inserted into the EMP table if the DEPTNO value doesn't exist in the DEPT table. Let's illustrate this with another example:
SQL>
SQL> --
SQL> -- Attempt to add a new employee
SQL> -- with a new department
SQL> --
SQL> INSERT
2 INTO EMP VALUES
3 (7969 'SMYTHE' 'CLERK' 7902
4 TO_DATE('17-DEC-1980' 'DD-MON-YYYY') 800 NULL 50);
INSERT
*
ERROR at line 1:
ORA-02291: integrity constraint (BING.EMP_DEPT_FK) violated - parent key not
found
As you can see the parent key (DEPTNO 50) doesn't exist in the DEPT table. Thus the child record cannot be added to the EMP table.
Another benefit of referential integrity is that any parent record associated with child records cannot be deleted from the parent table. To illustrate:
SQL>
SQL> --
SQL> -- Attempt to delete a department
SQL> -- associated with employees
SQL> --
SQL> DELETE FROM DEPT
2 WHERE DEPTNO 30;
DELETE FROM DEPT
*
ERROR at line 1:
ORA-02292: integrity constraint (BING.EMP_DEPT_FK) violated - child record
found
SQL>
Since DEPTNO 30 is associated with at least one employee in the EMP table dropping this department is not allowed.
A foreign key also does not need to be restricted to a single column; the reference in the child table is to the entire primary key of the parent. Modifying our previous example by adding the DNAME column to the EMP table and including it in the primary key and foreign key definitions we see:
SQL> --
SQL> -- Create the EMP table
SQL> --
SQL> CREATE TABLE EMP
2 (EMPNO NUMBER(4) NOT NULL
3 ENAME VARCHAR2(10)
4 JOB VARCHAR2(9)
5 MGR NUMBER(4)
6 HIREDATE DATE
7 SAL NUMBER(7 2)
8 COMM NUMBER(7 2)
9 DEPTNO NUMBER(2)
10 DNAME VARCHAR2(14)
11 constraint emp_pk
12 primary key(empno)
13 );
Table created.
SQL>
SQL> INSERT ALL
2 INTO EMP VALUES
3 (7369 'SMITH' 'CLERK' 7902
4 TO_DATE('17-DEC-1980' 'DD-MON-YYYY') 800 NULL 20 'RESEARCH')
5 INTO EMP VALUES
6 (7499 'ALLEN' 'SALESMAN' 7698
7 TO_DATE('20-FEB-1981' 'DD-MON-YYYY') 1600 300 30 'SALES')
8 INTO EMP VALUES
9 (7521 'WARD' 'SALESMAN' 7698
10 TO_DATE('22-FEB-1981' 'DD-MON-YYYY') 1250 500 30 'SALES')
11 INTO EMP VALUES
12 (7566 'JONES' 'MANAGER' 7839
13 TO_DATE('2-APR-1981' 'DD-MON-YYYY') 2975 NULL 20 'RESEARCH')
14 INTO EMP VALUES
15 (7654 'MARTIN' 'SALESMAN' 7698
16 TO_DATE('28-SEP-1981' 'DD-MON-YYYY') 1250 1400 30 'SALES')
17 INTO EMP VALUES
18 (7698 'BLAKE' 'MANAGER' 7839
19 TO_DATE('1-MAY-1981' 'DD-MON-YYYY') 2850 NULL 30 'SALES')
20 INTO EMP VALUES
21 (7782 'CLARK' 'MANAGER' 7839
22 TO_DATE('9-JUN-1981' 'DD-MON-YYYY') 2450 NULL 10 'ACCOUNTING')
23 INTO EMP VALUES
24 (7788 'SCOTT' 'ANALYST' 7566
25 TO_DATE('09-DEC-1982' 'DD-MON-YYYY') 3000 NULL 20 'RESEARCH')
26 INTO EMP VALUES
27 (7839 'KING' 'PRESIDENT' NULL
28 TO_DATE('17-NOV-1981' 'DD-MON-YYYY') 5000 NULL 10 'ACCOUNTING')
29 INTO EMP VALUES
30 (7844 'TURNER' 'SALESMAN' 7698
31 TO_DATE('8-SEP-1981' 'DD-MON-YYYY') 1500 0 30 'SALES')
32 INTO EMP VALUES
33 (7876 'ADAMS' 'CLERK' 7788
34 TO_DATE('12-JAN-1983' 'DD-MON-YYYY') 1100 NULL 20 'RESEARCH')
35 INTO EMP VALUES
36 (7900 'JAMES' 'CLERK' 7698
37 TO_DATE('3-DEC-1981' 'DD-MON-YYYY') 950 NULL 30 'SALES')
38 INTO EMP VALUES
39 (7902 'FORD' 'ANALYST' 7566
40 TO_DATE('3-DEC-1981' 'DD-MON-YYYY') 3000 NULL 20 'RESEARCH')
41 INTO EMP VALUES
42 (7934 'MILLER' 'CLERK' 7782
43 TO_DATE('23-JAN-1982' 'DD-MON-YYYY') 1300 NULL 10 'ACCOUNTING')
44 SELECT * FROM DUAL;
14 rows created.
SQL>
SQL> --
SQL> -- Create the DEPT table
SQL> --
SQL> CREATE TABLE DEPT
2 (DEPTNO NUMBER(2)
3 DNAME VARCHAR2(14)
4 LOC VARCHAR2(13)
5 constraint dept_pk
6 primary key(deptno dname)
7 );
Table created.
SQL>
SQL> INSERT ALL
2 INTO DEPT VALUES (10 'ACCOUNTING' 'NEW YORK')
3 INTO DEPT VALUES (20 'RESEARCH' 'DALLAS')
4 INTO DEPT VALUES (30 'SALES' 'CHICAGO')
5 INTO DEPT VALUES (40 'OPERATIONS' 'BOSTON')
6 SELECT * FROM DUAL;
4 rows created.
SQL>
SQL> COMMIT;
Commit complete.
SQL>
SQL> --
SQL> -- Add a foreign key to the EMP
SQL> -- table to prevent unreferenced
SQL> -- department numbers from being
SQL> -- used
SQL> --
SQL> ALTER TABLE EMP
2 ADD CONSTRAINT EMP_DEPT_FK
3 FOREIGN KEY(DEPTNO DNAME) REFERENCES DEPT;
Table altered.
SQL>
SQL> --
SQL> -- Attempt to add a new employee
SQL> -- with an existing department
SQL> -- but the wrong department name
SQL> --
SQL> INSERT
2 INTO EMP VALUES
3 (7969 'SMYTHE' 'CLERK' 7902
4 TO_DATE('17-DEC-1980' 'DD-MON-YYYY') 800 NULL 40 'MARKETING');
INSERT
*
ERROR at line 1:
ORA-02291: integrity constraint (BING.EMP_DEPT_FK) violated - parent key not
found
Notice that department 40 does exist in the DEPT table but it's named 'OPERATIONS' not 'MARKETING' thus the insert failure.
SQL>
Referential integrity ensures the quality of the data in a database since orphan records cannot exist either through erroneous inserts into the child table or through inadvertent deletes from the parent table.
