Referential Integrity

In order for the database to work properly, it is essential to guarantee that a foreign key always refers to a record which exists in the other table. This is known as referential integrity.

Example: If "New Delhi Branch" is a branch name appearing in one of the tuples in the account relation, then there exists a tuple in the branch relation for branch "New Delhi Branch".

Referential integrity is usually enforced by the combination of a primary key and a foreign key. For referential integrity to hold, any field in a table that is declared a foreign key can contain only values from a parent table's primary key field.

Referential integrity is a feature provided by relational database management systems (RDBMS’s) that prevents users or applications from entering inconsistent data.

Referential integrity is a database constraint that ensures that references between data are indeed valid and intact.

The main objective of Referential integrity is to maintain data of the two base relations in consistent state during tuple insertion, deletion and modification.

Example of Referential Integrity

The SQL statement defining the parent table, DEPARTMENT, is:

CREATE TABLE DEPARTMENT
      (DEPTNO    CHAR(3)     NOT NULL,
       DEPTNAME  VARCHAR(29) NOT NULL,
       MGRNO     CHAR(6),
       ADMRDEPT  CHAR(3)     NOT NULL,
       LOCATION  CHAR(16),
          PRIMARY KEY (DEPTNO))
   IN RESOURCE 

The SQL statement defining the dependent table, EMPLOYEE, is:

CREATE TABLE EMPLOYEE
      (EMPNO     CHAR(6)     NOT NULL PRIMARY KEY,
       FIRSTNME  VARCHAR(12) NOT NULL,
       LASTNAME  VARCHAR(15) NOT NULL,
       WORKDEPT  CHAR(3),
       PHONENO   CHAR(4),
       PHOTO     BLOB(10m)   NOT NULL,
          FOREIGN KEY DEPT (WORKDEPT)
          REFERENCES DEPARTMENT ON DELETE NO ACTION)
   IN RESOURCE 

By specifying the DEPTNO column as the primary key of the DEPARTMENT table and WORKDEPT as the foreign key of the EMPLOYEE table, you are defining a referential constraint on the WORKDEPT values. This constraint enforces referential integrity between the values of the two tables. In this case, any employees that are added to the EMPLOYEE table must have a department number that can be found in the DEPARTMENT table.



References:
Referential Integrity Is Important For Databases
Infocenter