What is referential integrity in SQL?

Referential integrity in SQL is a rule that ensures the relationship between two tables remains consistent — it makes sure that foreign keys in one table always refer to valid primary keys in another table.

In simpler terms, it prevents you from having orphan records — for example, an order that references a customer who doesn’t exist.

Let’s say we have two tables:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Here, the FOREIGN KEY constraint enforces referential integrity. It means:

• You can’t insert an order with a CustomerID that doesn’t exist in Customers.
• You can’t delete a customer if they still have related orders, unless you handle it explicitly (like using ON DELETE CASCADE).

For example, if you try:

INSERT INTO Orders VALUES (101, 999, '2025-10-30');

You’ll get an error because there’s no customer with CustomerID = 999.

I’ve applied referential integrity in multiple real-world database designs, especially in sales and HR systems, to ensure data consistency between master and transaction tables — like keeping employees linked correctly to their departments or products to their categories.

A challenge I’ve faced is when dealing with data imports — sometimes legacy data violates referential integrity, and cleaning it up before enforcing constraints takes time.

To handle that, I first load data into staging tables, validate relationships, and then insert only clean records into the main tables with constraints enabled.

In short, referential integrity ensures that relationships between tables stay valid and the data remains consistent, accurate, and reliable throughout the database.