Correctness and constraints

Introduction

Storing data is one thing; storing meaningful, useful, correct data is quite another. While meaning and utility are themselves subjective qualities, correctness at least can be logically defined and enforced. Types already ensure that numbers are numbers and dates are dates, but can't guarantee that weight or distance are positive numbers or prevent date ranges from overlapping. Tuple, table, and database constraints apply rules to data being stored and reject values or combinations of values which don't pass muster.

Constraints don't render other input validation techniques useless by any means, even when they test the same assertions. Time spent trying and failing to store invalid data is time wasted. Violation messaging, like assert in systems and application programming languages, only reveals the first problem with the first candidate record in much more detail than anyone not immediately involved with the database needs. But as far as the correctness of data is concerned, constraints are law, for good or ill; anything else is advice.

On tuples: not null, default, and check

Non-null constraints are the simplest category. A tuple must have a value for the constrained attribute, or put another way, NULL is no longer one of the allowed values for the column. No value means no tuple: the insert or update is rejected.

Protecting against null values is as easy as declaring column_name COLUMN_TYPE NOT NULL in CREATE TABLE or ADD COLUMN. Null values cause entire categories of problems between the database and end users, so reflexively defining non-null constraints on any column without a good reason to allow nulls is a good habit to get into.

The provision of a default value when an insert doesn't specify one is not always considered a constraint, since candidate records are modified and stored instead of rejected. In many DBMSs, default values may be generated by a function, although MySQL does not allow user-defined functions for this purpose.

A default is only used when it's asked for: the INSERT omits the column, or writes the DEFAULT keyword in its place. (An UPDATE leaves the columns it doesn't mention unchanged; SET column_name = DEFAULT resets one to its default.) An explicit NULL is not a request for the default. A nullable column stores the NULL, and a non-null column rejects it:

CREATE TABLE tickets (
  ticket_id INT PRIMARY KEY,
  priority VARCHAR(10) NOT NULL DEFAULT 'normal',
  assignee VARCHAR(20) DEFAULT 'triage'
);

-- 1. column omitted: the default is used
INSERT INTO tickets (ticket_id) VALUES (1);

-- 2. DEFAULT keyword: the default is used
INSERT INTO tickets (ticket_id, priority, assignee) VALUES (2, DEFAULT, DEFAULT);

-- 3. explicit NULL in a nullable column: NULL is stored
INSERT INTO tickets (ticket_id, assignee) VALUES (3, NULL);

-- 4. explicit NULL in a NOT NULL column: rejected
INSERT INTO tickets (ticket_id, priority) VALUES (4, NULL);

In PostgreSQL, the fourth insert fails. The failing row shows that the omitted assignee would have received its default, while priority kept the NULL it was given:

ERROR:  null value in column "priority" of relation "tickets" violates not-null constraint
DETAIL:  Failing row contains (4, null, triage).

The other three rows are stored, and ticket 3's assignee is NULL rather than 'triage' (psql displays NULL as an empty cell):

SELECT * FROM tickets ORDER BY ticket_id;

 ticket_id | priority | assignee
-----------+----------+----------
         1 | normal   | triage
         2 | normal   | triage
         3 | normal   |
(3 rows)

MySQL 8.4 behaves the same way in its default strict mode, rejecting the fourth insert with error 1048, Column 'priority' cannot be null. With strict mode turned off, MySQL still rejects a single-row insert like this one, but a multi-row INSERT or an UPDATE that writes NULL to a non-null column only raises a warning and stores the data type's implicit default -- an empty string for VARCHAR -- instead of the column's declared default.

Validation rules that depend on the values within a single tuple can be implemented as CHECK constraints. In PostgreSQL, a check rejects false, but accepts true or NULL. For example, CHECK (latitude BETWEEN -90 AND 90) permits a missing latitude; add NOT NULL if a value is required. CHECK (column_name IS NOT NULL) also rejects nulls because IS NOT NULL returns false for NULL. See PostgreSQL's constraint rules.

CREATE TABLE locations (
  latitude numeric CHECK (latitude BETWEEN -90 AND 90),
  longitude numeric NOT NULL CHECK (longitude BETWEEN -180 AND 180)
);
INSERT INTO locations VALUES (NULL, 0);

INSERT INTO locations VALUES (0, NULL);

On tables: unique and exclusion

Table-level constraints test tuples against each other. In a unique constraint, only one record may have any given set of non-null values for the constrained columns. By default, PostgreSQL treats nulls as distinct for uniqueness, so a unique constraint on (batman, robin) permits repeated values when robin is NULL. PostgreSQL 15 and later also support NULLS NOT DISTINCT to treat nulls as equal for this purpose. Other engines have their own rules; check the engine's documentation.

CREATE TABLE default_pairs (batman text, robin text, UNIQUE (batman, robin));
INSERT INTO default_pairs VALUES ('Bruce', NULL), ('Bruce', NULL);
CREATE TABLE strict_pairs (batman text, robin text, UNIQUE NULLS NOT DISTINCT (batman, robin));
INSERT INTO strict_pairs VALUES ('Bruce', NULL);

INSERT INTO strict_pairs VALUES ('Bruce', NULL);

Exclusion constraints, which PostgreSQL supports with EXCLUDE, fill a very useful niche: they can prevent overlaps. Specify the constrained fields and the operations by which each will be evaluated, and a new record will only be accepted if no existing record compares successfully with each field and operation. For instance, a schedules table can be configured to reject conflicts:

-- text, int, etc. comparisons in exclusion constraints require this
-- Postgres extension
CREATE EXTENSION btree_gist;

CREATE TABLE schedules (
  schedule_id SERIAL NOT NULL PRIMARY KEY,
  room_number TEXT NOT NULL,
  -- a range of TIMESTAMP WITH TIME ZONE provides both start and end
  duration TSTZRANGE,
  -- table-level constraints imply an index, since otherwise they'd
  -- have to search the entire table to validate a candidate record;
  -- GiST (generalized search tree) indexes are usually used in
  -- Postgres
  EXCLUDE USING GIST (
    room_number WITH =,
    duration WITH &&
  )
);

INSERT INTO schedules (room_number, duration)
VALUES ('32A', '[2020-08-20T10:00:00Z,2020-08-20T11:00:00Z)');

-- the same time in a different room: accepted
INSERT INTO schedules (room_number, duration)
VALUES ('32B', '[2020-08-20T10:00:00Z,2020-08-20T11:00:00Z)');

-- a half-hour overlap for an already-scheduled room: rejected
INSERT INTO schedules (room_number, duration)
VALUES ('32A', '[2020-08-20T10:30:00Z,2020-08-20T11:30:00Z)');

Upsert operations such as PostgreSQL's ON CONFLICT clause or MySQL's ON DUPLICATE KEY UPDATE use a table-level constraint to detect conflicts. And like non-null constraints can be expressed as CHECK constraints, a unique constraint can be expressed as an exclusion constraint on equality.

The primary key

Unique constraints have a particularly useful special case. With an additional non-null constraint on the unique column or columns, each record in the table can be singularly identified by its values for the constrained columns, which are collectively termed a key. Multiple candidate keys can coexist in a table, such as users still sometimes having distinct unique and non-null emails and usernames; but declaring a primary key establishes a single criterion by which records are publicly and exclusively known. Some RDBMSs even organize rows on pages by the primary key, called for this purpose a clustered index, to make searching by primary key values as fast as possible.

There are two types of primary key. A natural key is defined on a column or columns "naturally" included in the table's data, while a surrogate or synthetic key is invented solely for the purpose of becoming the key. Natural keys require care -- more things can change than database designers often credit, from names to numbering schemes. A lookup table containing country and region names can use their respective ISO 3166 codes as a safe natural primary key, but a users table with a natural key based on mutable values like names or email addresses invites trouble. When in doubt, create a surrogate key.

If a natural key spans multiple columns, a surrogate key should always at least be considered since multi-column keys take more effort to manage. If the natural key suits, however, columns should be ordered in increasing specificity just as they are in indexes: country code then region code, rather than the reverse.

The surrogate key has historically been a single integer column, or BIGINT where billions will eventually be assigned. Relational databases can automatically fill surrogate keys with the next integer in a series, a feature usually called SERIAL or IDENTITY.

An autoincrementing numeric counter is not without drawbacks: adding in records with pregenerated keys can cause conflicts, and if sequential values are exposed to users, it's easy for them to guess what other valid keys might be. Universally Unique Identifiers, or UUIDs, avoid these weaknesses and have become a common choice for surrogate keys, although they're also much bigger in-page than a simple number. The v1 (MAC address-based) and v4 (pseudorandom) UUID types are most frequently used.

On the database: foreign keys

Relational databases implement only one class of multi-table constraint, the "subset requirement" or foreign key. This sole constraint type is the guarantor of referential integrity, the principle that protects against inconsistencies between tables and distinguishes a relational database from a spreadsheet.

This informal "entity-relationship diagram" or ERD shows the beginnings of a schema for a database of libraries and their collections and patrons. Each edge represents a relationship between the tables it connects. The | glyph indicates a single record on its side, while the "crow's foot" glyph represents multiple: a library holds many books and has many patrons.

A foreign key constrains columns to reference an eligible key in another table (or the same table). The referenced key is often the primary key, but in PostgreSQL it can also be a non-deferrable unique constraint or a non-partial unique index. The corresponding columns must have compatible types. In the schema above, the books table maintains a library_id foreign key to libraries, which hold books, and an author_id to authors, who write them. But what happens if a book is inserted with an author_id that doesn't exist in authors?

For example, a foreign key can reference a unique library code while the library's primary key is a separate identifier:

CREATE TABLE branch_libraries (id integer PRIMARY KEY, code text UNIQUE NOT NULL);
CREATE TABLE branch_books (id integer PRIMARY KEY, library_code text REFERENCES branch_libraries(code));
INSERT INTO branch_libraries VALUES (1, 'CENTRAL');
INSERT INTO branch_books VALUES (1, 'CENTRAL');

INSERT INTO branch_books VALUES (2, 'MISSING');

If the foreign key is not constrained -- i.e., it's just another column or columns -- a book can have an author who doesn't exist. This is a problem: if someone tries to follow the link between books and authors, they wind up nowhere. If authors.author_id is a serial integer, there's also the possibility that no-one notices until the spurious author_id is eventually assigned, and you wind up with a particular copy of Don Quixote attributed first to nobody known and then to Pierre Menard, with Miguel Cervantes nowhere to be found.

Constraining the foreign key can't prevent a book from being misattributed should the erroneous author_id point to an existing record in authors, so other checks and tests remain important. However, the set of extant foreign key values is almost always a tiny subset of the possible foreign key values, so foreign key constraints will catch and prevent most wrong values. With a foreign key constraint, the Quixote with a nonexistent author will be rejected instead of recorded.

For certain values of correct

There are many more ways in which data may be incorrect than addressed here. Constraints help, but even they are only so flexible; many common intra-table specifications, like a limit of two or higher on the number of times a value is allowed to appear in a column, can only be enforced with triggers.

But there are also ways in which the very structure of a table can lead to inconsistencies. To prevent these, we'll need to marshal both primary and foreign keys not just to define and validate but to normalize the relationships between tables. First, though, we've barely scratched the surface of how the relationships between tables define the structure of the database itself.

FAQ