-- Check constraint: salary must be positive
ALTER TABLE employees ADD CONSTRAINT chk_salary_positive
  CHECK (salary > 0);

-- Check constraint: end date after start date
ALTER TABLE projects ADD CONSTRAINT chk_dates
  CHECK (end_date IS NULL OR end_date > start_date);

-- Exclusion constraint: no overlapping project for same employee
-- Requires btree_gist extension
CREATE EXTENSION btree_gist;

ALTER TABLE project_assignments ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (
    emp_id WITH =,  -- same employee
    daterange(assigned_on, assigned_on + INTERVAL '90 days') WITH &&  -- overlapping period
  );