5.4. Constraints

5.4. Constraints
Prev	Up	Chapter 5. Data Definition	Home	Next

5.4.1. Check Constraints
5.4.2. Not-Null Constraints
5.4.3. Unique Constraints
5.4.4. Primary Keys
5.4.5. Foreign Keys
5.4.6. Exclusion Constraints

+ 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. +

5.4.1. Check Constraints

+ 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: +

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

+ 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: +

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

+ 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: +

+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 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: +

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

+ or even: +

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

+ It's a matter of taste. +

+ Names can be assigned to table constraints in the same way as + column constraints: +

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

+ 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. +

Note

+ PostgreSQL does not support + CHECK constraints that reference table data other than + the new or updated row being checked. While a CHECK + constraint that violates this rule may appear to work in simple + tests, it cannot guarantee that the database will not reach a state + in which the constraint condition is false (due to subsequent changes + of the other row(s) involved). This would cause a database dump and + restore to fail. The restore could fail even when the complete + database state is consistent with the constraint, due to rows not + being loaded in an order that will satisfy the constraint. If + possible, use UNIQUE, EXCLUDE, + or FOREIGN KEY constraints to express + cross-row and cross-table restrictions. +

+ If what you desire is a one-time check against other rows at row + insertion, rather than a continuously-maintained consistency + guarantee, a custom trigger can be used + to implement that. (This approach avoids the dump/restore problem because + pg_dump does not reinstall triggers until after + restoring data, so that the check will not be enforced during a + dump/restore.) +

Note

+ PostgreSQL assumes that + CHECK constraints' conditions are immutable, that + is, they will always give the same result for the same input row. + This assumption is what justifies examining CHECK + constraints only when rows are inserted or updated, and not at other + times. (The warning above about not referencing other table data is + really a special case of this restriction.) +

+ An example of a common way to break this assumption is to reference a + user-defined function in a CHECK expression, and + then change the behavior of that + function. PostgreSQL does not disallow + that, but it will not notice if there are rows in the table that now + violate the CHECK constraint. That would cause a + subsequent database dump and restore to fail. + The recommended way to handle such a change is to drop the constraint + (using ALTER TABLE), adjust the function definition, + and re-add the constraint, thereby rechecking it against all table rows. +

5.4.2. Not-Null Constraints

+ A not-null constraint simply specifies that a column must not + assume the null value. A syntax example: +

+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. 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: +

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

+ 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: +

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

+ and then insert the NOT key word where desired. +

Tip

+ In most database designs the majority of columns should be marked + not null. +

5.4.3. 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. The syntax is: +

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

+ when written as a column constraint, and: +

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

+ when written as a table constraint. +

+ 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. +

+ You can assign your own name for a unique constraint, in the usual way: +

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

+ 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 of the + columns included in the constraint are equal. + By default, 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 can be changed by adding the clause NULLS + NOT DISTINCT, like +

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

+ or +

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

+ The default behavior can be specified explicitly using NULLS + DISTINCT. The default null treatment in unique constraints is + implementation-defined according to the SQL standard, and other + implementations have a different behavior. So be careful when developing + applications that are intended to be portable. +

5.4.4. 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. So, the following + two table definitions accept the same data: +

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

+ +

+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. (There can be any number + of unique and not-null constraints, which are functionally almost 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. +

+ Primary keys are 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. There are also various ways in which the database system + makes use of a primary key if one has been declared; for example, + the primary key defines the default target column(s) for foreign keys + referencing its table. +

5.4.5. Foreign Keys

+ 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: +

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

+ 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: +

+CREATE TABLE orders (
+    order_id integer PRIMARY KEY,
+    product_no integer REFERENCES products (product_no),
+    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 that in this situation the orders table is the + referencing table and the products table is + the referenced table. Similarly, there are + referencing and referenced columns. +

+ You can also shorten the above command to: +

+CREATE TABLE orders (
+    order_id integer PRIMARY KEY,
+    product_no integer REFERENCES products,
+    quantity integer
+);
+

+ because in absence of a column list the primary key of the + referenced table is used as the referenced column(s). +

+ 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. + As usual, it then needs to be written in table constraint form. + Here is a contrived syntax example: +

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

+ Of course, 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 top-level node would have NULL parent_id, + while non-NULL parent_id entries would be + constrained to reference valid rows of the table. +

+ A table can have 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: +

+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,
+    order_id integer REFERENCES orders,
+    quantity integer,
+    PRIMARY KEY (product_no, order_id)
+);
+

+ 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: +

+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. + (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 column(s) in the referencing row(s) + to be set to nulls or their 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 constraint, the + operation will fail. +

+ The appropriate choice of 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. If the two tables + represent independent objects, then RESTRICT or + NO ACTION is more appropriate; an application that + actually wants to delete both objects would then have to be explicit about + this and run two delete commands. In the above example, order items are + part of an order, and it is convenient if they are deleted automatically + if an order is deleted. But products and orders are different things, and + so making a deletion of a product automatically cause the deletion of some + order items could be considered problematic. The actions SET + NULL or SET DEFAULT can be appropriate if a + foreign-key relationship represents optional information. For example, if + the products table contained a reference to a product manager, and the + product manager entry gets deleted, then setting the product's product + manager to null or a default might be useful. +

+ The actions SET NULL and SET DEFAULT + can take a column list to specify which columns to set. Normally, all + columns of the foreign-key constraint are set; setting only a subset is + useful in some special cases. Consider the following example: +

+CREATE TABLE tenants (
+    tenant_id integer PRIMARY KEY
+);
+
+CREATE TABLE users (
+    tenant_id integer REFERENCES tenants ON DELETE CASCADE,
+    user_id integer NOT NULL,
+    PRIMARY KEY (tenant_id, user_id)
+);
+
+CREATE TABLE posts (
+    tenant_id integer REFERENCES tenants ON DELETE CASCADE,
+    post_id integer NOT NULL,
+    author_id integer,
+    PRIMARY KEY (tenant_id, post_id),
+    FOREIGN KEY (tenant_id, author_id) REFERENCES users ON DELETE SET NULL (author_id)
+);
+

+ Without the specification of the column, the foreign key would also set + the column tenant_id to null, but that column is still + required as part of the primary key. +

+ 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). +

+ Normally, a referencing row need not satisfy the foreign key constraint + if any of its referencing columns are null. If MATCH FULL + is added to the foreign key declaration, a referencing row escapes + satisfying the constraint only if all its referencing columns are null + (so a mix of null and non-null values is guaranteed to fail a + MATCH FULL constraint). If you don't want referencing rows + to be able to avoid satisfying the foreign key constraint, declare the + referencing column(s) as NOT NULL. +

+ A foreign key must reference columns that either are a primary key or + form a unique constraint. This means that the referenced columns always + have an index (the one underlying the primary key or unique constraint); + so checks on whether a referencing row has a match will be efficient. + Since a DELETE of a row from the referenced table + or an UPDATE of a referenced column will require + a scan of the referencing table for rows matching the old value, it + is often a good idea to index the referencing columns too. Because this + is not always needed, and there are many choices available on how + to index, declaration of a foreign key constraint does not + automatically create an index on the referencing columns. +

+ More information about updating and deleting data is in Chapter 6. Also see the description of foreign key constraint + syntax in the reference documentation for + CREATE TABLE. +

5.4.6. Exclusion Constraints

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

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

+ See also CREATE + TABLE ... CONSTRAINT ... EXCLUDE for details. +

+ Adding an exclusion constraint will automatically create an index + of the type specified in the constraint declaration. +

Prev	Up	Next
5.3. Generated Columns	Home	5.5. System Columns