The Referential Integrity Rule in RDBMS is based on the relationship between Primary Keys and Foreign Keys. The rule states that a foreign key in one table must have a matching valid primary key in the referenced table, ensuring that references between tables are always valid.

Referential Integrity Rule

Referential integrity ensures that relationships between tables remain consistent. Specifically −

• A foreign key value must either match an existing primary key value in the referenced table, or be NULL.
• You cannot insert a foreign key value that does not exist in the referenced table.
• You cannot delete a primary key record if it is being referenced by a foreign key in another table (unless cascading is configured).
• The primary key and foreign key must have the same data type to allow joins.

Example

Consider two tables − Employee and Department ?

The DEPT_ID in the Employee table is a foreign key that references the DEPT_ID primary key in the Department table. Every DEPT_ID value in Employee must have a matching valid DEPT_ID in the Department table.

SQL Example

CREATE TABLE Department (
    DEPT_ID INT PRIMARY KEY,
    DEPT_NAME VARCHAR(50),
    DEPT_ZONE VARCHAR(50)
);

CREATE TABLE Employee (
    EMP_ID INT PRIMARY KEY,
    EMP_NAME VARCHAR(50),
    DEPT_ID INT,
    FOREIGN KEY (DEPT_ID) REFERENCES Department(DEPT_ID)
);

-- Valid: DEPT_ID 101 exists in Department
INSERT INTO Employee VALUES (1, 'Alice', 101);

-- Invalid: DEPT_ID 999 does not exist in Department
-- This will throw a referential integrity violation error
INSERT INTO Employee VALUES (2, 'Bob', 999);

Conclusion

Referential integrity ensures that foreign key values always point to valid primary key records in the referenced table, preventing orphaned records and maintaining consistent relationships across the database.