Referential Integrity Enforcement

Direct Answer

Referential integrity means each non-NULL foreign-key value matches a referenced primary key or UNIQUE candidate key. The database checks child inserts and updates, and it applies the configured action to referenced parent updates and deletes, so a committed change cannot leave a broken relationship.

Code Example

-- Creating tables with referential integrity constraints
CREATE TABLE customers (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10,2),
    CONSTRAINT fk_orders_customer 
        FOREIGN KEY (customer_id) 
        REFERENCES customers(id)
        ON DELETE RESTRICT  -- Blocks deleting customers with orders
);

-- This INSERT succeeds if customer 5 exists
INSERT INTO orders (customer_id, total_amount) VALUES (5, 100.00);

-- This INSERT fails: customer 999 doesn't exist
INSERT INTO orders (customer_id, total_amount) VALUES (999, 50.00);
-- ERROR: foreign key constraint violated

Common Mistakes & Pitfalls

• ORM-Only Relationships: Freshers define @ManyToOne in JPA entities without creating the database foreign key constraint, assuming Hibernate's in-memory relationship tracking is enough — but direct SQL queries or other applications can still corrupt data.
• Disabling for Performance: Dropping foreign key constraints to speed up bulk inserts and forgetting to recreate them leaves the database permanently vulnerable to orphaned records and inconsistent state.
• INSERT-Only Thinking: Assuming referential integrity only matters for DELETE operations, not realizing that INSERT and UPDATE are also validated — trying to insert an order with an invalid customer ID fails immediately at the database level, which can surprise freshers debugging failed transactions.