26 Constraints, Indexes, and Triggers

26.1 Beyond Primary and Foreign Keys

The lesson on creating and manipulating tables introduced constraints — NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK — as the tools PostgreSQL gives you to keep bad data out of a table in the first place. Primary and foreign keys got most of the attention there, because they are the constraints every relational table needs. This lesson picks up where that one left off, rounding out CHECK constraints and composite primary keys, and then introduces two more advanced features that build on the same idea of letting the DBMS enforce rules instead of trusting every client application to do it correctly: indexes, which make your queries faster, and triggers, which let the database react automatically to changes in your data.

26.2 Enforcing Custom Rules with CHECK

Recall that a CHECK constraint defines a logical test that a column's value must pass before a row can be inserted or updated. Unlike NOT NULL or UNIQUE, which enforce one fixed rule, CHECK lets you write whatever rule your data actually needs.

Take the work table. It records the width and height of each work of art, but nothing so far stops a cataloguer from typing in a zero or a negative number by mistake. A CHECK constraint can close that gap:

ALTER TABLE work
ADD CONSTRAINT work_dimensions_positive
CHECK (width > 0 AND height > 0);

From now on, any INSERT or UPDATE on work that tries to set width or height to zero or less is rejected before it ever reaches the table. Try it:

UPDATE work
SET width = -10
WHERE work_id = 500;

This statement fails with a constraint violation error naming work_dimensions_positive, rather than silently storing an impossible measurement.

26.3 Composite Primary Keys

The lesson on creating and manipulating tables mentioned that a primary key can sometimes span more than one column — a composite primary key — and showed the generic syntax for one. The subject table in this book's own database is a good real example of when you'd need it.

Every other table in this book's database has had a primary key since it was first created, but subject has gone without one. Each row in subject records one subject depicted in a work, linked back to work by work_id. A single work can depict more than one subject — recall the transaction processing lesson, where a work named "Winter Market" was recorded with both "Marketplace" and "Winter Landscape" as subjects. That's exactly why subject was left without a single-column primary key in the first place: a plain primary key on work_id alone would refuse to let a second subject row reference the same work, which rules out work_id alone as the primary key. What actually makes each row of subject unique is the combination of work_id and subject, so now is a good time to add the primary key it's been missing:

ALTER TABLE subject
ADD CONSTRAINT subject_pkey
PRIMARY KEY (work_id, subject);

Now subject has the primary key every table should have, while still allowing as many rows per work_id as a work has subjects, and refusing to store the exact same work_id/subject pair twice.

26.4 Understanding Indexes

Every query you've written so far has worked correctly regardless of how many rows a table holds. But correctness and speed are two different things. Consider a query used earlier in this book:

SELECT full_name, nationality, style
FROM artist
WHERE nationality = 'French';

Without any help, PostgreSQL has to check the nationality value of every single row in artist to find the French ones — a sequential scan. On a table with a few hundred rows, that's instant. On a table with tens of millions, it's not.

You've actually already been relying on an index without knowing it: the lesson on creating and manipulating tables noted that PostgreSQL automatically builds an index on every primary key, which is why looking a row up by its primary key is fast even on a huge table. Indexes are that same idea, made available for any column (or combination of columns) you choose.

26.4.1 The Cost of Indexes

Indexes aren't free, which is why PostgreSQL doesn't simply index every column on every table automatically.

• An index takes up disk space of its own, separate from the table it indexes.
• Every INSERT, UPDATE, or DELETE on the table must also update every index defined on it, which makes writes slower.
• Too many indexes on a heavily written table can slow it down more than the indexes speed up reads.

26.4.2 Creating Indexes

Indexes are created with the CREATE INDEX statement:

CREATE INDEX indexname
ON tablename (column1, column2, ...);

Say the artist table has grown large, and queries filtering by nationality — like the one at the start of this section — have gotten noticeably slower. You can use a chat with an AI assistant to write the statement that adds an index:

Try the SQL statement.

CREATE INDEX idx_artist_nationality
ON artist (nationality);

You are not limited to a single column. A multi-column index is useful when queries regularly filter or join on more than one column together. The work table, for example, is frequently joined to both artist and museum at once:

CREATE INDEX idx_work_artist_museum
ON work (artist_id, museum_id);

Finally, CREATE UNIQUE INDEX builds an index that also enforces uniqueness, which is exactly what happens behind the scenes whenever you add a UNIQUE constraint. The two statements below are equivalent — pick whichever reads more clearly to you:

ALTER TABLE museum
ADD CONSTRAINT museum_name_unique UNIQUE (name);

CREATE UNIQUE INDEX idx_museum_name
ON museum (name);

26.4.3 Seeing Whether an Index Is Used

PostgreSQL lets you check whether a query actually uses an index with the EXPLAIN statement, which shows the query plan the DBMS intends to run, without running the query itself:

EXPLAIN SELECT full_name, nationality, style
FROM artist
WHERE nationality = 'French';

On a small table like this book's artist table, PostgreSQL will likely still choose a Seq Scan (sequential scan) even with the index in place — for a handful of rows, scanning the whole table is actually cheaper than consulting an index. On a table large enough for the index to pay off, the same EXPLAIN would instead report an Index Scan using idx_artist_nationality, meaning PostgreSQL used the index rather than reading every row.

26.4.4 Removing Indexes

An index that isn't earning its keep can be removed with DROP INDEX:

DROP INDEX idx_work_artist_museum;

26.5 Understanding Triggers

Constraints, including CHECK, can only validate a single row against a fixed rule at the moment it's written. Sometimes you need the database to actually do something in response to a change — run a calculation, copy a value somewhere else, log what changed — automatically, without depending on every application or user to remember to do it. That's what a trigger is for.

Triggers share some of the same motivation as stored procedures, discussed in an earlier lesson: they move logic out of client applications and into the database itself, so it's enforced consistently no matter what application, script, or person is writing to the table.

A PostgreSQL trigger is really two pieces working together:

• A trigger function — ordinary PL/pgSQL code, written much like the functions from the lesson on stored procedures, except that it's written specifically to run as a trigger and returns the special type TRIGGER rather than an ordinary value.
• A trigger definition, created with CREATE TRIGGER, which tells PostgreSQL which table to watch, which events to watch for (INSERT, UPDATE, DELETE, or some combination), and when to run the function relative to those events.

Two settings determine exactly when a trigger fires:

• Timing — BEFORE, AFTER, or INSTEAD OF. A BEFORE trigger runs before the triggering statement takes effect, and can inspect or even change the row before it's written. An AFTER trigger runs once the change has already happened, and is typically used to react to it — for example, writing to a separate log table. INSTEAD OF triggers are a special case used on views; the lesson on views mentioned that a complex view is read-only unless you define one of these to tell PostgreSQL how to translate a write against the view into real changes on its underlying tables.
• Level — FOR EACH ROW, which runs the trigger once per affected row, or FOR EACH STATEMENT, which runs it once for the whole statement regardless of how many rows it touches. Every example in this lesson uses FOR EACH ROW, by far the more common choice.

Inside a row-level trigger function, PostgreSQL makes the row being written available through two special record variables: NEW, the row's value after the change (available in INSERT and UPDATE triggers), and OLD, the row's value before the change (available in UPDATE and DELETE triggers).

26.5.1 A BEFORE Trigger: Stamping the Time of the Last Update

Suppose you want to know, at a glance, when each museum's record was last changed. First, add a column to hold that timestamp:

ALTER TABLE museum
ADD COLUMN last_updated TIMESTAMP;

You could rely on every UPDATE statement to set last_updated = CURRENT_TIMESTAMP by hand, but that depends on nobody ever forgetting — exactly the kind of client-side rule this lesson opened by warning against. A trigger enforces it instead. First, the trigger function:

CREATE FUNCTION set_museum_last_updated()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.last_updated = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$;

Then the trigger definition that attaches this function to museum:

CREATE TRIGGER museum_last_updated
BEFORE UPDATE ON museum
FOR EACH ROW
EXECUTE FUNCTION set_museum_last_updated();

Try the SQL statement.

ALTER TABLE museum
ADD COLUMN last_updated TIMESTAMP;

CREATE FUNCTION set_museum_last_updated()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.last_updated = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$;

CREATE TRIGGER museum_last_updated
BEFORE UPDATE ON museum
FOR EACH ROW
EXECUTE FUNCTION set_museum_last_updated();

The trigger fires BEFORE UPDATE, which matters here: because it runs before the row is actually written, the function can modify NEW directly, and that modified version — including the freshly stamped last_updated — is what actually gets saved. The function ends with RETURN NEW, handing that modified row back to PostgreSQL to write; a BEFORE trigger that instead returned NULL would silently cancel the write altogether.

Try updating a museum's phone number, without ever mentioning last_updated:

UPDATE museum
SET phone = '5551234567'
WHERE museum_id = 3;

Querying the row afterward shows last_updated set to the current timestamp anyway — the trigger set it automatically, the same way it will for every future update, from every application, with no chance of anyone forgetting.

26.5.2 An AFTER Trigger: Logging a Column's History

A BEFORE trigger reaches into a write that hasn't happened yet. An AFTER trigger is better suited to reacting to a write that already has — for instance, keeping a history of a column that shouldn't normally change but occasionally does, like a museum's url.

First, a table to hold that history:

CREATE TABLE museum_url_history (
    museum_id  INTEGER NOT NULL REFERENCES museum (museum_id),
    old_url    VARCHAR(255),
    changed_at TIMESTAMP NOT NULL
);

Then a trigger function that records the old url whenever it's about to be overwritten:

CREATE FUNCTION log_museum_url_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO museum_url_history (museum_id, old_url, changed_at)
    VALUES (OLD.museum_id, OLD.url, CURRENT_TIMESTAMP);
    RETURN NULL;
END;
$$;

And the trigger definition:

CREATE TRIGGER museum_url_change
AFTER UPDATE OF url ON museum
FOR EACH ROW
WHEN (OLD.url IS DISTINCT FROM NEW.url)
EXECUTE FUNCTION log_museum_url_change();

Try the SQL statement.

CREATE TABLE museum_url_history (
    museum_id  INTEGER NOT NULL REFERENCES museum (museum_id),
    old_url    VARCHAR(255),
    changed_at TIMESTAMP NOT NULL
);

CREATE FUNCTION log_museum_url_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO museum_url_history (museum_id, old_url, changed_at)
    VALUES (OLD.museum_id, OLD.url, CURRENT_TIMESTAMP);
    RETURN NULL;
END;
$$;

CREATE TRIGGER museum_url_change
AFTER UPDATE OF url ON museum
FOR EACH ROW
WHEN (OLD.url IS DISTINCT FROM NEW.url)
EXECUTE FUNCTION log_museum_url_change();

A few details are worth calling out:

• AFTER UPDATE OF url restricts the trigger to updates that touch the url column specifically — an update that only changes phone never fires it.
• The WHEN clause is an extra filter evaluated before the function is even called. OLD.url IS DISTINCT FROM NEW.url is true only when the url's value has actually changed (IS DISTINCT FROM compares two values for inequality the same way <> does, except that it also correctly treats NULL as different from any non-NULL value, and as equal to another NULL, rather than returning an unknown result). Together, these two restrictions mean a statement like UPDATE museum SET url = url WHERE ... — which touches the url column but doesn't change its value — won't log anything.
• Because this trigger fires AFTER the row has already been written, there's nothing left for it to change, so it ends with RETURN NULL rather than RETURN NEW. For an AFTER trigger, PostgreSQL ignores whatever the function returns either way; NULL is simply the conventional choice.

Now try changing a museum's url:

UPDATE museum
SET url = 'https://newsite.example.org'
WHERE museum_id = 3;

SELECT * FROM museum_url_history;

The second query shows a new row recording the museum's previous url and the moment it changed — a permanent history that required no extra work from whatever application or person happened to run the UPDATE.

26.5.3 Removing Triggers

A trigger is removed with DROP TRIGGER, naming both the trigger and the table it's attached to (trigger names only need to be unique within a single table, so PostgreSQL needs both to find the right one):

DROP TRIGGER museum_url_change ON museum;

Dropping a trigger does not remove its trigger function — the two are separate objects. To remove the function as well:

DROP FUNCTION log_museum_url_change();

26.6 Summary

In this lesson, you extended what you already knew about constraints with CHECK, used to enforce custom rules a NOT NULL or UNIQUE constraint can't express, and with composite primary keys, used when no single column can uniquely identify a row on its own. You then learned about indexes: separate structures the DBMS maintains to speed up lookups on columns you frequently filter, join, or sort on, at the cost of extra storage and slower writes, created with CREATE INDEX and removed with DROP INDEX. Finally, you learned about triggers: PL/pgSQL functions attached to a table with CREATE TRIGGER that run automatically before or after an INSERT, UPDATE, or DELETE, letting the database react to changes on its own rather than depending on every application to remember to do so. Constraints, indexes, and triggers are each substantial topics in their own right, and this lesson has only introduced them — refer to the PostgreSQL documentation for the fuller picture.