PostgreSQL CREATE TABLE, ALTER and constraints

CREATE TABLE defines columns and the rules (constraints) the data must follow. PostgreSQL rejects any row that breaks a rule, so bad data never gets in.

PRIMARY KEY: unique and not null; identifies the row.
GENERATED ALWAYS AS IDENTITY: auto-numbering, the modern replacement for serial.
NOT NULL: a value is required.
UNIQUE: no two rows may share the value.
CHECK (condition): any rule you can express.
DEFAULT value: used when an INSERT doesn’t provide the column.
REFERENCES other(id): a foreign key. ON DELETE CASCADE deletes child rows with the parent; ON DELETE SET NULL clears the link; the default (NO ACTION) blocks the delete.

ALTER TABLE changes an existing table: add, drop or rename columns, change types, add constraints. DROP TABLE deletes the table and its data; IF EXISTS avoids an error when it’s already gone.

Syntax

CREATE TABLE name (
  id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  col  type [NOT NULL] [UNIQUE] [DEFAULT x] [CHECK (...)],
  fk   integer REFERENCES other(id) ON DELETE CASCADE
);
ALTER TABLE name ADD COLUMN c type;
DROP TABLE IF EXISTS name;

Examples

Edit the code and run it. Output is printed the way psql prints it.

16.1A table with every kind of constraint

DROP TABLE IF EXISTS reviews;

CREATE TABLE reviews (
  id          integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id  integer NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  customer_id integer REFERENCES customers(id) ON DELETE SET NULL,
  rating      smallint NOT NULL CHECK (rating BETWEEN 1 AND 5),
  body        text,
  created_at  timestamptz NOT NULL DEFAULT now(),
  UNIQUE (product_id, customer_id)
);

INSERT INTO reviews (product_id, customer_id, rating, body)
VALUES (1, 1, 5, 'Love the clicky keys'), (6, 2, 4, 'Great noise cancelling');

SELECT * FROM reviews;

id numbers itself; you never supply it.
UNIQUE (product_id, customer_id) is a table-level constraint: one review per customer per product.
created_at fills itself in with DEFAULT now().

16.2Watch the constraints say no

INSERT INTO reviews (product_id, customer_id, rating) VALUES (1, 3, 9);

Fails with “violates check constraint” because rating must be 1–5. Edit it to try the other rules: product_id 999 breaks the foreign key; (1, 1, 4) breaks the unique rule; leaving out rating breaks NOT NULL. Reading error messages is a skill; they always name the constraint.

16.3ALTER TABLE

ALTER TABLE reviews ADD COLUMN verified boolean NOT NULL DEFAULT false;
ALTER TABLE reviews RENAME COLUMN body TO comment;
ALTER TABLE reviews ALTER COLUMN comment TYPE varchar(500);
ALTER TABLE reviews ADD CONSTRAINT comment_not_empty CHECK (comment <> '');

SELECT column_name, data_type, column_default
FROM information_schema.columns
WHERE table_name = 'reviews'
ORDER BY ordinal_position;

Each ALTER changes the structure in place while keeping data. Adding a column with a default fills it for existing rows. Run this example twice and the first line fails because the column already exists; ADD COLUMN IF NOT EXISTS avoids that.

16.4ON DELETE CASCADE in action

BEGIN;
DELETE FROM products WHERE id = 1 AND NOT EXISTS (SELECT 1 FROM order_items WHERE product_id = 1);
SELECT count(*) AS reviews_left FROM reviews;
ROLLBACK;

-- this one is blocked by the order_items foreign key (no cascade there):
DELETE FROM products WHERE id = 1;

Product 1 has orders, so the guarded delete removes nothing. The final DELETE fails: order_items.product_id references products without a cascade rule, so PostgreSQL refuses to leave orphaned order lines. That protection is the whole point of foreign keys.

Exercises

Write a query and check it. Any query that returns the right rows passes.

Exercise 16.1

Create a table suppliers with an identity primary key id, a required unique name (text), a country (text) defaulting to India, and a rating integer that must be between 1 and 10. Then insert one supplier named Acme with only the name given.

Hint

CREATE TABLE ... ; INSERT INTO suppliers (name) VALUES ('Acme');

Solution

1
2

CREATE TABLE suppliers (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL UNIQUE, country text DEFAULT 'India', rating integer CHECK (rating BETWEEN 1 AND 10));
INSERT INTO suppliers (name) VALUES ('Acme');