How to create a constraint that ensures a column has continuous values

Question

I have this model for photos:

CREATE TABLE photo (
    id serial PRIMARY KEY,
    name text NOT NULL,
    ordinal smallint NOT NULL CHECK (ordinal > 0),
    album integer NOT NULL,
    UNIQUE(name, ordinal, album) DEFERRABLE INITIALLY IMMEDIATE
)

And some sample data

id  name    ordinal  album
1   a.jpg   1        1
2   b.jpg   2        1
3   c.jpg   1        2
4   d.jpg   2        2

where photos are contained inside albums. Within each album, photos are ordered.

I want to create a constraint that ensures the ordinal column always have continuous values. For example,

id  name    ordinal  album
5   e.jpg   4        1

should be rejected, because there isn't a photo in album 1 with ordinal 3.

Notice that I made the unique constraint deferrable, because I'd like to allow reordering photos in a album.

I'd like to make the continuous deferrable also, because I might insert multiple photos, so as long as the insertions happen in the same transaction, insert a photo with ordinal 4 and then again with ordinal 3 shouldn't trigger an error.

How should I write this constraint?

I tried to define a function that returns boolean and use that in a check constraint, hoping to query the table to check if the ordinal are continuous for album that equals to that in the current row (using SQL like this) . But the reference from postgresql says

Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row.

So this seems like a dead end and I have no idea how to proceed.

Any thoughts?

Hannes Erven Hannes Erven · Accepted Answer · 2017-07-19T21:24:56

You can use a trigger function that will execute during commit of the transaction.

See here: https://stackoverflow.com/a/37277421/1980653

Depening on the number of rows in the table, it might be advisable to actually use two triggers:

a regular row-based trigger that writes the IDs of the touched records to a temporary table
a deferred on-commit trigger that with the help of the temporary table checks all updated records at once. The trigger will still be invoked once for every updated record, so you should also record whether the checks have run or not.

How to create a constraint that ensures a column has continuous values

1 Answers