Tables_constraints

Chapter 6: Creating Tables & Constraints

Building Strong Data Foundations

---

6.1 Table Creation Fundamentals

Tables are the fundamental building blocks of any relational database. Proper table design with appropriate constraints ensures data integrity and optimal performance.

                    Table Creation Process
    ========================================================================

                              Start: Design Table
                                        |
                                        v
                           +----------------------+
                           | Define Columns       |
                           | - Name              |
                           | - Data Type         |
                           | - Default Value     |
                           +----------------------+
                                        |
                                        v
                           +----------------------+
                           | Add Constraints     |
                           | - Primary Key       |
                           | - Foreign Key       |
                           | - Unique            |
                           | - Not Null          |
                           | - Check             |
                           +----------------------+
                                        |
                                        v
                           +----------------------+
                           | Create Indexes      |
                           | - Performance       |
                           | - Query Patterns    |
                           +----------------------+
                                        |
                                        v
                           +----------------------+
                           | Table Created!      |
                           +----------------------+
    ========================================================================

---

6.2 Basic Table Creation

Simple Table with Common Options

-- Create a basic users table
CREATE TABLE users (
    -- Primary key with auto-increment
    id SERIAL PRIMARY KEY,

    -- Character columns
    username VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,

    -- Boolean
    is_active BOOLEAN DEFAULT TRUE,
    is_verified BOOLEAN DEFAULT FALSE,

    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- Unique constraint
    UNIQUE(email)
);

-- Add comments for documentation
COMMENT ON TABLE users IS 'User accounts for the application';
COMMENT ON COLUMN users.email IS 'User email address, must be unique';

Table with All Constraint Types

CREATE TABLE products (
    -- PRIMARY KEY
    id BIGSERIAL PRIMARY KEY,

    -- NOT NULL constraint
    name VARCHAR(200) NOT NULL,
    description TEXT,
    sku VARCHAR(50) NOT NULL,

    -- UNIQUE constraint
    barcode VARCHAR(100) UNIQUE,

    -- CHECK constraint (inline)
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    quantity INTEGER NOT NULL CHECK (quantity >= 0),
    discount_percent DECIMAL(5,2) DEFAULT 0 CHECK (discount_percent >= 0 AND discount_percent <= 100),

    -- FOREIGN KEY
    category_id INTEGER REFERENCES categories(id),
    supplier_id INTEGER REFERENCES suppliers(id),

    -- DEFAULT values
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

---

6.3 Primary Keys

Primary keys uniquely identify each row in a table.

                    Primary Key Types
    ========================================================================

    1. SERIAL (Auto-increment Integer)
    ================================

    CREATE TABLE orders (
        id SERIAL PRIMARY KEY,
        ...
    );

    -- Creates sequence: orders_id_seq
    -- Equivalent to:
    CREATE SEQUENCE orders_id_seq;
    CREATE TABLE orders (
        id INTEGER DEFAULT nextval('orders_id_seq') PRIMARY KEY,
        ...
    );

    2. BIGSERIAL (Large Auto-increment)
    ====================================

    CREATE TABLE events (
        id BIGSERIAL PRIMARY KEY,  -- For high-volume tables
        ...
    );

    3. UUID (Universally Unique)
    ============================

    CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

    CREATE TABLE sessions (
        id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
        ...
    );

    4. Composite Primary Key
    ========================

    CREATE TABLE order_items (
        order_id INTEGER REFERENCES orders(id),
        product_id INTEGER REFERENCES products(id),
        quantity INTEGER NOT NULL,

        PRIMARY KEY (order_id, product_id)  -- Composite key
    );

    5. Natural Key
    =============

    CREATE TABLE countries (
        country_code CHAR(2) PRIMARY KEY,  -- ISO country code
        name VARCHAR(100) NOT NULL
    );

    Primary Key Best Practices:
    ===========================

    ┌─────────────────────────────────────────────────────────────────────┐
    │  ✓ Always use a primary key on every table                        │
    │  ✓ Use surrogate keys (SERIAL, UUID) unless natural key exists  │
    │  ✓ Keep primary keys small (INTEGER preferred)                  │
    │  ✓ Never reuse deleted primary key values                        │
    │  ✓ Consider UUID for distributed systems                        │
    └─────────────────────────────────────────────────────────────────────┘

---

6.4 Foreign Keys

Foreign keys enforce referential integrity between tables.

                    Foreign Key Implementation
    ========================================================================

    1. Basic Foreign Key
    ====================

    CREATE TABLE orders (
        id SERIAL PRIMARY KEY,
        user_id INTEGER NOT NULL,
        total_amount DECIMAL(10,2),

        FOREIGN KEY (user_id) REFERENCES users(id)
    );

    2. Foreign Key with ON DELETE
    =============================

    CREATE TABLE orders (
        id SERIAL PRIMARY KEY,
        user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        -- Options: CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION
        ...
    );

    Foreign Key Actions:
    ====================

    ┌─────────────────────────────────────────────────────────────────────┐
    │  Action       │  Behavior                                          │
    ├───────────────┼────────────────────────────────────────────────────┤
    │  CASCADE      │  Delete/update child rows when parent deleted     │
    ├───────────────┼────────────────────────────────────────────────────┤
    │  SET NULL     │  Set foreign key to NULL when parent deleted      │
    ├───────────────┼────────────────────────────────────────────────────┤
    │  SET DEFAULT  │  Set foreign key to default when parent deleted  │
    ├───────────────┼────────────────────────────────────────────────────┤
    │  RESTRICT     │  Prevent deletion of parent (default)             │
    ├───────────────┼────────────────────────────────────────────────────┤
    │  NO ACTION    │  Delay check until end of transaction             │
    └───────────────┴────────────────────────────────────────────────────┘

    3. Foreign Key with ON UPDATE
    ============================

    CREATE TABLE order_items (
        order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE ON UPDATE CASCADE,
        product_id INTEGER REFERENCES products(id),
        quantity INTEGER NOT NULL,
        PRIMARY KEY (order_id, product_id)
    );

    4. Self-Referential Foreign Key
    ===============================

    CREATE TABLE employees (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        manager_id INTEGER REFERENCES employees(id),
        department_id INTEGER REFERENCES departments(id)
    );

    5. Multiple Foreign Keys
    =======================

    CREATE TABLE transactions (
        id SERIAL PRIMARY KEY,
        from_account_id INTEGER NOT NULL REFERENCES accounts(id),
        to_account_id INTEGER NOT NULL REFERENCES accounts(id),
        amount DECIMAL(15,2) NOT NULL,

        -- Ensure from != to
        CONSTRAINT valid_transfer CHECK (from_account_id <> to_account_id)
    );

    Foreign Key Examples:
    =====================

    -- Full example with all options
    CREATE TABLE posts (
        id SERIAL PRIMARY KEY,
        author_id INTEGER NOT NULL
            REFERENCES users(id)
            ON DELETE RESTRICT,
        category_id INTEGER
            REFERENCES categories(id)
            ON DELETE SET NULL,
        title VARCHAR(200) NOT NULL,
        content TEXT,
        published_at TIMESTAMP,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

    -- Add foreign key to existing table
    ALTER TABLE orders
    ADD CONSTRAINT fk_orders_users
    FOREIGN KEY (user_id) REFERENCES users(id);

    -- Remove foreign key
    ALTER TABLE orders DROP CONSTRAINT fk_orders_users;

---

6.5 Unique Constraints

Unique constraints ensure no duplicate values in specified columns.

                    Unique Constraint Types
    ========================================================================

    1. Single Column Unique
    ======================

    CREATE TABLE users (
        id SERIAL PRIMARY KEY,
        email VARCHAR(255) NOT NULL UNIQUE,
        ...
    );

    -- Or as constraint
    CREATE TABLE users (
        id SERIAL PRIMARY KEY,
        email VARCHAR(255) NOT NULL,
        ...
        CONSTRAINT uk_users_email UNIQUE (email)
    );

    2. Composite Unique Constraint
    ==============================

    CREATE TABLE user_roles (
        user_id INTEGER NOT NULL REFERENCES users(id),
        role_id INTEGER NOT NULL REFERENCES roles(id),
        assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

        CONSTRAINT uk_user_roles UNIQUE (user_id, role_id)
    );

    3. Unique Index (More Flexible)
    ===============================

    CREATE UNIQUE INDEX idx_users_username_lower
    ON users(LOWER(username));

    -- Allows case-insensitive unique constraint
    INSERT INTO users (username) VALUES ('John');  -- OK
    INSERT INTO users (username) VALUES ('john');  -- Fails (duplicate)

    4. Partial Unique Index
    ======================

    CREATE UNIQUE INDEX idx_users_active_email
    ON users(email)
    WHERE is_active = TRUE;

    -- Only enforces uniqueness for active users

    Unique Constraint vs Primary Key:
    ==================================

    ┌─────────────────────────────────────────────────────────────────────┐
    │  Feature            │  Primary Key      │  Unique Constraint      │
    ├─────────────────────┼───────────────────┼─────────────────────────┤
    │  One per table      │  Only one         │  Multiple allowed       │
    ├─────────────────────┼───────────────────┼─────────────────────────┤
    │  NULL values        │  Not allowed      │  Allowed (unless NOT NUL│
    ├─────────────────────┼───────────────────┼─────────────────────────┤
    │  Clustered index   │  Yes (default)    │  Optional               │
    ├─────────────────────┼───────────────────┼─────────────────────────┤
    │  Purpose            │  Row identifier  │  Business rule          │
    └─────────────────────┴───────────────────┴─────────────────────────┘

---

6.6 Check Constraints

Check constraints validate data based on custom conditions.

                    Check Constraint Examples
    ========================================================================

    1. Simple Check Constraints
    ==========================

    CREATE TABLE products (
        id SERIAL PRIMARY KEY,
        name VARCHAR(200) NOT NULL,
        price DECIMAL(10,2) NOT NULL CHECK (price > 0),
        quantity INTEGER NOT NULL CHECK (quantity >= 0),
        discount DECIMAL(5,2) CHECK (discount BETWEEN 0 AND 100)
    );

    2. Column-Level vs Table-Level
    =============================

    -- Column-level (inline)
    CREATE TABLE orders (
        id SERIAL PRIMARY KEY,
        status VARCHAR(20) CHECK (status IN ('pending', 'processing', 'shipped', 'delivered'))
    );

    -- Table-level (multiple columns)
    CREATE TABLE bookings (
        check_in DATE,
        check_out DATE,
        CONSTRAINT valid_dates CHECK (check_out > check_in)
    );

    3. Complex Check Constraints
    ===========================

    CREATE TABLE employees (
        id SERIAL PRIMARY KEY,
        hire_date DATE NOT NULL,
        termination_date DATE,
        salary DECIMAL(10,2),

        -- Termination date must be after hire date
        CONSTRAINT valid_termination_date
        CHECK (termination_date IS NULL OR termination_date >= hire_date),

        -- Salary constraints based on employment status
        CONSTRAINT valid_salary
        CHECK (
            (termination_date IS NULL AND salary > 0) OR
            (termination_date IS NULL AND salary IS NULL) OR
            termination_date IS NOT NULL
        )
    );

    4. Date/Time Check Constraints
    ==============================

    CREATE TABLE events (
        id SERIAL PRIMARY KEY,
        name VARCHAR(200),
        start_time TIMESTAMP NOT NULL,
        end_time TIMESTAMP NOT NULL,

        CONSTRAINT valid_times CHECK (end_time > start_time)
    );

    5. Pattern Matching Check
    =========================

    CREATE TABLE users (
        id SERIAL PRIMARY KEY,
        username VARCHAR(50),
        phone VARCHAR(20),

        -- Username: alphanumeric and underscore only
        CONSTRAINT valid_username
        CHECK (username ~ '^[a-zA-Z][a-zA-Z0-9_]{2,49}$'),

        -- Phone: basic format validation
        CONSTRAINT valid_phone
        CHECK (phone ~ '^\+?[0-9]{10,15}$')
    );

    6. Conditional Check
    ==================

    CREATE TABLE subscriptions (
        id SERIAL PRIMARY KEY,
        plan VARCHAR(20) NOT NULL,
        monthly_price DECIMAL(10,2),
        annual_price DECIMAL(10,2),

        CONSTRAINT valid_prices
        CHECK (
            (plan = 'free' AND monthly_price IS NULL AND annual_price IS NULL) OR
            (plan != 'free' AND monthly_price > 0)
        )
    );

    Adding Check Constraints to Existing Tables:
    ===========================================

    -- Add constraint
    ALTER TABLE products
    ADD CONSTRAINT valid_price CHECK (price > 0);

    -- Add constraint with NO INHERIT (for partitioned tables)
    ALTER TABLE orders
    ADD CONSTRAINT valid_status CHECK (status IN ('pending', 'completed'));

    -- Remove constraint
    ALTER TABLE products DROP CONSTRAINT valid_price;

    -- Validate existing data (PostgreSQL 12+)
    ALTER TABLE products VALIDATE CONSTRAINT valid_price;

---

6.7 Exclusion Constraints

Exclusion constraints prevent overlapping values in specified columns or expressions.

                    Exclusion Constraints
    ========================================================================

    1. Basic Exclusion Constraint
    =============================

    CREATE TABLE reservations (
        id SERIAL PRIMARY KEY,
        room_number INTEGER NOT NULL,
        check_in DATE NOT NULL,
        check_out DATE NOT NULL,

        EXCLUDE USING gist (
            room_number WITH =,
            check_in WITH &&
        )
    );

    -- Prevents double-booking the same room for overlapping dates

    2. Time-Based Exclusion (Prevent Same Employee Double-Booked)
    ===============================================================

    CREATE TABLE shifts (
        id SERIAL PRIMARY KEY,
        employee_id INTEGER NOT NULL,
        shift_date DATE NOT NULL,
        start_time TIME NOT NULL,
        end_time TIME NOT NULL,

        EXCLUDE USING btree (
            employee_id WITH =,
            tsrange(start_time, end_time) WITH &&
        )
    );

    3. Geometric Exclusion (Prevent Overlapping Areas)
    ====================================================

    CREATE TABLE delivery_zones (
        id SERIAL PRIMARY KEY,
        zone_name VARCHAR(100),
        area POLYGON NOT NULL,

        EXCLUDE USING gist (
            area WITH &&
        )
    );

    4. Range Type Exclusion
    =======================

    CREATE TABLE bookings (
        id SERIAL PRIMARY KEY,
        resource_id INTEGER NOT NULL,
        time_slot TSRANGE NOT NULL,

        EXCLUDE USING gist (
            resource_id WITH =,
            time_slot WITH &&
        )
    );

    5. Conditional Exclusion
    =========================

    CREATE TABLE schedule (
        id SERIAL PRIMARY KEY,
        employee_id INTEGER NOT NULL,
        day VARCHAR(10) NOT NULL,
        time_range TZRANGE NOT NULL,

        EXCLUDE USING btree (
            employee_id WITH =,
            day WITH =,
            time_range WITH &&
        )
    ) WHERE day != 'Sunday';  -- Only enforce for weekdays

    Exclusion Operators:
    =====================

    ┌─────────────────────────────────────────────────────────────────────┐
    │  Operator │  Meaning              │  Use Case                      │
    ├───────────┼───────────────────────┼────────────────────────────────┤
    │  =        │  Equals               │  Same value                    │
    ├───────────┼───────────────────────┼────────────────────────────────┤
    │  &&       │  Overlaps             │  Any overlap                  │
    ├───────────┼───────────────────────┼────────────────────────────────┤
    │  <        │  Strictly less than  │  Before                        │
    ├───────────┼───────────────────────┼────────────────────────────────┤
    │  >        │  Strictly greater    │  After                         │
    ├───────────┼───────────────────────┼────────────────────────────────┤
    │  <<       │  Is left of          │  Entirely before              │
    ├───────────┼───────────────────────┼────────────────────────────────┤
    │  >>       │  Is right of         │  Entirely after               │
    ├───────────┼───────────────────────┼────────────────────────────────┤
    │  &<       │  Overlaps or left of │  Starts before, overlaps      │
    ├───────────┼───────────────────────┼────────────────────────────────┤
    │  &>       │  Overlaps or right   │  Starts after, overlaps       │
    ├───────────┼───────────────────────┼────────────────────────────────┤
    │  -|-      │  Is adjacent to      │  Directly next to             │
    └───────────┴───────────────────────┴────────────────────────────────┘

    Important Note:
    ===============

    Exclusion constraints require the btree-gist or btree-gin extension:

    CREATE EXTENSION IF NOT EXISTS btree_gist;

---

6.8 NOT NULL Constraints

NOT NULL ensures columns always contain a value.

                    NOT NULL Constraints
    ========================================================================

    1. Basic NOT NULL
    ================

    CREATE TABLE users (
        id SERIAL PRIMARY KEY,
        username VARCHAR(50) NOT NULL,    -- Cannot be NULL
        email VARCHAR(255) NOT NULL,     -- Must have value
        password VARCHAR(255) NOT NULL
    );

    2. NOT NULL with Default
    =========================

    CREATE TABLE posts (
        id SERIAL PRIMARY KEY,
        title VARCHAR(200) NOT NULL,
        content TEXT,
        status VARCHAR(20) NOT NULL DEFAULT 'draft',
        views INTEGER NOT NULL DEFAULT 0
    );

    3. Adding NOT NULL to Existing Table
    ====================================

    -- Add column with NOT NULL (requires default or existing data)
    ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL DEFAULT '';

    -- Or add NOT NULL after ensuring no NULLs exist
    UPDATE users SET phone = '' WHERE phone IS NULL;
    ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

    4. Removing NOT NULL
    ===================

    ALTER TABLE users ALTER COLUMN phone DROP NOT NULL;

    NOT NULL vs Empty String:
    =========================

    ┌─────────────────────────────────────────────────────────────────────┐
    │  '' (empty string)     │  NULL                                     │
    ├────────────────────────┼───────────────────────────────────────────┤
    │  Is a value            │  Represents unknown/missing              │
    ├────────────────────────┼───────────────────────────────────────────┤
    │  Has length = 0        │  Has no length                           │
    ├────────────────────────┼───────────────────────────────────────────┤
    │  '' IS NULL = FALSE   │  NULL IS NULL = TRUE (special)          │
    ├────────────────────────┼───────────────────────────────────────────┤
    │  COUNT('') = counted   │  COUNT(col) = excludes NULL             │
    ├────────────────────────┼───────────────────────────────────────────┤
    │  '' = '' = TRUE        │  NULL = NULL = NULL (not true!)         │
    └────────────────────────┴───────────────────────────────────────────┘

    Best Practices:
    ==============

    ┌─────────────────────────────────────────────────────────────────────┐
    │  • Use NOT NULL for required fields                               │
    │  • Use default values instead of NULL when appropriate            │
    │  • Avoid NOT NULL on VARCHAR unless you have a valid empty value│
    │  • Consider if NULL has business meaning before using NOT NULL  │
    └─────────────────────────────────────────────────────────────────────┘

---

6.9 Default Values

Default values provide fallback data when no explicit value is supplied.

                    Default Value Examples
    ========================================================================

    1. Literal Default Values
    =========================

    CREATE TABLE products (
        id SERIAL PRIMARY KEY,
        name VARCHAR(200) NOT NULL,
        status VARCHAR(20) DEFAULT 'active',
        is_featured BOOLEAN DEFAULT FALSE,
        priority INTEGER DEFAULT 0,
        views INTEGER DEFAULT 0
    );

    2. Expression Default Values
    =============================

    CREATE TABLE orders (
        id SERIAL PRIMARY KEY,
        order_number VARCHAR(20) DEFAULT 'ORD-' || to_char(NOW(), 'YYYYMMDD') || '-' || LPAD(nextval('order_seq')::TEXT, 6, '0'),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        status VARCHAR(20) DEFAULT 'pending'
    );

    3. Function Default Values
    ==========================

    CREATE TABLE users (
        id SERIAL PRIMARY KEY,
        username VARCHAR(50) NOT NULL,
        invite_code VARCHAR(20) DEFAULT gen_random_uuid()::TEXT,
        api_key VARCHAR(64) DEFAULT encode(gen_random_bytes(32), 'hex'),
        created_at TIMESTAMPTZ DEFAULT now()
    );

    4. Sequence Default Values
    ==========================

    CREATE TABLE invoices (
        id BIGSERIAL PRIMARY KEY,  -- Auto-increment
        invoice_number VARCHAR(20) DEFAULT 'INV-' || TO_CHAR(NOW(), 'YYYY') || '-' || LPAD(nextval('invoice_seq')::TEXT, 5, '0')
    );

    5. Complex Default Values
    =========================

    CREATE TABLE events (
        id SERIAL PRIMARY KEY,
        name VARCHAR(200),
        start_date DATE,
        end_date DATE,
        duration_days INTEGER GENERATED ALWAYS AS (end_date - start_date) STORED,
        status VARCHAR(20) DEFAULT CASE
            WHEN start_date > CURRENT_DATE THEN 'upcoming'
            WHEN end_date < CURRENT_DATE THEN 'completed'
            ELSE 'ongoing'
        END
    );

    6. Changing Default Values
    ==========================

    -- Set default for new inserts
    ALTER TABLE products ALTER COLUMN status SET DEFAULT 'draft';

    -- Remove default
    ALTER TABLE products ALTER COLUMN status DROP DEFAULT;

    DEFAULT vs NOT NULL:
    ===================

    ┌─────────────────────────────────────────────────────────────────────┐
    │  Column Definition         │  Behavior                            │
    ├──────────────────────────────┼─────────────────────────────────────┤
    │  col VARCHAR(50)             │  Allows NULL, no default            │
    ├──────────────────────────────┼─────────────────────────────────────┤
    │  col VARCHAR(50) NOT NULL   │  Must have value (error if omitted)│
    ├──────────────────────────────┼─────────────────────────────────────┤
    │  col VARCHAR(50) DEFAULT ''  │  Gets '' if no value provided       │
    ├──────────────────────────────┼─────────────────────────────────────┤
    │  col VARCHAR(50) NOT NULL DEFAULT ''  │  '' if omitted, can't be NULL│
    └──────────────────────────────┴─────────────────────────────────────┘

---

6.10 Altering Tables

PostgreSQL provides extensive ALTER TABLE options for modifying existing tables.

                    ALTER TABLE Operations
    ========================================================================

    Adding Columns:
    ==============

    ALTER TABLE users ADD COLUMN phone VARCHAR(20);
    ALTER TABLE users ADD COLUMN bio TEXT;
    ALTER TABLE users ADD COLUMN last_login TIMESTAMPTZ;

    Removing Columns:
    =================

    ALTER TABLE users DROP COLUMN phone;
    ALTER TABLE users DROP COLUMN IF EXISTS old_column;

    -- CASCADE to drop dependent objects
    ALTER TABLE users DROP COLUMN some_data CASCADE;

    Modifying Columns:
    =================

    -- Change data type
    ALTER TABLE users ALTER COLUMN phone TYPE VARCHAR(30);

    -- Change with conversion
    ALTER TABLE orders ALTER COLUMN total TYPE DECIMAL(15,2) USING total::numeric;

    -- Set default
    ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';

    -- Remove default
    ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

    -- Set NOT NULL
    ALTER TABLE users ALTER COLUMN email SET NOT NULL;

    -- Remove NOT NULL
    ALTER TABLE users ALTER COLUMN email DROP NOT NULL;

    Renaming:
    =========

    -- Rename table
    ALTER TABLE users RENAME TO app_users;

    -- Rename column
    ALTER TABLE users RENAME COLUMN email TO user_email;

    -- Rename constraint
    ALTER TABLE orders RENAME CONSTRAINT fk_user TO fk_orders_user;

    Adding Constraints:
    ==================

    -- Add primary key
    ALTER TABLE order_items ADD PRIMARY KEY (order_id, product_id);

    -- Add foreign key
    ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);

    -- Add unique constraint
    ALTER TABLE users ADD CONSTRAINT uk_username UNIQUE (username);

    -- Add check constraint
    ALTER TABLE products ADD CONSTRAINT positive_price CHECK (price > 0);

    Removing Constraints:
    ====================

    ALTER TABLE orders DROP CONSTRAINT fk_user;
    ALTER TABLE users DROP CONSTRAINT uk_username;
    ALTER TABLE products DROP CONSTRAINT positive_price;

    -- If you don't know the constraint name:
    SELECT conname FROM pg_constraint
    WHERE conrelid = 'users'::regclass AND contype = 'u';

    Other Operations:
    =================

    -- Change owner
    ALTER TABLE users OWNER TO new_owner;

    -- Change tablespace
    ALTER TABLE users SET TABLESPACE fast_storage;

    -- Enable row security
    ALTER TABLE users ENABLE ROW LEVEL SECURITY;

    -- Set fill factor
    ALTER TABLE users SET (fillfactor = 80);

    -- Add comment
    COMMENT ON TABLE users IS 'Application user accounts';

---

6.11 Temporary Tables

Temporary tables exist only for the duration of a session or transaction.

                    Temporary Tables
    ========================================================================

    1. Session-Level Temporary Table
    =================================

    CREATE TEMP TABLE temp_results (
        id INTEGER,
        data TEXT
    );

    -- Persists for entire session
    -- Only visible to the current session

    2. Transaction-Level Temporary Table
    ====================================

    CREATE TEMP TABLE temp_results (...) ON COMMIT DELETE ROWS;

    -- Data is deleted after transaction completes
    -- Other options: PRESERVE ROWS (default), DROP (drop table)

    3. Temporary Table Options
    =========================

    -- With data
    CREATE TEMP TABLE temp_calc AS
    SELECT * FROM large_table WHERE created_at > '2024-01-01';

    -- With schema
    CREATE TEMP TABLE temp_data (LIKE source_table INCLUDING ALL);

    -- Unlogged temporary (faster, no WAL)
    CREATE UNLOGGED TEMP TABLE temp_cache (...);

    Temporary Table Behavior:
    =========================

    ┌─────────────────────────────────────────────────────────────────────┐
    │  Option           │  Behavior                                      │
    ├───────────────────┼────────────────────────────────────────────────┤
    │  ON COMMIT DELETE │  Rows deleted after each COMMIT              │
    ├───────────────────┼────────────────────────────────────────────────┤
    │  ON COMMIT PRESERVE│  Rows preserved after COMMIT (default)       │
    ├───────────────────┼────────────────────────────────────────────────┤
    │  ON COMMIT DROP   │  Table dropped at end of transaction         │
    └───────────────────┴────────────────────────────────────────────────┘

    Using Temporary Tables:
    ======================

    -- Session 1
    CREATE TEMP TABLE user_stats AS
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    GROUP BY user_id;

    -- Works with application data for processing
    INSERT INTO temp_results VALUES (1, 'test');
    SELECT * FROM temp_results;

    -- Session 2 (separate connection) - won't see Session 1's temp table

    Best Practices:
    ==============

    ┌─────────────────────────────────────────────────────────────────────┐
    │  • Use temporary tables for intermediate processing               │
    │  • Consider UNLOGGED for performance on non-critical data         │
    │  • Drop temp tables when done to free resources                  │
    │  • Use transaction-scoped temp tables for short operations       │
    └─────────────────────────────────────────────────────────────────────┘

---

6.12 Table Inheritance

PostgreSQL supports table inheritance, allowing a child table to inherit columns from a parent.

                    Table Inheritance
    ========================================================================

    1. Basic Inheritance
    ====================

    CREATE TABLE users (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(255),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

    CREATE TABLE admin_users (
        permissions TEXT[]
    ) INHERITS (users);

    -- Insert into parent
    INSERT INTO users (name, email) VALUES ('John', 'john@example.com');

    -- Insert into child
    INSERT INTO admin_users (name, email, permissions)
    VALUES ('Admin', 'admin@example.com', ARRAY['all']);

    -- Query parent (includes children by default!)
    SELECT * FROM users;
    -- Returns: John + Admin

    -- Query only parent
    SELECT * FROM ONLY users;
    -- Returns: John only

    2. Partitioned Tables (Recommended Alternative)
    ===============================================

    CREATE TABLE orders (
        id SERIAL,
        user_id INTEGER NOT NULL,
        created_at DATE NOT NULL,
        total DECIMAL(10,2)
    ) PARTITION BY RANGE (created_at);

    -- Create partitions
    CREATE TABLE orders_2024_q1 PARTITION OF orders
        FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

    CREATE TABLE orders_2024_q2 PARTITION OF orders
        FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

    -- Default partition for anything else
    CREATE TABLE orders_default PARTITION OF orders DEFAULT;

    When to Use Inheritance:
    =======================

    ┌─────────────────────────────────────────────────────────────────────┐
    │  Use Cases:                                                       │
    │  • Audit logging (archive tables)                                │
    │  • Data partitioning (for older PG versions)                    │
    │  • Table specialization with additional columns                  │
    └─────────────────────────────────────────────────────────────────────┘

    ┌─────────────────────────────────────────────────────────────────────┐
    │  Considerations:                                                  │
    │  • Queries scan all partitions by default                        │
    │  • Indexes not automatically inherited                           │
    │  • Consider native partitioning (PostgreSQL 10+) for new apps   │
    └─────────────────────────────────────────────────────────────────────┘

---

6.13 Best Practices Summary

                    Table Design Best Practices
    ========================================================================

    1. Primary Keys
       ┌──────────────────────────────────────────────────────────────┐
       │  • Always use primary keys                                   │
       │  • Prefer surrogate keys (SERIAL) for most cases           │
       │  • Use UUID for distributed systems                         │
       │  • Avoid composite primary keys unless necessary           │
       └──────────────────────────────────────────────────────────────┘

    2. Foreign Keys
       ┌──────────────────────────────────────────────────────────────┐
       │  • Always use foreign keys for data integrity               │
       │  • Choose appropriate ON DELETE action                      │
       │  • Consider indexes on foreign key columns                  │
       └──────────────────────────────────────────────────────────────┘

    3. Constraints
       ┌──────────────────────────────────────────────────────────────┐
       │  • Use NOT NULL for required fields                         │
       │  • Use CHECK for data validation                           │
       │  • Use UNIQUE for business rules                           │
       │  • Use EXCLUDE for complex constraints                     │
       └──────────────────────────────────────────────────────────────┘

    4. Naming Conventions
       ┌──────────────────────────────────────────────────────────────┐
       │  • Table: plural (users, orders, products)                  │
       │  • Primary key: id or table_name_id                         │
       │  • Foreign key: referenced_table_id                         │
       │  • Constraints: pk/fk/uk/chk_tablename_column              │
       └──────────────────────────────────────────────────────────────┘

    5. Timestamps
       ┌──────────────────────────────────────────────────────────────┐
       │  • Always include created_at                                 │
       │  • Consider updated_at for modification tracking           │
       │  • Use TIMESTAMPTZ for timezone-aware timestamps          │
       └──────────────────────────────────────────────────────────────┘
    ========================================================================

---

Next Chapter

Chapter 7: Schema Design Best Practices

---

Last Updated: February 2026