Referential Integrity

Introduction

Referential integrity: consistency across tables. Foreign key: links to primary key of another table. Constraint: enforces relationship. Violation: prevented by database. Foundation: data consistency.

"Referential integrity ties tables together: one-to-many relationships, enforced by foreign keys. Database maintains consistency: no orphan records, no invalid references." -- Relational model

Foreign Key Concept

Definition

Column(s): reference primary key of another table. Value: must exist in referenced table (or NULL). Establishes: one-to-many relationship. Maintained: database enforces.

Example

Employees table: has dept_id (foreign key)
Department table: has dept_id (primary key)

Each employee: references department by dept_id
Constraint: dept_id value must exist in Department table

Notation

FK: foreign key (abbreviated). Shown: in schema diagrams as arrow (pointing to primary key). Syntax: FOREIGN KEY clause in CREATE TABLE.

Self-Referencing

Foreign key: can reference same table. Example: manager_id references employee_id (hierarchical). Enables: recursive relationships.

Foreign Key Constraints

Definition

Rule: column value must exist in referenced table. Insertion: checked (INSERT fails if no match). Update: checked (UPDATE fails if invalid). Deletion: checked (DELETE fails if referenced).

SQL Syntax

CREATE TABLE employees (
 emp_id INT PRIMARY KEY,
 name VARCHAR(100),
 dept_id INT,
 FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

Multi-Column Foreign Key

FOREIGN KEY (year, month) REFERENCES calendar(year, month);

Named Constraint

CONSTRAINT fk_employee_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id);

Violation Prevention

Insert Violation

INSERT INTO employees (emp_id, name, dept_id)
VALUES (100, 'Alice', 999);

Error: dept_id 999 doesn't exist in departments table
Constraint prevents: invalid state

Update Violation

UPDATE employees SET dept_id = 999 WHERE emp_id = 1;

Error: dept_id 999 invalid
Update rejected: maintaining integrity

Delete Cascade Issue

DELETE FROM departments WHERE dept_id = 10;

Behavior depends: ON DELETE action
 RESTRICT: error (has employees)
 CASCADE: deletes all employees in dept 10
 SET NULL: employees.dept_id becomes NULL

Prevention Strategy

Database enforces: developer doesn't bypass. Errors caught: application handles (user feedback). Consistency: guaranteed.

Cascading Actions

ON DELETE Actions

RESTRICT: error if referenced (default). CASCADE: delete child records. SET NULL: nullify foreign key. SET DEFAULT: use default value.

ON UPDATE Actions

RESTRICT: error if referenced. CASCADE: update child foreign keys. SET NULL: nullify. SET DEFAULT: use default value.

Example Cascade

CREATE TABLE employees (
 ...
 dept_id INT,
 FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
 ON DELETE CASCADE
 ON UPDATE CASCADE
);

Delete department: all employees deleted (cascade)
Update dept_id: all employees' dept_id updated (cascade)

Caution

CASCADE: dangerous (unintended deletions). Careful: design cascades. Document: for understanding. Alternative: RESTRICT + explicit logic.

Orphan Records

Definition

Child record: parent deleted (no valid reference). Orphan: exists without parent. Database prevents (no orphans with constraint). Possible: if constraint disabled (risky).

Example

Without constraint:
 Delete department 10
 Employee records with dept_id=10: orphaned (invalid)

With constraint (RESTRICT):
 Delete fails: error (department has employees)
 Orphans prevented: consistency maintained

Data Corruption

Orphans indicate: constraint violation. Possible causes: disabled constraints, buggy code. Cleanup: find and remove orphans (manual fix, costly).

Prevention

Enable: foreign key constraints (default, standard). Verify: on startup (check existing data). Audit: periodic validation.

Enforcement Mechanisms

Database Level

Triggered: on DML operations (INSERT, UPDATE, DELETE). Check: primary key exists in referenced table. Index: on referenced key (fast lookup). Automatic: transparent to application.

Deferred Checking

Immediate: checked after each statement. Deferred: checked at transaction end. Flexibility: allows temporary violations (complex multi-step transactions). Careful: ensure final state valid.

Application Level

Validation: before INSERT/UPDATE (redundant, but safe). Error handling: catch database errors (graceful). Logging: track violations (audit trail).

Disabling Constraints

ALTER TABLE employees DISABLE CONSTRAINT fk_employee_dept;
-- Risky: orphans possible

ALTER TABLE employees ENABLE CONSTRAINT fk_employee_dept;
-- Re-enable (may fail if orphans exist)

Complex Scenarios

Circular Foreign Keys

A -> B -> A (mutual references). Allowed: if handled carefully. Insertion: deferred constraints (allow temporary violation). Deletion: careful order.

Self-Referencing Hierarchy

Employee: has manager_id (foreign key to employee_id)
Enables: organizational structure (reporting relationships)
Tree structure: parent-child relationships

Multi-Table Join Dependencies

Complex: multiple foreign keys referencing different tables. Design: ensure no cycles (or handle carefully). Testing: verify cascade behavior.

Soft Deletes

Instead of DELETE: mark as deleted (status column). Foreign key: still references. Advantages: preserves history, easy undo. Disadvantage: logic (must filter deleted).

Practical Examples

Simple One-to-Many

CREATE TABLE departments (
 dept_id INT PRIMARY KEY,
 dept_name VARCHAR(50)
);

CREATE TABLE employees (
 emp_id INT PRIMARY KEY,
 name VARCHAR(100),
 dept_id INT NOT NULL,
 FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
 ON DELETE RESTRICT
);

With Cascading

CREATE TABLE projects (
 project_id INT PRIMARY KEY,
 dept_id INT,
 FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
 ON DELETE CASCADE
);

Delete department: projects deleted automatically

Self-Referencing

CREATE TABLE employees (
 emp_id INT PRIMARY KEY,
 name VARCHAR(100),
 manager_id INT,
 FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
 ON DELETE SET NULL
);

Composite Foreign Key

CREATE TABLE order_items (
 order_id INT NOT NULL,
 item_id INT NOT NULL,
 quantity INT,
 PRIMARY KEY (order_id, item_id),
 FOREIGN KEY (order_id) REFERENCES orders(order_id),
 FOREIGN KEY (item_id) REFERENCES products(item_id)
);

References

• Ramakrishnan, R., and Gehrke, J. "Database Management Systems." McGraw-Hill, 3rd edition, 2003.
• Silberschatz, A., Korth, H. F., and Sudarshan, S. "Database System Concepts." McGraw-Hill, 6th edition, 2010.