Lesson 01

Schema keys and constraints

Model table ownership with primary keys, foreign keys, uniqueness, and check constraints.

schema
constraints
keys

Good Code

schema.sql

CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE code_reviews (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id BIGINT NOT NULL REFERENCES users(id),
  title TEXT NOT NULL,
  status TEXT NOT NULL CHECK (
    status IN ('draft', 'submitted', 'approved', 'rejected')
  ),
  submitted_at TIMESTAMPTZ
);

Bad Code

schema.sql

CREATE TABLE users (
  id BIGINT,
  email TEXT,
  name TEXT,
  created_at TEXT
);

CREATE TABLE code_reviews (
  id BIGINT,
  author_id BIGINT,
  title TEXT,
  status TEXT,
  submitted_at TEXT
);

Review Notes

What to review

Good Code

The good version makes identity, uniqueness, relationships, required fields, and allowed review states explicit in the database.

Bad Code

The bad version stores important rules only as hope. Duplicate users, orphaned reviews, invalid statuses, and inconsistent dates can all be inserted.

Takeaways

A schema should enforce the data rules the application depends on.