Catalog My learning Blog
PostgreSQL Course: Beginner to Production in 30 Days · Phase 1 · Lesson 5 · 6 min read

PostgreSQL CREATE TABLE and Constraints

On this page
  1. Why constraints matter
  2. PRIMARY KEY and UNIQUE constraints
  3. Foreign keys and ON DELETE actions
  4. NOT NULL, CHECK and DEFAULT
  5. ALTER TABLE: changing a table later
  6. DROP TABLE and CASCADE
  7. Check yourself

Why constraints matter

Application code changes, scripts get written in a hurry, and someone always runs a manual fix in production. If the only thing keeping data clean is application code, bad rows will get in eventually. Constraints put the rules in the database, where every writer has to obey them.

This lesson builds two tables, departments and employees. Each constraint is there to block one specific kind of bad row.

SQL
CREATE TABLE departments (
  id    int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name  text NOT NULL UNIQUE
);

CREATE TABLE employees (
  id             int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email          text NOT NULL UNIQUE,
  first_name     text NOT NULL,
  last_name      text NOT NULL,
  salary         numeric(10,2) NOT NULL CHECK (salary > 0),
  status         text NOT NULL DEFAULT 'active',
  hired_at       timestamptz NOT NULL DEFAULT now(),
  department_id  int REFERENCES departments (id) ON DELETE SET NULL,
  manager_id     int REFERENCES employees (id) ON DELETE SET NULL,
  CONSTRAINT employees_status_check CHECK (status IN ('active', 'on_leave', 'terminated'))
);

Now let's go through what each line prevents.

PRIMARY KEY and UNIQUE constraints

PRIMARY KEY gives every row an identity. It's unique, can't be NULL, and PostgreSQL builds an index for it automatically. A table has at most one primary key, but it can span several columns. That's how you model "a student can enroll in a course once":

SQL
CREATE TABLE enrollments (
  student_id   int NOT NULL,
  course_id    int NOT NULL,
  enrolled_on  date NOT NULL DEFAULT CURRENT_DATE,
  PRIMARY KEY (student_id, course_id)
);

UNIQUE blocks duplicates in other columns, such as email. A table can have as many as you need. One surprise: by default UNIQUE allows any number of NULLs, because each NULL counts as different from every other. If a value must be present and unique, add NOT NULL, as email does above. Since PostgreSQL 15, you can also write UNIQUE NULLS NOT DISTINCT to allow only one NULL.

Foreign keys and ON DELETE actions

department_id uses REFERENCES, which makes it a foreign key. You can't insert an employee into department 99 unless department 99 exists:

SQL
INSERT INTO employees (email, first_name, last_name, salary, department_id)
VALUES ('x@example.com', 'Xan', 'Lee', 50000, 99);
-- ERROR: insert or update on table "employees" violates foreign key constraint

The interesting choice is what happens when someone deletes a department that still has employees. That's the ON DELETE action:

ActionWhen the department is deleted
NO ACTION (default)The delete fails. The check can be deferred to the end of the transaction.
RESTRICTThe delete fails straight away, with no deferring.
SET NULLThe employees stay, with department_id set to NULL.
SET DEFAULTThe employees get the column's default value.
CASCADEThe employees are deleted too.

Be careful with CASCADE. One DELETE on a parent can quietly remove rows from a whole chain of child tables. Use the default or SET NULL unless the child rows truly mean nothing without the parent, like the lines of an order.

NOT NULL, CHECK and DEFAULT

Three more tools handle single columns:

  • NOT NULL means the value is required. Most columns should have it, so treat NULL as something you allow on purpose.
  • CHECK rejects a row when its expression is false. salary > 0 stops negative salaries, and the named status check works like a small enum.
  • DEFAULT fills in a value when the INSERT leaves the column out. It can call functions like now().

There's one trap with CHECK. It only rejects a row when the expression is false. If the value is NULL, the expression is unknown, and the row gets through. That's why salary has both NOT NULL and CHECK.

SQL
CREATE TABLE t (n int CHECK (n > 0));
INSERT INTO t VALUES (NULL);   -- accepted

Name your constraints, as with employees_status_check. When something fails, the error names the rule, and later migrations can drop or change it by name.

A few naming habits save friction across a team. Use lowercase snake_case, because PostgreSQL folds unquoted names to lowercase and mixed case forces you to quote everywhere. Use plural table names, and name foreign keys after the singular table plus _id.

ALTER TABLE: changing a table later

Tables change over time. ALTER TABLE handles it:

SQL
ALTER TABLE employees ADD COLUMN phone text;
ALTER TABLE employees ADD COLUMN is_remote boolean NOT NULL DEFAULT false;
ALTER TABLE employees RENAME COLUMN is_remote TO remote;
ALTER TABLE employees DROP COLUMN phone;
ALTER TABLE employees ADD CONSTRAINT employees_salary_cap CHECK (salary < 1000000);

How long these take matters on big tables. Adding a column with a constant default is instant since PostgreSQL 11, because the default is stored once in the catalog, not written into every row. A default that must be computed per row, like random(), still rewrites the whole table. Adding a constraint scans every row to check it. The migration course covers ways to do this without blocking writes.

DROP TABLE and CASCADE

DROP TABLE deletes the table and its data. There's no undo apart from a backup, or rolling back a transaction you haven't committed yet.

Code
DROP TABLE departments;            -- fails: employees depends on it
DROP TABLE IF EXISTS departments CASCADE;

The first command fails because employees.department_id still references it. That's PostgreSQL protecting you. CASCADE drops the dependent objects too, but note what that means: it removes the foreign key constraint and any views built on the table, not the employees' rows. Look before you cascade in production. In psql, \d+ departments shows what references the table.

Check yourself

No. UNIQUE treats each NULL as distinct, so many NULLs are allowed. Add NOT NULL if email is required, or use UNIQUE NULLS NOT DISTINCT to allow just one.

The invoices' foreign key to customers used ON DELETE CASCADE. For records you need to keep, use the default (NO ACTION) or RESTRICT so the delete is blocked instead.

With a NULL discount, discount BETWEEN 0 AND 50 evaluates to unknown, not false, and a CHECK constraint only rejects rows where the expression is false. So NULLs pass. If a discount is always required, add NOT NULL, ideally with DEFAULT 0, so missing values become an explicit zero.

Add it as NOT VALID first, which only checks new rows and takes a brief lock, then run ALTER TABLE ... VALIDATE CONSTRAINT separately. Validation scans existing rows but with a weaker lock that allows normal reads and writes to continue.

Usually yes. PostgreSQL indexes the referenced primary key automatically, but not the referencing column. Without an index on orders.customer_id, deleting or updating a customer forces a full scan of orders to check for references, and joins on that column are slower.