Constraints

April 2021

Constraints

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.

-- column constraint
CREATE TABLE products (
    product_no integer,
    name text,
    -- price numeric CHECK (price > 0) -- alternative w/o separate name
    price numeric CONSTRAINT positive_price CHECK (price > 0)
);

-- column and table constraints
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),  -- column constraint
    discounted_price numeric CHECK (discounted_price > 0), -- column constraint
    CHECK (price > discounted_price)  -- table constraint
    -- CONSTRAINT valid_discount CHECK (price > discounted_price)  -- with name
);

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.

Not-Null Constraints

A not-null constraint simply specifies that a column must not assume the null value. 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)
);

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. Adding a unique constraint will automatically create a unique B-tree index on the column or group of columns listed in the constraint.

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. However, two null values are never 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 (ie. the null value is in fact the differentiator).

CREATE TABLE products (
    product_no integer UNIQUE,  -- column constraint format
    -- product_no integer CONSTRAINT must_be_different UNIQUE,  -- with name
    name text,
    price numeric
    -- UNIQUE (product_no)  -- table constraint format
);

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

Primary Key

A primary key is a column or collection of columns whose values uniquely identify each row in a table. A valid primary key column enforces certain constraints:

1. The column or collection of columns must have a unique value for each row.
2. The column or collection of columns can’t have nulls (unlike a Unique Constraint).

NOTE: Primary key values only need to be unique within a table.

Natural keys

• The data already exists in the table, and you don’t need to add a column to create a key. Eg. license plates within a State; student IDs within a university, etc.
• Because the natural key data has meaning, it can reduce the need to join tables when searching.

Surrogate keys

• Because a surrogate key doesn’t have any meaning in itself and its values are independent of the data in the table, if your data changes later, you’re not limited by the key structure.
• Natural keys tend to consume more storage than the integers typically used for surrogate keys.
• Universally unique identifiers (UUID) are a great way to create surrogate ids.

Adding a Primary Key

-- column constraint format
CREATE TABLE natural_key_example (
    -- license_id varchar(10)  PRIMARY KEY, -- w/o separate name
    license_id varchar(10) CONSTRAINT license_key PRIMARY KEY,
    first_name varchar(50),
    last_name varchar(50)
);

-- table constraint format
CREATE TABLE natural_key_example (
    license_id varchar(10),
    first_name varchar(50),
    last_name varchar(50),
    CONSTRAINT license_key PRIMARY KEY (license_id)
);

Composite Primary Keys

If a single column is not enough to create a unique natural key, two or more columns could be utilized as a composite primary key.

-- Composite keys must be created using the table constraint format
CREATE TABLE natural_key_composite_example (
    student_id varchar(10),
    school_day date,
    present boolean,
    CONSTRAINT student_key PRIMARY KEY (student_id, school_day)
);

Creating an Auto-Incrementing Surrogate Key

-- creating the table
CREATE TABLE surrogate_key_example (
    order_number bigserial,
    product_name varchar(50),
    order_date date,
    CONSTRAINT order_key PRIMARY KEY (order_number)
);

-- inserting records
-- order_number column is omitted. Counts from 1 and auto-increments +1
INSERT INTO surrogate_key_example (product_name, order_date)
VALUES ('Beachball Polish', '2015-03-17'),
       ('Wrinkle De-Atomizer', '2017-05-22'),
       ('Flux Capacitor', '1985-10-26');

Index Numbering Watchout

Even though a column with a serial type auto-increments each time a row is added, some scenarios will create gaps in the sequence of numbers in the column. If a row is deleted, for example, the value in that row is never replaced. Or, if a row insert is aborted, the sequence for the column will still be incremented.

Foreign Key

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. A foreign key must reference columns that either are a primary key or form a unique constraint. Unlike a primary key, a foreign key column can be empty, and it can contain duplicate values.

Creating a Foreign Key

In the registrations table, we designate the column license_id as a foreign key by adding the REFERENCES keyword, followed by the table name and column for it to reference.

CREATE TABLE licenses (
    license_id varchar(10),
    first_name varchar(50),
    last_name varchar(50),
    CONSTRAINT licenses_key PRIMARY KEY (license_id)
);

CREATE TABLE registrations (
    registration_id varchar(10),
    registration_date date,
    -- new keyword: REFERENCES
    license_id varchar(10) REFERENCES licenses (license_id),
    CONSTRAINT registration_key PRIMARY KEY (registration_id, license_id)
);

Referential Integrity Implications

1. It affects the order we insert data. We cannot add data to a table that contains a foreign key before the other table referenced by the key has the related records, or we’ll get an error.

2. The reverse applies when we delete data. To maintain referential integrity, the foreign key constraint prevents us from deleting a row from licenses before removing any related rows in registrations, because doing so would leave an orphaned record.

CASCADE: Automatically Deleting (or Updating) Related Records

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.

This is more efficient as it allows to work ‘forwards’, ie. deleting the referenced row cascades over all referencing rows, versus having to deleted all referencing rows first and work ‘backwards’.

CREATE TABLE registrations (
    registration_id varchar(10),
    registration_date date,
    license_id varchar(10) REFERENCES licenses (license_id) ON DELETE CASCADE,
    CONSTRAINT registration_key PRIMARY KEY (registration_id, license_id)
);

NOTE: 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. In this case, CASCADE means that the updated values of the referenced column(s) should be copied into the referencing row(s).