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_idConstraint: 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 tableConstraint prevents: invalid state

Update Violation

UPDATE employees SET dept_id = 999 WHERE emp_id = 1;Error: dept_id 999 invalidUpdate 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 possibleALTER 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.