Create Databases & Tables — DDL and Constraints

Prerequisite: a running PostgreSQL and psql. If you don't have those yet, do Install PostgreSQL first — it takes ten minutes. Everything below is copy-paste runnable in psql, and I'll show you the exact output (including the errors — especially the errors) you should see.

1Schema Design in 2 Minutes: Tables, Columns, Rows

Before typing any SQL, get the model straight. A relational database stores data in tables. Each table models one kind of thing. Each column is one attribute of that thing, with a fixed type. Each row is one actual instance.

• Table = the noun. customers models people who buy from us.
• Column = the attribute. email is TEXT, created_at is a timestamp.
• Row = the instance. Maria Santos from Manila is one row in customers.

The web shop we're building needs four tables. Here's the whole design on a napkin — 1 and * mark the "one" and "many" sides of each relationship:

┌───────────┐ 1     * ┌────────┐ 1     * ┌─────────────┐ *     1 ┌──────────┐
│ customers │────────<│ orders │────────<│ order_items │>────────│ products │
└───────────┘         └────────┘         └─────────────┘         └──────────┘

customers    : customer_id, full_name, email, city, created_at
products     : product_id, name, category, price, stock
orders       : order_id, customer_id → customers, status, ordered_at
order_items  : order_item_id, order_id → orders, product_id → products,
               quantity, unit_price

Read it out loud: one customer places many orders; one order contains many line items; each line item points at exactly one product. Notice we never store the customer's name inside orders — we store customer_id and let the database connect them. That's the entire relational idea, and the rest of this post is just teaching PostgreSQL to enforce it.

2CREATE DATABASE and Connecting to It

Open psql (you'll land in the default postgres database) and create a dedicated database for the series:

CREATE DATABASE shop;

postgres=# CREATE DATABASE shop;
CREATE DATABASE

CREATE DATABASE is the confirmation tag psql prints — no news is good news in DDL land. Now connect to it with the \c meta-command (a psql feature, not SQL — note there's no semicolon):

\c shop

postgres=# \c shop
You are now connected to database "shop" as user "thirdy".
shop=#

The prompt changing from postgres=# to shop=# is your compass — it always tells you which database you're about to run SQL against. You can list all databases anytime with \l.

3Your First CREATE TABLE: customers

Here's table number one. Type it exactly — we'll dissect every line right after:

CREATE TABLE customers (
  customer_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name   TEXT NOT NULL,
  email       TEXT NOT NULL UNIQUE,
  city        TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

shop=# CREATE TABLE customers (
shop(#   customer_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
shop(#   full_name   TEXT NOT NULL,
shop(#   email       TEXT NOT NULL UNIQUE,
shop(#   city        TEXT,
shop(#   created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
shop(# );
CREATE TABLE

Line by line, this is what you just told PostgreSQL:

The \d table_name meta-command describes a table — you'll use it constantly:

\d customers

shop=# \d customers
                                     Table "public.customers"
   Column    |           Type           | Collation | Nullable |           Default
-------------+--------------------------+-----------+----------+------------------------------
 customer_id | integer                  |           | not null | generated always as identity
 full_name   | text                     |           | not null |
 email       | text                     |           | not null |
 city        | text                     |           |          |
 created_at  | timestamp with time zone |           | not null | now()
Indexes:
    "customers_pkey" PRIMARY KEY, btree (customer_id)
    "customers_email_key" UNIQUE CONSTRAINT, btree (email)

INSERT INTO customers (full_name, email, city)
VALUES ('Maria Santos', '[email protected]', 'Manila');

SELECT customer_id, full_name, email, city, created_at
FROM customers;

INSERT 0 1

 customer_id |  full_name   |          email           |  city  |          created_at
-------------+--------------+--------------------------+--------+-------------------------------
           1 | Maria Santos | [email protected] | Manila | 2026-07-18 10:02:41.921544+08
(1 row)

We chose TEXT instead of VARCHAR(50) and TIMESTAMPTZ instead of TIMESTAMP deliberately — the full reasoning has its own post (PostgreSQL Data Types), but the short version: in PostgreSQL TEXT costs nothing over VARCHAR, and timestamps without time zones cause 2 AM incident calls. Literally.

4GENERATED ALWAYS AS IDENTITY vs SERIAL

Every tutorial older than a few years — and most codebases you'll inherit — writes the primary key like this instead:

-- You WILL see this in old code. It works, but it's the legacy way.
CREATE TABLE customers_legacy (
  customer_id SERIAL PRIMARY KEY,
  full_name   TEXT NOT NULL
);

SERIAL is a shortcut that creates a sequence behind your back and wires it as the column default. It works, but it has quirks: the sequence is a separate loosely-attached object, permissions on it need separate handling, and — the sneaky one — nothing stops someone from inserting an explicit ID and putting the sequence out of sync. GENERATED ALWAYS AS IDENTITY is the SQL-standard replacement (PostgreSQL 10+) and it actively protects you. Watch:

INSERT INTO customers (customer_id, full_name, email)
VALUES (999, 'Hacker Hank', '[email protected]');

ERROR:  cannot insert into column "customer_id"
DETAIL:  Column "customer_id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

The database said no. With SERIAL (or GENERATED BY DEFAULT), that insert would have succeeded — and the next auto-generated ID would eventually collide with 999 and fail with a confusing duplicate-key error days later. ALWAYS makes ID management the database's job, full stop.

5Constraints Deep Tour: Break Them, Read the Error, Fix It

Constraints are promises the database enforces so your application doesn't have to remember. The fastest way to learn them is to violate each one on purpose and read the error like an engineer. Errors first, fixes second — let's go.

INSERT INTO customers (full_name, city)
VALUES ('Juan dela Cruz', 'Cebu');

ERROR:  null value in column "email" of relation "customers" violates not-null constraint
DETAIL:  Failing row contains (2, Juan dela Cruz, null, Cebu, 2026-07-18 10:15:22.482915+08).

Read the parts: which column (email), which table (customers), and DETAIL shows the exact row it refused — you can see the null sitting where the email should be. Also notice the identity had already assigned 2; that number is now burned (a gap, and that's fine). The fix is to supply the required value:

INSERT INTO customers (full_name, email, city)
VALUES ('Juan dela Cruz', '[email protected]', 'Cebu');

INSERT 0 1

INSERT INTO customers (full_name, email, city)
VALUES ('Maria S. Impostor', '[email protected]', 'Davao');

ERROR:  duplicate key value violates unique constraint "customers_email_key"
DETAIL:  Key (email)=([email protected]) already exists.

customers_email_key is the constraint name PostgreSQL auto-generated (table_column_key pattern — you saw it in the \d customers output). Your application should catch this specific violation and turn it into a friendly "email already registered" message. Fix: use an email that isn't taken.

INSERT INTO customers (full_name, email, city)
VALUES ('Ana Reyes', '[email protected]', 'Davao');

INSERT INTO customers (full_name, email, city)
VALUES ('Carlo Garcia', '[email protected]', 'Quezon City');

INSERT 0 1
INSERT 0 1

Time for the second table. products introduces CHECK, a constraint that evaluates any boolean expression on the row. A price below zero is nonsense, so we outlaw it at the schema level:

CREATE TABLE products (
  product_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       TEXT NOT NULL,
  category   TEXT NOT NULL,
  price      NUMERIC(10,2) NOT NULL CHECK (price >= 0),
  stock      INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE

Try to sneak in a negative price:

INSERT INTO products (name, category, price, stock)
VALUES ('Mechanical Keyboard', 'Peripherals', -100.00, 10);

ERROR:  new row for relation "products" violates check constraint "products_price_check"
DETAIL:  Failing row contains (1, Mechanical Keyboard, Peripherals, -100.00, 10).

Rejected. Now the legit catalog — five products for our shop:

INSERT INTO products (name, category, price, stock) VALUES
  ('Mechanical Keyboard', 'Peripherals', 3499.00, 25),
  ('USB-C Hub',           'Accessories', 1299.00, 40),
  ('27-inch Monitor',     'Displays',    9999.00, 12),
  ('HD Webcam',           'Peripherals', 1899.00, 30),
  ('Laptop Stand',        'Accessories',  899.00,  0);

INSERT 0 5

Notice stock INTEGER NOT NULL DEFAULT 0. Insert a product without mentioning stock and the default kicks in:

INSERT INTO products (name, category, price)
VALUES ('Webcam Tripod', 'Accessories', 499.00);

SELECT product_id, name, price, stock
FROM products
WHERE name = 'Webcam Tripod';

 product_id |     name      | price  | stock
------------+---------------+--------+-------
          6 | Webcam Tripod | 499.00 |     0
(1 row)

And note the money type: NUMERIC(10,2) — exact decimal, up to 10 digits, 2 after the point. Never FLOAT for money; we'll prove why in the common-mistakes section.

6Foreign Keys: orders REFERENCES customers

Table #3. An order must belong to a real customer — not customer #999 who doesn't exist. That's what REFERENCES does: it makes orders.customer_id a foreign key pointing at customers.customer_id, and PostgreSQL refuses any value that doesn't exist on the other side.

CREATE TABLE orders (
  order_id    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (customer_id),
  status      TEXT NOT NULL DEFAULT 'pending',
  ordered_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE

INSERT INTO orders (customer_id)
VALUES (999);

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(999) is not present in table "customers".

Perfect. No orphan orders, guaranteed — even if a buggy API endpoint or a manual psql session tries. Fix: reference a customer that exists (Maria is customer_id = 1):

INSERT INTO orders (customer_id) VALUES (1);           -- Maria, status defaults to 'pending'
INSERT INTO orders (customer_id, status) VALUES (2, 'paid');   -- Juan

SELECT order_id, customer_id, status, ordered_at
FROM orders;

INSERT 0 1
INSERT 0 1

 order_id | customer_id | status  |          ordered_at
----------+-------------+---------+-------------------------------
        1 |           1 | pending | 2026-07-18 10:31:07.114202+08
        2 |           2 | paid    | 2026-07-18 10:31:07.118539+08
(2 rows)

Foreign keys protect both directions. Maria has an order — try to delete her:

DELETE FROM customers WHERE customer_id = 1;

ERROR:  update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"
DETAIL:  Key (customer_id)=(1) is still referenced from table "orders".

That refusal is the default behavior, ON DELETE NO ACTION. You can choose a different policy per foreign key — this table is worth memorizing:

7The Junction Table: order_items

Last table, and the most interesting shape. One order contains many products; one product appears in many orders. That's a many-to-many relationship, and relational databases model it with a junction table (a.k.a. join table) that sits in the middle holding a foreign key to each side:

CREATE TABLE order_items (
  order_item_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id      INTEGER NOT NULL REFERENCES orders (order_id) ON DELETE CASCADE,
  product_id    INTEGER NOT NULL REFERENCES products (product_id),
  quantity      INTEGER NOT NULL CHECK (quantity > 0),
  unit_price    NUMERIC(10,2) NOT NULL
);

CREATE TABLE

Three deliberate decisions in there:

• ON DELETE CASCADE on order_id only. A line item is a fragment of its order — it has no meaning on its own. If order #1 is deleted, its line items should vanish. But product_id keeps the default: you can't delete a product that appears in any order.
• CHECK (quantity > 0). Zero or negative quantities are bugs; reject them at the door.
• unit_price is copied from the product at purchase time. Looks redundant — it isn't. When the catalog price changes next month, historical orders must still show what the customer actually paid. This is intentional, valuable duplication.

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
  (1, 1, 1, 3499.00),   -- 1x Mechanical Keyboard
  (1, 2, 2, 1299.00);   -- 2x USB-C Hub

SELECT order_item_id, order_id, product_id, quantity, unit_price
FROM order_items;

INSERT 0 2

 order_item_id | order_id | product_id | quantity | unit_price
---------------+----------+------------+----------+------------
             1 |        1 |          1 |        1 |    3499.00
             2 |        1 |          2 |        2 |    1299.00
(2 rows)

BEGIN;                                   -- open a transaction so we can undo this demo
DELETE FROM orders WHERE order_id = 1;
SELECT count(*) FROM order_items;        -- Maria's 2 line items went with it
ROLLBACK;                                -- put everything back
SELECT count(*) FROM order_items;

BEGIN
DELETE 1
 count
-------
     0
(1 row)

ROLLBACK
 count
-------
     2
(1 row)

8The Full Schema File: 01_create_tables.sql

You built the schema interactively — now capture it as a file, because real schemas live in version control, not in someone's memory of psql commands. This is the canonical file the whole series refers back to:

-- Web-shop schema for the PostgreSQL series (thirdygayares.com)
-- Drop in reverse dependency order so re-running the file always works.
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
  customer_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name   TEXT NOT NULL,
  email       TEXT NOT NULL UNIQUE,
  city        TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE products (
  product_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       TEXT NOT NULL,
  category   TEXT NOT NULL,
  price      NUMERIC(10,2) NOT NULL CHECK (price >= 0),
  stock      INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE orders (
  order_id    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (customer_id),
  status      TEXT NOT NULL DEFAULT 'pending',
  ordered_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  order_item_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id      INTEGER NOT NULL REFERENCES orders (order_id) ON DELETE CASCADE,
  product_id    INTEGER NOT NULL REFERENCES products (product_id),
  quantity      INTEGER NOT NULL CHECK (quantity > 0),
  unit_price    NUMERIC(10,2) NOT NULL
);

Two details worth noticing: tables are created parents-first (customers and products before anything that references them) and dropped children-first — you can't create a foreign key to a table that doesn't exist yet, and you can't (plainly) drop a table something still points at. Dependency order is the invisible grammar of DDL files.

Run the whole file from inside psql with \i, then list your tables with \dt:

\i 01_create_tables.sql
\dt

shop=# \i 01_create_tables.sql
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
shop=# \dt
            List of relations
 Schema |    Name     | Type  | Owner
--------+-------------+-------+--------
 public | customers   | table | thirdy
 public | order_items | table | thirdy
 public | orders      | table | thirdy
 public | products    | table | thirdy
(4 rows)

The seed data (customers from Manila to Davao, the full product catalog, sample orders) gets its own dedicated treatment in INSERT, UPDATE, DELETE — every later post in the series assumes this schema file plus that seed.

9ALTER TABLE: Evolving a Schema That Already Exists

Schemas are never finished. Marketing wants a phone number, finance wants a stock rule — and you can't drop and recreate a table full of production data. ALTER TABLE changes a live table in place. The four moves you'll use most:

ALTER TABLE customers ADD COLUMN phone TEXT;

\d customers

ALTER TABLE

                                     Table "public.customers"
   Column    |           Type           | Collation | Nullable |           Default
-------------+--------------------------+-----------+----------+------------------------------
 customer_id | integer                  |           | not null | generated always as identity
 full_name   | text                     |           | not null |
 email       | text                     |           | not null |
 city        | text                     |           |          |
 created_at  | timestamp with time zone |           | not null | now()
 phone       | text                     |           |          |
Indexes:
    "customers_pkey" PRIMARY KEY, btree (customer_id)
    "customers_email_key" UNIQUE CONSTRAINT, btree (email)

Existing rows get NULL in the new column. That's why a brand-new column is usually nullable first — you can't declare NOT NULL on day one unless you also provide a DEFAULT for the rows that already exist.

ALTER TABLE customers RENAME COLUMN phone TO phone_number;

ALTER TABLE

ALTER TABLE products ALTER COLUMN stock SET DEFAULT 10;

\d products

ALTER TABLE

                                  Table "public.products"
   Column   |     Type      | Collation | Nullable |           Default
------------+---------------+-----------+----------+------------------------------
 product_id | integer       |           | not null | generated always as identity
 name       | text          |           | not null |
 category   | text          |           | not null |
 price      | numeric(10,2) |           | not null |
 stock      | integer       |           | not null | 10
Indexes:
    "products_pkey" PRIMARY KEY, btree (product_id)
Check constraints:
    "products_price_check" CHECK (price >= 0::numeric)

Important nuance: changing a default only affects future inserts — existing rows are untouched. Let's put it back to the canonical 0 so our schema matches the series:

ALTER TABLE products ALTER COLUMN stock SET DEFAULT 0;

ALTER TABLE

Here's the one that bites people. Adding a constraint validates every existing row. Watch it fail first — suppose a bad row snuck in while there was no rule:

INSERT INTO products (name, category, price, stock)
VALUES ('Cursed Mouse Pad', 'Accessories', 199.00, -5);

ALTER TABLE products ADD CONSTRAINT products_stock_check CHECK (stock >= 0);

INSERT 0 1
ERROR:  check constraint "products_stock_check" of relation "products" is violated by some row

PostgreSQL refuses to install a promise the current data already breaks. Clean the data, then add the constraint:

UPDATE products SET stock = 0 WHERE stock < 0;

ALTER TABLE products ADD CONSTRAINT products_stock_check CHECK (stock >= 0);

\d products

UPDATE 1
ALTER TABLE

                                  Table "public.products"
   Column   |     Type      | Collation | Nullable |           Default
------------+---------------+-----------+----------+------------------------------
 product_id | integer       |           | not null | generated always as identity
 name       | text          |           | not null |
 category   | text          |           | not null |
 price      | numeric(10,2) |           | not null |
 stock      | integer       |           | not null | 0
Indexes:
    "products_pkey" PRIMARY KEY, btree (product_id)
Check constraints:
    "products_price_check" CHECK (price >= 0::numeric)
    "products_stock_check" CHECK (stock >= 0)

Finally, since phone_number was just a demo and isn't part of the canonical series schema, drop it to leave things clean:

ALTER TABLE customers DROP COLUMN phone_number;

ALTER TABLE

10Dropping Things Safely: DROP TABLE, IF EXISTS, CASCADE

DROP TABLE deletes the table, its data, its indexes, its constraints — permanently, with no confirmation prompt. So let's learn its guardrails. First, dependencies protect you. Try to drop customers while orders still points at it:

DROP TABLE customers;

ERROR:  cannot drop table customers because other objects depend on it
DETAIL:  constraint orders_customer_id_fkey on table orders depends on table customers
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Second guardrail: IF EXISTS turns "table not found" from an error into a notice, which is what makes re-runnable scripts like our 01_create_tables.sql possible:

DROP TABLE temp_scratch;             -- errors: no such table
DROP TABLE IF EXISTS temp_scratch;   -- shrugs politely

ERROR:  table "temp_scratch" does not exist
NOTICE:  table "temp_scratch" does not exist, skipping
DROP TABLE

And the biggest hammer of all — DROP DATABASE. Two things to know: you can't drop the database you're currently connected to, and there is no undo. Practice on a scratch database, never on shop:

CREATE DATABASE scratch;
\c scratch
DROP DATABASE scratch;    -- fails: we're inside it
\c shop
DROP DATABASE scratch;    -- works from outside

CREATE DATABASE
You are now connected to database "scratch" as user "thirdy".
ERROR:  cannot drop the currently open database
You are now connected to database "shop" as user "thirdy".
DROP DATABASE

11Naming Conventions and Schema Hygiene

Naming feels cosmetic until you're reading queries at 2 AM. Consistent names are the cheapest documentation you'll ever write. The rules this series uses (and that most PostgreSQL teams converge on):

On singular vs plural for table names: both camps exist, and consistency matters more than the choice. This series picks plural for two reasons. First, a table is a collection — customers holds many customers, and SELECT … FROM customers reads like English. Second, plural conveniently dodges reserved words: try creating a singular order table —

CREATE TABLE order (
  order_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

ERROR:  syntax error at or near "order"
LINE 1: CREATE TABLE order (
                     ^

ORDER is an SQL keyword (as in ORDER BY), so the parser chokes. user is another famous trap. Plural orders and a join table named order_items sidestep the whole category. When in doubt, check the PostgreSQL keyword list before naming a table something short and common.

And note the pattern in the junction table's name: order_items — the two tables it connects, in a sensible reading order. Boring names, zero surprises. That's schema hygiene.

12Common Mistakes (Learn Them Here, Not in Production)

Mistake #1: quoted "CamelCase" identifiers. Unquoted identifiers get folded to lowercase; quoting preserves case exactly — and once you quote at creation, you must quote forever, everywhere:

CREATE TABLE "Customers" ("FullName" TEXT);

SELECT fullname FROM Customers;   -- unquoted -> folded to lowercase "customers"

CREATE TABLE
ERROR:  relation "customers" does not exist
LINE 1: SELECT fullname FROM Customers;
                             ^

Mistake #2: VARCHAR(255) cargo cult. That 255 isn't engineering — it's folklore copied from decades-old MySQL habits. In PostgreSQL, TEXT and VARCHAR(n) have identical storage and performance; the only thing (n) adds is an error when someone's legitimate input is character 256.

Mistake #3: FLOAT for money. Binary floating point cannot represent most decimal fractions exactly. Don't take my word for it:

SELECT 0.1::float8 + 0.2::float8 AS float_math,
       0.1::numeric + 0.2::numeric AS numeric_math;

     float_math      | numeric_math
---------------------+--------------
 0.30000000000000004 |          0.3
(1 row)

Mistake #4: assuming foreign keys create indexes. PostgreSQL indexes primary keys and unique constraints automatically — you saw customers_pkey and customers_email_key appear in \d without asking. But foreign key columns like orders.customer_id get no index at all. With toy data you'll never notice; with a million orders, every "show this customer's orders" query and every customer delete does a full table scan.

13Best Practices Checklist + DDL Reference

Everything from this post on one screen — bookmark it:

14Practice Exercises + What's Next

Prove it to yourself: design a tiny blog platform schema from scratch — no peeking at the solution until each exercise runs. Do it in a fresh database so mistakes are free:

1. Create a database named blog and connect to it.
2. Create authors: identity PK author_id, required pen_name that no two authors can share, required email (also unique), and a joined_at timestamp that fills itself in.
3. Create posts: identity PK post_id, a required FK to authors that deletes an author's posts when the author is deleted, required title, required body, a status defaulting to 'draft', and a published_at that may be NULL (drafts aren't published yet).
4. Add a CHECK so title can't be the empty string. Then try to insert a post with title = '' and confirm you get a check-violation error.
5. Insert one author and one post, then delete the author — confirm the post disappears with them (that's your CASCADE working). Bonus: rehearse it inside BEGIN … ROLLBACK first.

One clean solution (yours may differ in details — what matters is that the constraints hold):

CREATE DATABASE blog;
-- then: \c blog

CREATE TABLE authors (
  author_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  pen_name  TEXT NOT NULL UNIQUE,
  email     TEXT NOT NULL UNIQUE,
  joined_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE posts (
  post_id      INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id    INTEGER NOT NULL REFERENCES authors (author_id) ON DELETE CASCADE,
  title        TEXT NOT NULL CHECK (title <> ''),
  body         TEXT NOT NULL,
  status       TEXT NOT NULL DEFAULT 'draft',
  published_at TIMESTAMPTZ
);

Your \d posts should look like this — same shape, same constraints:

blog=# \d posts
                                      Table "public.posts"
    Column    |           Type           | Collation | Nullable |           Default
--------------+--------------------------+-----------+----------+------------------------------
 post_id      | integer                  |           | not null | generated always as identity
 author_id    | integer                  |           | not null |
 title        | text                     |           | not null |
 body         | text                     |           | not null |
 status       | text                     |           | not null | 'draft'::text
 published_at | timestamp with time zone |           |          |
Indexes:
    "posts_pkey" PRIMARY KEY, btree (post_id)
Check constraints:
    "posts_title_check" CHECK (title <> ''::text)
Foreign-key constraints:
    "posts_author_id_fkey" FOREIGN KEY (author_id) REFERENCES authors(author_id) ON DELETE CASCADE

Recap: you created a database, built the four-table web-shop schema with identity primary keys, made the database itself enforce your business rules (NOT NULL, UNIQUE, CHECK, foreign keys), evolved it with ALTER TABLE, and learned to drop things without flinching — or rather, with exactly the right amount of flinching. Every post in this series builds on the tables you just made. See you in the next one!