Referential Integrity in DBMS With Example

Referential integrity is a database rule that ensures relationships between tables remain consistent — specifically, that a foreign key value in one table always corresponds to an existing primary key value in another table.

Why Referential Integrity Matters

Without referential integrity, you could end up with "orphaned" records — for example, an order in an orders table pointing to a customer ID that no longer exists in the customers table. This breaks data reliability and can crash applications relying on that data.

How It's Enforced

Referential integrity is enforced using foreign key constraints. When you define a foreign key, the database automatically checks that any value inserted matches an existing primary key in the referenced table.

Example

CREATE TABLE customers (customer_id INT PRIMARY KEY, name VARCHAR(100));

CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(customer_id));

With this setup, trying to insert an order with a customer_id that doesn't exist in the customers table will be rejected by the database.

Referential Actions

Referential integrity is a core part of maintaining a reliable relational database, and understanding it is essential before moving on to broader concepts like ACID properties and transactions.