constraints.sql

-- 1. Add a named primary key constraint
ALTER TABLE public.users
  ADD CONSTRAINT users_pkey PRIMARY KEY (id);

-- 2. Add a quoted unique constraint on a mixed-case column
ALTER TABLE "App"."User Data"
  ADD CONSTRAINT "Unique_Full Name" UNIQUE ("Full Name");

-- 3. Add a composite unique constraint with custom name
ALTER TABLE school.attendance
  ADD CONSTRAINT attendance_unique UNIQUE ("Student ID", "Class ID");

-- 4. Add a foreign key with quoted constraint and schema-qualified reference
ALTER TABLE "Orders"."OrderLines"
  ADD CONSTRAINT "FK_Order_Ref" FOREIGN KEY (order_id)
  REFERENCES "Orders"."Order"("OrderID");

-- 5. Add a check constraint with a regex pattern
ALTER TABLE "x-Schema"."z-Table"
  ADD CONSTRAINT "zNameFormatCheck" CHECK ("Z-Name" ~ '^[A-Z]');

-- 6. Add a check constraint on JSON key existence
ALTER TABLE data.snapshots
  ADD CONSTRAINT metadata_has_key CHECK (metadata ? 'type');

-- 7. Add a foreign key referencing quoted schema.table.column
ALTER TABLE "Billing"."Invoices"
  ADD CONSTRAINT "FK_Client_ID"
  FOREIGN KEY ("Client ID") REFERENCES "Clients"."ClientBase"("Client ID");

-- 8. Add a primary key on a quoted identifier
ALTER TABLE "API Keys"
  ADD CONSTRAINT "PK_KeyID" PRIMARY KEY ("KeyID");

-- 9. Add a check on numeric range
ALTER TABLE finance.transactions
  ADD CONSTRAINT tax_rate_range CHECK (tax_rate >= 0 AND tax_rate <= 1);

-- 10. Add a multi-column foreign key with custom name
ALTER TABLE school.enrollments
  ADD CONSTRAINT fk_student_course FOREIGN KEY (student_id, course_id)
  REFERENCES school.courses_students(student_id, course_id);

-- 11. 
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,
  total DECIMAL(10,2) CHECK (total > 0),
  status VARCHAR(20) DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT now(),
  CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT unique_user_date UNIQUE (user_id, created_at),
  CONSTRAINT check_status CHECK (status IN ('pending', 'completed', 'cancelled'))
);

-- 12.

ALTER TABLE products ADD CONSTRAINT fk_category 
  FOREIGN KEY (category_id) 
  REFERENCES categories(id) 
  ON UPDATE CASCADE 
  ON DELETE SET NULL 
  DEFERRABLE INITIALLY DEFERRED;

-- 13

ALTER TABLE products ADD CONSTRAINT check_price CHECK (price > 0);

-- 14

ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);


-- 15 

ALTER TABLE school.enrollments
  ADD CONSTRAINT fk_student_course
  FOREIGN KEY (student_id, course_id)
  REFERENCES school.courses_students (student_id, course_id);

-- 16 

ALTER TABLE school.enrollments
  ADD CONSTRAINT chk_enrollment_date
  CHECK (
    enrollment_date <= CURRENT_DATE
    AND status IN ('active', 'completed', 'withdrawn')
  );

-- 17

CREATE TABLE school.enrollments (
  student_id INT NOT NULL,
  course_id INT NOT NULL,
  enrollment_date DATE NOT NULL,
  status TEXT CHECK (
    status IN ('active', 'completed', 'withdrawn')
  ),
  CHECK (
    enrollment_date <= CURRENT_DATE
  )
);