Data types are a way to limit the kind of data that can be
stored in a table. For many applications, however, the constraint
they provide is too coarse. For example, a column containing a
product price should probably only accept positive values. But
there is no standard data type that accepts only positive
numbers. Another issue is that you might want to constrain column
data with respect to other columns or rows. For example, in a
table containing product information, there should be only one
row for each product number.

To that end, SQL allows you to define constraints on columns
and tables. Constraints give you as much control over the data in
your tables as you wish. If a user attempts to store data in a
column that would violate a constraint, an error is raised. This
applies even if the value came from the default value
definition.

A check constraint is the most generic constraint type. It
allows you to specify that the value in a certain column must
satisfy a Boolean (truth-value) expression. For instance, to
require positive product prices, you could use:

As you see, the constraint definition comes after the data
type, just like default value definitions. Default values and
constraints can be listed in any order. A check constraint
consists of the key word CHECK
followed by an expression in parentheses. The check constraint
expression should involve the column thus constrained,
otherwise the constraint would not make too much
sense.

You can also give the constraint a separate name. This
clarifies error messages and allows you to refer to the
constraint when you need to change it. The syntax is:

So, to specify a named constraint, use the key word
CONSTRAINT followed by an identifier
followed by the constraint definition. (If you don't specify a
constraint name in this way, the system chooses a name for
you.)

A check constraint can also refer to several columns. Say
you store a regular price and a discounted price, and you want
to ensure that the discounted price is lower than the regular
price:

The first two constraints should look familiar. The third
one uses a new syntax. It is not attached to a particular
column, instead it appears as a separate item in the
comma-separated column list. Column definitions and these
constraint definitions can be listed in mixed order.

We say that the first two constraints are column
constraints, whereas the third one is a table constraint
because it is written separately from any one column
definition. Column constraints can also be written as table
constraints, while the reverse is not necessarily possible,
since a column constraint is supposed to refer to only the
column it is attached to. (PostgreSQL doesn't enforce that rule, but
you should follow it if you want your table definitions to work
with other database systems.) The above example could also be
written as:

It should be noted that a check constraint is satisfied if
the check expression evaluates to true or the null value. Since
most expressions will evaluate to the null value if any operand
is null, they will not prevent null values in the constrained
columns. To ensure that a column does not contain null values,
the not-null constraint described in the next section can be
used.

A not-null constraint is always written as a column
constraint. A not-null constraint is functionally equivalent to
creating a check constraint CHECK
(column_name IS NOT NULL),
but in PostgreSQL creating an
explicit not-null constraint is more efficient. The drawback is
that you cannot give explicit names to not-null constraints
created this way.

Of course, a column can have more than one constraint. Just
write the constraints one after another:

The order doesn't matter. It does not necessarily determine
in which order the constraints are checked.

The NOT NULL constraint has an
inverse: the NULL constraint. This
does not mean that the column must be null, which would surely
be useless. Instead, this simply selects the default behavior
that the column might be null. The NULL constraint is not present in the SQL
standard and should not be used in portable applications. (It
was only added to PostgreSQL
to be compatible with some other database systems.) Some users,
however, like it because it makes it easy to toggle the
constraint in a script file. For example, you could start
with:

In general, a unique constraint is violated when there is
more than one row in the table where the values of all of the
columns included in the constraint are equal. However, two null
values are not considered equal in this comparison. That means
even in the presence of a unique constraint it is possible to
store duplicate rows that contain a null value in at least one
of the constrained columns. This behavior conforms to the SQL
standard, but we have heard that other SQL databases might not
follow this rule. So be careful when developing applications
that are intended to be portable.

A primary key indicates that a column or group of columns
can be used as a unique identifier for rows in the table. (This
is a direct consequence of the definition of a primary key.
Note that a unique constraint does not, by itself, provide a
unique identifier because it does not exclude null values.)
This is useful both for documentation purposes and for client
applications. For example, a GUI application that allows
modifying row values probably needs to know the primary key of
a table to be able to identify rows uniquely.

A table can have at most one primary key. (There can be any
number of unique and not-null constraints, which are
functionally the same thing, but only one can be identified as
the primary key.) Relational database theory dictates that
every table must have a primary key. This rule is not enforced
by PostgreSQL, but it is
usually best to follow it.

A foreign key constraint specifies that the values in a
column (or a group of columns) must match the values appearing
in some row of another table. We say this maintains the
referential integrity between two
related tables.

Say you have the product table that we have used several
times already:

Let's also assume you have a table storing orders of those
products. We want to ensure that the orders table only contains
orders of products that actually exist. So we define a foreign
key constraint in the orders table that references the products
table:

Of course, the number and type of the constrained columns
need to match the number and type of the referenced
columns.

You can assign your own name for a foreign key constraint,
in the usual way.

A table can contain more than one foreign key constraint.
This is used to implement many-to-many relationships between
tables. Say you have tables about products and orders, but now
you want to allow one order to contain possibly many products
(which the structure above did not allow). You could use this
table structure:

Notice that the primary key overlaps with the foreign keys
in the last table.

We know that the foreign keys disallow creation of orders
that do not relate to any products. But what if a product is
removed after an order is created that references it? SQL
allows you to handle that as well. Intuitively, we have a few
options:

Disallow deleting a referenced product

Delete the orders as well

Something else?

To illustrate this, let's implement the following policy on
the many-to-many relationship example above: when someone wants
to remove a product that is still referenced by an order (via
order_items), we disallow it. If
someone removes an order, the order items are removed as
well:

Restricting and cascading deletes are the two most common
options. RESTRICT prevents deletion of
a referenced row. NO ACTION means that
if any referencing rows still exist when the constraint is
checked, an error is raised; this is the default behavior if
you do not specify anything. (The essential difference between
these two choices is that NO ACTION
allows the check to be deferred until later in the transaction,
whereas RESTRICT does not.) CASCADE specifies that when a referenced row is
deleted, row(s) referencing it should be automatically deleted
as well. There are two other options: SET
NULL and SET DEFAULT. These cause
the referencing columns to be set to nulls or default values,
respectively, when the referenced row is deleted. Note that
these do not excuse you from observing any constraints. For
example, if an action specifies SET
DEFAULT but the default value would not satisfy the
foreign key, the operation will fail.

Analogous to ON DELETE there is
also ON UPDATE which is invoked when a
referenced column is changed (updated). The possible actions
are the same.

Finally, we should mention that a foreign key must reference
columns that either are a primary key or form a unique
constraint. If the foreign key references a unique constraint,
there are some additional possibilities regarding how null
values are matched. These are explained in the reference
documentation for CREATE TABLE.