PostgreSQL Constraints

References

• PostgreSQL Constraints Wikipedia Article

Notes

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 if the value came from the default value definition.

Check Constraints

A check constraint is the most generic constraint type. It allows you to specify the=at the value in a certain column must satisfy a Boolean (truth-value) expression.

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0)
);

Constraints come after data types. Default values and constraints can be listed in any order. 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.

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CONSTRAINT positive_price CHECK (price > 0)
);

To specify a named constraint, use the key word CONSTRAINT followed by an identifier followed by the constraint definition. A check constraint can also refer to several columns.

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

The last constraint in the example above is not attached to a particular column. We say that the first two constraints above are column constraints and the last one is a table constraint because it is written separately from any one column definition. Column constraints can also be written as table constraints, whereas the reverse is not possible.

A check constraint is satisfied if the check expression evaluates to true or the null value.

Not-Null Constraints

A not-null constraint simplify specifies that a column must not assume the null value.

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric
);

A not-null constraint is always written as a column constraint. You can not give a name to a not-null constraint written like above. The order of constraints does not matter - it does not determine the order in which constraints are checked.

Unique Constraints

Unique constraints ensure that the data contained in a column, or a group of columns, is unique, among all the rows in the table.

CREATE TABLE products (
    product_no integer UNIQUE,
    name text,
    price numeric
);

To define a unique constraint for a group of columns, write it as a table constraint with the column names separated by commas:

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c)
);

This specifies that the combination of values in the indicated columns is unique across the whole table, though any one of the columns need not be (and ordinarily isn't) unique. Adding a unique constraint will automatically create a unique B-tree index on the column or group of columns listed in the constraint. A uniqueness restriction covering only some rows cannot be written as a unique constraint, but it is possible to enforce such a restriction by creating a unique partial index. In general, a unique constraint is violated if there is more than one row in the table where the values of all the columns included in the constraint are equal. Two null values are not considered equal in this comparison. This means it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. This behavior can be changed by adding the clause NULLS NOT DISTINCT.

CREATE TABLE products (
    product_no integer UNIQUE NULLS NOT DISTINCT,
    name text,
    price numeric
);

Primary Keys

A primary key constraint indicates that a column, or group of columns, can be used as a unique identifier for rows in the table. This requires that the values be both unique and not null.

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

Primary keys can span more than one column; the syntax is similar to unique constraints:

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    PRIMARY KEY (a, c)
);

Adding a primary key will automatically create a unique B-tree index on the column or group of columns listed in the primary key, and will force the column(s) to be marked NOT NULL. A table can have at most one 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. Primary keys are useful both for documentation purposes and for client applications.

Foreign Keys

A foreign key constraint specifies that the values in a column (or 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.

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no), -- FOREIGN KEY
    quantity integer
);

Now it is impossible to create orders with non-NULL product_no entries that do not appear in the products table. We say in this situation that the orders table is the referencing table and the products table is the referenced table. Similarly, there are referencing and referenced columns. You can assign your own name for a foreign key constraint, in the usual way. A foreign key can also constrain and reference a group of columns.

CREATE TABLE t1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);

The number and type of the constrained columns need to match the number and type of the referenced columns. Sometimes it is useful for the “other table” of a foreign key constraint to be the same table; this is called a self-referential foreign key. For example, if you want rows of a table to represent nodes of a tree structure, you could write:

CREATE TABLE tree (
    node_id integer PRIMARY KEY,
    parent_id integer REFERENCES tree,
    name text,
    ...
);

A table can have more than one foreign key constraint. this is used to implement many-to-many relationships between tables. If the row that the foreign key references is deleted, you have a few options:

• Disallow deleting the referenced row
• Delete the referencing row as well
• Something else...

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

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. CASCADE specifies that when a referenced row is deleted, row(s) referencing it should be deleted as well. There are two other options: SET NULL and SET DEFAULT. These cause the referencing column(s) in the referencing row(s) to be set to nulls or their default values, respectively, when their referenced row is deleted.

The appropriate choice of an ON DELETE action depends on what kinds of objects the related tables represent. When the referencing table represents something that is a component of what is represented by the referenced table and cannot exist independently, then CASCADE could be appropriate. 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, except that column lists cannot be specified for SET NULL and SET DEFAULT. In this case, CASCADE means that the updated values of the referenced column(s) should be copied into the referencing row(s). A foreign key must reference columns that either are a primary key or form a unique constraint, or are columns from a non-partial unique index.

Exclusion Constraints

Exclusion constraints ensure that if any two rows are compared on the specified columns or expression using the specified operators, at least one of these operator comparisons will return false or null.

CREATE TABLE circles (
    c circle,
    EXCLUDE USING gist (c WITH &&)
);