Table Constraints and Validation

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    hire_date DATE NOT NULL DEFAULT CURRENT_DATE,
    salary NUMERIC(10, 2) NOT NULL,
    department_id INTEGER NOT NULL,
    manager_id INTEGER,
    employment_status VARCHAR(20) NOT NULL DEFAULT 'active',

    -- Unique constraint
    CONSTRAINT uk_employees_email UNIQUE (email),

    -- Check constraints for data validation
    CONSTRAINT chk_salary_positive CHECK (salary > 0),
    CONSTRAINT chk_salary_range CHECK (salary <= 1000000),
    CONSTRAINT chk_hire_date CHECK (hire_date <= CURRENT_DATE),
    CONSTRAINT chk_status_valid CHECK (
        employment_status IN ('active', 'on_leave', 'terminated', 'suspended')
    ),
    CONSTRAINT chk_phone_format CHECK (
        phone IS NULL OR phone ~ '^\+?[0-9]{10,15}$'
    ),

    -- Foreign key constraints
    CONSTRAINT fk_employee_department
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,

    CONSTRAINT fk_employee_manager
        FOREIGN KEY (manager_id)
        REFERENCES employees(employee_id)
        ON DELETE SET NULL
) TABLESPACE pg_default;

-- Add a deferred constraint
ALTER TABLE employees
    ADD CONSTRAINT chk_no_self_reference
    CHECK (manager_id IS NULL OR manager_id != employee_id);