Module 7: Creating tables

Design tables that protect their own data: types, primary and foreign keys, UNIQUE, CHECK and ALTER TABLE.

1. CREATE TABLE and data types: Pick types on purpose: INT/BIGINT for ids, NUMERIC(p,s) for money (never FLOAT), VARCHAR/TEXT for text and phone numbers, DATE/TIMESTAMP, BOOLEAN.
2. Constraints: PRIMARY KEY, NOT NULL, UNIQUE, CHECK, DEFAULT: Constraints are rules Postgres enforces on every write: PRIMARY KEY, NOT NULL, UNIQUE (allows NULLs), CHECK (NULL passes), DEFAULT.
3. Foreign keys and ON DELETE: REFERENCES parent(col) keeps children pointing at real parents; ON DELETE decides: refuse (default/RESTRICT), CASCADE, or SET NULL.
4. ALTER, DROP, TRUNCATE, identity columns and sequences: ALTER TABLE changes a live table; TRUNCATE empties it, DROP removes it; identity columns number rows for you, with gaps.

Ends with a 12-question checkpoint (80% passes) and a project: The canteen pre-order database.