PostgreSQL CREATE TABLE and Constraints
On this page
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.
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":
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:
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 constraintThe interesting choice is what happens when someone deletes a department that still has employees. That's the ON DELETE action:
| Action | When the department is deleted |
|---|---|
NO ACTION (default) | The delete fails. The check can be deferred to the end of the transaction. |
RESTRICT | The delete fails straight away, with no deferring. |
SET NULL | The employees stay, with department_id set to NULL. |
SET DEFAULT | The employees get the column's default value. |
CASCADE | The 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 > 0stops 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.
CREATE TABLE t (n int CHECK (n > 0));
INSERT INTO t VALUES (NULL); -- acceptedName 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:
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.
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.