Constraints

Constraints are rules the database enforces on a column’s values, so invalid data can never be stored in the first place. There are four:

Constraint	Rule
not null	The column must always have a value.
unique	No two rows may share a value (empty values aside).
default <value>	The value to use when an insert omits the column.
check (<expr>)	Every value must satisfy a condition you write.

Adding constraints

You can add a constraint to an existing column at any time. In the example library, a book’s publication year should be positive, and a sensible fallback is useful when it is unknown:

add constraint default 2000 to books.published
add constraint check (published > 1400) to books.published

Constraints show up in the column’s Constraints cell in show table:

show table books

books
┌───────────┬────────┬──────────────────────────────────────────┐
│ Name      │ Type   │ Constraints                              │
├───────────┼────────┼──────────────────────────────────────────┤
│ book_id   │ serial │ PK                                       │
│ title     │ text   │ NOT NULL                                 │
│ author_id │ int    │                                          │
│ published │ int    │ DEFAULT 2000, CHECK ("published" > 1400) │
│ isbn      │ text   │ UNIQUE                                   │
└───────────┴────────┴──────────────────────────────────────────┘

Here title is NOT NULL and isbn is UNIQUE — both were declared when the table was built (see below). PK marks the primary key, which is fixed at creation time (Tables).

Declaring constraints when you build the table

Constraints can also be part of a column’s spec from the start. In advanced mode they sit inline in CREATE TABLE:

create table books (
  book_id serial primary key,
  title text not null,
  author_id int,
  published int default 2000 check (published > 1400),
  isbn text unique
)

Adding a constraint to existing data

When you add a constraint to a column that already holds rows, the playground checks the current data first. If any row would break the new rule — a null where you are adding not null, a duplicate where you are adding unique, or a value that fails a check — the change is refused and the offending rows are listed, so you can fix the data and try again. Nothing is changed until the constraint can hold for every row.

Removing a constraint

Name the kind to drop it (there is at most one of each kind per column):

drop constraint check from books.published
drop constraint default from books.published

In advanced mode

A table-level check or unique can be added and dropped with ALTER TABLE; a named constraint can then be dropped by that name:

alter table books add constraint published_range check (published > 1400)
alter table books add unique (title, author_id)
alter table books drop constraint published_range

Syntax

add constraint not null to <Table>.<col>
add constraint unique to <Table>.<col>
add constraint default <value> to <Table>.<col>
add constraint check (<expr>) to <Table>.<col>
drop constraint (not null | unique | default | check) from <Table>.<col>

See also Columns, Types, and Inserting & editing data.