A2 Work Session

Goals

• Make significant progress on the Assignment 2 SQL script
• Write all CREATE TABLE IF NOT EXISTS statements based on your Assignment 1 model
• Add the 5 required CHECK constraints
• Begin writing the ALTER TABLE operations with explanatory comments

Session Structure

Working Checklist

Complete the following in order during this session:

Step 1 — Create your database and schema

CREATE DATABASE IF NOT EXISTS your_domain_db;
-- Then connect to it:
\c your_domain_db

CREATE SCHEMA IF NOT EXISTS domain_schema;
SET search_path TO domain_schema;

Step 2 — Write all CREATE TABLE IF NOT EXISTS statements

• Start with tables that have no foreign keys (reference/lookup tables)
• Work outward to child tables
• Add PK, FK, NOT NULL, UNIQUE, CHECK constraints inline

Step 3 — Add 5 CHECK constraints

Required types:

• Date > '2026-01-01'
• Numeric value >= 0
• Value IN a set (e.g., status or gender)
• UNIQUE (can be inline or as a named constraint)
• NOT NULL (inline on a column)

Step 4 — Write at least 5 ALTER TABLE statements

Each ALTER must have a comment above it explaining why:

-- email was not marked unique during initial creation
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

Step 5 — Add TRUNCATE before INSERTs (when you start inserting)

Common Issues

• FK violation — if a child table references a parent that does not exist yet, reorder your CREATE TABLE statements
• GENERATED column syntax — use GENERATED ALWAYS AS (expr) STORED, not GENERATED ALWAYS AS (expr)
• CHECK constraint with IN — use: CHECK (status IN ('active', 'inactive', 'suspended'))

Bonus (for fast finishers)

Start writing the INSERT statements:

• Write TRUNCATE statements first, in reverse FK order (children first, or use CASCADE)
• Insert at least 3 rows into your first two tables using realistic data
• Use a subquery to resolve at least one FK value instead of hardcoding