Relational Model (Tables, Schemas, Constraints)

Introduced by IBM researcher Edgar F. Codd in 1970, the Relational Model organizes data into mathematical relations based on first-order predicate logic and set theory:

• Relation (Table): A named set of ordered rows with identical column structures.
• Tuple (Row / Record): A single data entity containing an ordered collection of attribute values.
• Attribute (Column / Field): A named domain value with a strict data type (e.g., INTEGER, VARCHAR(255), DECIMAL(10,2), TIMESTAMP).
• Schema: The formal, declarative contract defining tables, columns, data types, indexes, and integrity constraints.

Why it won: Unlike older hierarchical or network databases that required procedural code to navigate hardcoded pointers, the relational model separates the logical data structure from the physical storage implementation, allowing declarative querying via SQL.

A relational database engine actively enforces four categories of data integrity constraints on every write operation:

1. Entity Integrity (Primary Key Constraint): Every table must have a Primary Key (PK) containing unique, non-null values that uniquely identify every individual row.
2. Referential Integrity (Foreign Key Constraint): A Foreign Key (FK) links a column in Table A to the Primary Key of Table B. The database prohibits inserting a row in Table A with an invalid FK referencing a non-existent row in Table B.
3. Domain Integrity (Check Constraints & Data Types): Restricts column values to valid types and ranges (e.g., CHECK (age >= 18), CHECK (status IN ('PENDING', 'PAID'))).
4. User-Defined Integrity (Unique Constraints & Triggers): Enforces business rules (e.g., UNIQUE(email) ensures no two users share an email address).

When a parent row is deleted, what happens to child rows referencing it?

• ON DELETE RESTRICT / NO ACTION (Default): The database throws an error and prohibits deleting the parent if child rows exist (e.g., cannot delete a Product if historic OrderItems reference it).
• ON DELETE CASCADE: Deleting the parent automatically deletes all child rows (e.g., deleting an Order automatically deletes its child OrderItems).
• ON DELETE SET NULL: Deleting the parent sets the child's FK column to NULL (e.g., deleting a Department sets employee department_id to NULL).

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    total_amount DECIMAL(12, 2) NOT NULL CHECK (total_amount >= 0),
    status VARCHAR(32) NOT NULL CHECK (status IN ('PENDING', 'PAID', 'CANCELLED')),
    CONSTRAINT fk_user FOREIGN KEY (user_id) 
        REFERENCES users(id) ON DELETE RESTRICT
);

-- Crucial: Always create explicit index on Foreign Key columns!
CREATE INDEX idx_orders_user_id ON orders(user_id);