160: SQL - Tables, Keys, and Constraints

Learning outcomes

• model entities with tables and keys;
• use constraints as durable invariants;
• compare relational and document modeling.

Study and practice

A relational database stores rows in tables and uses constraints to reject invalid states. Design users, tasks, and sessions for the task manager. Add primary keys, ownership foreign keys, timestamps, status checks, and uniqueness constraints. Insert valid and invalid rows and record each rejection.

Worked schema

sqlCopy
CREATE TABLE tasks (
  id uuid PRIMARY KEY,
  owner_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title text NOT NULL CHECK (length(trim(title)) BETWEEN 1 AND 200),
  status text NOT NULL CHECK (status IN ('open', 'done')),
  created_at timestamptz NOT NULL DEFAULT now()
);

ON DELETE CASCADE is a product decision, not a syntax detail. It may be correct for private tasks but dangerous for audit records. Record the chosen behavior and test deletion of an owner.

Edge cases

Test duplicate IDs, missing owners, blank titles, invalid status values, and concurrent attempts to create the same unique email. The database must remain correct when two application processes race.

Checkpoint

Explain primary versus foreign keys, normalization versus duplication, and why an application-only uniqueness check races.

References

• PostgreSQL: CREATE TABLE
• PostgreSQL: Constraints