schema.sql

-- =============================================================
-- Section 05 — Constraints
-- =============================================================
-- Reference version of the tables you'll build in the exercises,
-- with every constraint in place.
-- =============================================================

DROP SCHEMA IF EXISTS adv_constraints CASCADE;
CREATE SCHEMA adv_constraints;
SET search_path TO adv_constraints;

CREATE TABLE employee (
    id         SERIAL,
    email      VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name  VARCHAR(100) NOT NULL,
    salary     NUMERIC(10, 2) NOT NULL DEFAULT 0,
    country    CHAR(2)        NOT NULL DEFAULT 'GB',
    joined_on  DATE           NOT NULL DEFAULT CURRENT_DATE,

    CONSTRAINT employee_pk            PRIMARY KEY (id),
    CONSTRAINT employee_email_unique  UNIQUE (email),
    CONSTRAINT employee_salary_non_negative CHECK (salary >= 0),
    CONSTRAINT employee_country_iso2        CHECK (country ~ '^[A-Z]{2}$')
);