Protect your data with rules the database enforces — PRIMARY KEY, FOREIGN KEY with ON DELETE actions, UNIQUE, NOT NULL, DEFAULT, CHECK, and generated columns.
Why: a PRIMARY KEY is the column that uniquely identifies each row. PostgreSQL enforces that it is never NULL and never repeated, and automatically builds an index on it so lookups by id are fast. Note: we build small throwaway tables in this lesson so they do not clash with the schema you already created.
CREATE TABLE tags (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);Why: a FOREIGN KEY says "this column must point at a real row in another table", so you can never have a line item for an order that does not exist. Note: ON DELETE CASCADE means deleting an order automatically deletes its line items; ON DELETE RESTRICT blocks deleting a product while a line item still references it.
CREATE TABLE cart_items (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id bigint NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
product_id bigint NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
quantity integer NOT NULL
);Why: UNIQUE forbids duplicate values (e.g. two users with the same email), NOT NULL forbids missing values, and DEFAULT supplies a value when an INSERT leaves the column out. These three carry most of the weight in keeping data clean.
CREATE TABLE members (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE,
status text NOT NULL DEFAULT 'active',
created_at timestamptz NOT NULL DEFAULT now()
);Why: a CHECK constraint enforces any rule you can write as a condition — a price that cannot be negative, a quantity of at least one. The database rejects any INSERT or UPDATE that breaks the rule.
CREATE TABLE line_items (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
price numeric(10, 2) NOT NULL CHECK (price >= 0),
quantity integer NOT NULL CHECK (quantity > 0)
);Why: a generated column is computed automatically from other columns and stored for you, so a derived value (like a line total) can never drift out of sync with the values it depends on.
CREATE TABLE invoices (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
price numeric(10, 2) NOT NULL,
quantity integer NOT NULL,
total numeric(10, 2) GENERATED ALWAYS AS (price * quantity) STORED
);