Constraints

CHECK

CREATE TABLE products (
    product_no integer,
    
    -- named column constraint
    price numeric CONSTRAINT positive_price CHECK (price > 0),

    -- column contraint    
    discounted_price numeric CHECK (discounted_price > 0),
    
    -- table constraint as defined not within a column
    CHECK (price > discounted_price),
    
    -- this is also possible
    CHECK (discounted_price > 0 AND price > discounted_price),
    
    -- named table constraint
    CONSTRAINT valid_discount CHECK (price > discounted_price)
);

NOT NULL

CREATE TABLE products (
    -- (always column related)
    product_no integer NOT NULL

);

UNIQUE

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 (

    -- column constraint
    product_no integer UNIQUE,
    
    -- named
    product_no integer CONSTRAINT must_be_different UNIQUE,
    
    -- table constraint
    UNIQUE (name),
    
    -- 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
    UNIQUE (a, c)
);

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.

UNIQUE NULLS NOT DISTINCT (product_no, column_b, column_c) 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

PRIMARY KEY

UNIQUE NOT NULL = PRIMARY KEY

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    age numeric
    
    -- compound key example
    PRIMARY KEY (product_no, age)
    
    -- example from codebase
    constraint transaction_changelog_pk primary key (id)

);

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

Foreign Keys

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),
    quantity integer
    
    -- NOTE. in absence of a column list the primary key of the 
    -- referenced table is used as the referenced column(s)
    product_no integer REFERENCES products,
    
    -- example for group of columns
    FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
    
    -- example from codebase
    constraint request_fk references processes

);

Example of many-to-many relation

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)
);

what if a product is removed after an order is created that references it?

E.g., 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,
    ...
);

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

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

• 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 behaviour 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.

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

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