Constraints#

Constraints enable you to set restrictions on the data storable in table columns (i.e. other than data type). If data to be entered violates a constraint, an error is raised (even if the value is a default).

Constraints can be written in forms:

  • Column constraints: follow a column definition’s data type, and apply to it alone e.g:

    CREATE TABLE table_name (
  column_name     data_type column_constraint(s),
  ...
);

  • Table constraints: written separately from column definitions e.g:

    CREATE TABLE table_name (
  column_name    data_type,
  ...
  table_constraint,
  ...
);

    Table constraints and column definitions can be written in any order.

Some column constraints can be written as table constraints:

CREATE TABLE table_name (
  col1    data_type some_constraint,  --column constraint
  col2    data_type,
  some_constraint (col2)  -- table constraint
);

To name a constraint, use the key word CONSTRAINT, followed by a name, followed by the constraint definition. Otherwise, the system chooses a name for you.

1. Check Constraints#

Specify that the values in a column must satisfy a Boolean expression. Consist of the CHECK key word, and an expression in ():

mydb=> CREATE TABLE beverages (
mydb(>   name           text,
mydb(>   price          numeric(5,2) CHECK (price > 0),
mydb(>   serving_temp   text
mydb(> );
CREATE TABLE

Values are only included if the check expression evaluates to true or null:

mydb=> INSERT INTO beverages (name, price, serving_temp) VALUES ('Water', 0, 'cool');
ERROR:  new row for relation "beverages" violates check constraint "beverages_price_check"
DETAIL:  Failing row contains (Water, 0.00, cool).
mydb=> INSERT INTO beverages (name, serving_temp) VALUES ('Water', 'cool');  -- null price (default)
INSERT 0 1
mydb=> INSERT INTO beverages (name, price, serving_temp) VALUES ('Tea', 9.99, 'hot');
INSERT 0 1
mydb=> SELECT * FROM beverages;
 name  | price | serving_temp 
-------+-------+--------------
 Water |       | cool
 Tea   |  9.99 | hot
(2 rows)

You can name the check constraint to easily reference it and to clarify error messages.

mydb=> DROP TABLE beverages;
DROP TABLE
mydb=> CREATE TABLE beverages (
mydb(>   name           text,
mydb(>   price          numeric(5,2) CONSTRAINT positive_price CHECK (price > 0),
mydb(>   serving_temp   text
mydb(> );
CREATE TABLE
mydb=> INSERT INTO beverages (name, price, serving_temp) VALUES ('Water', 0, 'cool');
ERROR:  new row for relation "beverages" violates check constraint "positive_price"
DETAIL:  Failing row contains (Water, 0.00, cool).

A check constraint can refer to multiple columns when written as a table constraint:

mydb=> DROP TABLE beverages;
DROP TABLE
mydb=> CREATE TABLE beverages (
mydb(>   name           text,
mydb(>   price          numeric(5,2),
mydb(>   serving_temp   text,
mydb(>   CONSTRAINT valid_beverage CHECK (price > 0
mydb(>                                    AND serving_temp IN ('cold', 'cool', 'room', 'warm', 'hot'))
mydb(> );
CREATE TABLE
mydb=> INSERT INTO beverages (name, price, serving_temp) VALUES ('Water', 0, 'cool');
ERROR:  new row for relation "beverages" violates check constraint "valid_beverage"
DETAIL:  Failing row contains (Water, 0.00, cool).
mydb=> INSERT INTO beverages (name, price, serving_temp) VALUES ('Water', 1, 'icy');
ERROR:  new row for relation "beverages" violates check constraint "valid_beverage"
DETAIL:  Failing row contains (Water, 1.00, icy).
mydb=> INSERT INTO beverages (name, price, serving_temp) VALUES ('Water', 1, 'room');
INSERT 0 1

Caution

PostgreSQL assumes that CHECK constraints’ conditions are immutable. In case of changes, drop the constraint (using ALTER TABLE) and then re-add it to re-check it against all rows.

2. Not-Null Constraints#

Specify that a column must not assume the null value. Always written as column constraints.

mydb=> DROP TABLE beverages;
DROP TABLE
mydb=> CREATE TABLE beverages (
mydb(>   name           text NOT NULL,
mydb(>   price          numeric(5,2) NOT NULL,
mydb(>   serving_temp   text
mydb(> );
CREATE TABLE
mydb=> INSERT INTO beverages (name, serving_temp) VALUES ('Milk', 'warm');
ERROR:  null value in column "price" of relation "beverages" violates not-null constraint
DETAIL:  Failing row contains (Milk, null, warm).
mydb=> INSERT INTO beverages (name, price) VALUES ('Milk', 7.50);  -- NULL serving_temp not restricted
INSERT 0 1
mydb=> SELECT * FROM beverages;
 name | price | serving_temp 
------+-------+--------------
 Milk |  7.50 | 
(1 row)

Tip

The NOT NULL syntax in the example above doesn’t support naming. If you must name a not-null constraint, specify it as CONSTRAINT constraint_name CHECK (column_name IS NOT NULL).

3. Unique Constraints#

Ensure that the data contained in a column (or group of columns) is unique among all rows in the table.

mydb=> DROP TABLE beverages;
DROP TABLE
mydb=> CREATE TABLE beverages (
mydb(>   name           text UNIQUE,
mydb(>   price          numeric(5,2),
mydb(>   serving_temp   text
mydb(> );
CREATE TABLE
mydb=> INSERT INTO beverages (name, price, serving_temp) VALUES ('Milk', 7.50, 'warm');
INSERT 0 1
mydb=> INSERT INTO beverages (name, price, serving_temp) VALUES ('Milk', 7.50, 'hot');
ERROR:  duplicate key value violates unique constraint "beverages_name_key"
DETAIL:  Key (name)=(Milk) already exists.

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

mydb=> DROP TABLE beverages;
DROP TABLE
mydb=> CREATE TABLE beverages (
mydb(>   name           text,
mydb(>   price          numeric(5,2),
mydb(>   serving_temp   text,
mydb(>   UNIQUE (name, serving_temp)
mydb(> );
CREATE TABLE
mydb=> INSERT INTO beverages (name, price, serving_temp) VALUES ('Milk', 7.50, 'hot');
INSERT 0 1
mydb=> INSERT INTO beverages (name, price, serving_temp) VALUES ('Milk', 6.50, 'hot');
ERROR:  duplicate key value violates unique constraint "beverages_name_serving_temp_key"
DETAIL:  Key (name, serving_temp)=(Milk, hot) already exists.

Adding a unique constraint will automatically create a unique B-tree index on the column(s) listed in the constraint.

Caution

Since null values are by default considered unequal, it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. Adding a NULLS NOT DISTINCT clause or NOT NULL constraint can fix this.

Keep in mind that some platforms might implement unique constraints’ null treatment differently.

4. Primary Keys#

Indicate that a column (or group of columns) can be used as a unique identifier for rows in a table (unique and not null).

A table can have only one primary key. Relational database theory dictates that every table must have a primary key.

mydb=> DROP TABLE beverages;
DROP TABLE
mydb=> CREATE TABLE beverages (
mydb(>   name           text,
mydb(>   price          numeric(5,2),
mydb(>   serving_temp   text,
mydb(>   PRIMARY KEY (name, serving_temp)
mydb(> );
CREATE TABLE
mydb=> INSERT INTO beverages (name, price, serving_temp) VALUES ('Lemonade', 5, 'cool');
INSERT 0 1
mydb=> INSERT INTO beverages (name, price, serving_temp) VALUES ('Lemonade', 5.75, 'cool');
ERROR:  duplicate key value violates unique constraint "beverages_pkey"
DETAIL:  Key (name, serving_temp)=(Lemonade, cool) already exists.
mydb=> INSERT INTO beverages (name, price) VALUES ('Lemonade', 5.75);
ERROR:  null value in column "serving_temp" of relation "beverages" violates not-null constraint
DETAIL:  Failing row contains (Lemonade, 5.75, null).

Adding a primary key will automatically create a unique B-tree index on the column(s) listed in the primary key, and will force the column(s) to be marked NOT NULL.

A primary key defines the default target column(s) for foreign keys referencing its table.

5. Foreign Keys#

Specify that the values in a column (or group of columns) must match the values appearing in some row of another table (maintain referential integrity between two related tables).

Extending the example from “Primary Keys” section above:

mydb=> CREATE TABLE beverage_sales (
mydb(>   transaction_id     serial PRIMARY KEY,
mydb(>   transaction_time   timestamp DEFAULT CURRENT_TIMESTAMP,
mydb(>   beverage           text,
mydb(>   serving_temp       text,
mydb(>   price              numeric(5,2),
mydb(>   FOREIGN KEY (beverage, serving_temp) REFERENCES beverages (name, serving_temp)
mydb(> );
CREATE TABLE
mydb=> SELECT * FROM beverages;
   name   | price | serving_temp 
----------+-------+--------------
 Lemonade |  5.00 | cool
(1 row)

mydb=> INSERT INTO beverage_sales (beverage, serving_temp, price)
mydb->   VALUES ('Lemonade', 'cool', 5.00);
INSERT 0 1
mydb=> INSERT INTO beverage_sales (beverage, serving_temp, price)
mydb->   VALUES ('Lemonade', 'cold', 6.00);
ERROR:  insert or update on table "beverage_sales" violates foreign key constraint "beverage_sales_beverage_serving_temp_fkey"
DETAIL:  Key (beverage, serving_temp)=(Lemonade, cold) is not present in table "beverages"
mydb=> INSERT INTO beverage_sales (beverage, serving_temp, price)
mydb->   VALUES ('Lemonade', DEFAULT, 6.00);  -- Null values might sneak in if not constrained
INSERT 0 1
mydb=> SELECT * FROM beverage_sales;
 transaction_id |      transaction_time      | beverage | serving_temp | price 
----------------+----------------------------+----------+--------------+-------
              1 | 2023-08-05 10:53:21.48406  | Lemonade | cool         |  5.00
              3 | 2023-08-05 11:01:08.471428 | Lemonade |              |  6.00
(2 rows)

We say the beverage_sales table is the referencing table, and the beverages table is the referenced table.

You can also define foreign key constraints as column constraints e.g. column_name data_type REFERENCES other_table (other_table_col).

A foreign key must reference columns that are either a primary key or form a unique constraint. In the absence of a column list in a foreign key declaration, the primary key of the referenced table is used as the referenced column(s).

A table can have more than one foreign key constraint, to implement many-to-many relationships.

A foreign key that references its own table is called a self-referential foreign key. Useful in some cases e.g. to make table rows represent nodes of a tree structure.

You can specify what action to take when an item in a referenced row has to be removed (ON DELETE) or changed (ON UPDATE):

  • NO ACTION: Produce an error indicating that the deletion or update would create a foreign key constraint violation.

  • RESTRICT: Just like NO ACTION, but can’t be deferred (is checked immediately).

  • CASCADE: Delete any rows referencing the deleted row, or update the values of the referencing column(s) to the new values of the referenced columns, respectively.

  • SET NULL: Set all of the referencing columns (or a specified subset, only for ON DELETE) to null.

  • SET DEFAULT: Set all of the referencing columns (or a specified subset, only for ON DELETE) to their default values.

e.g.

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

Tip

If referenced column(s) are changed frequently, it’s recommended to add an index to them so that referential actions associated with the foreign key constraint can be performed more efficiently.

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

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

CREATE TABLE circles (
    c circle,
    EXCLUDE USING gist (c WITH &&)  -- no overlapping cirlces
);