I like my database constraint heavy, so there are less surprises.

Unique constraint

A wide used constraint is the unique constraint. This must be one of the simplest constraints. It makes sure that there are no rows with same values in the given fields. For example lets say we have a table where we store the data of different tournaments on different countries. We store the position, country and athlete.

CREATE TABLE tournament_positions (
    position integer,
    championship text,
    athlete text
);

Now we want to make sure that for each tournament there are no repeated positions. For this we can use a unique constraint such as:

ALTER TABLE tournament_positions
ADD CONSTRAINT uq_position_and_championship
UNIQUE (position, championship);

And we can see that the constraint exists on the table:

\d tournament_positions;

...
"uq_position_and_championship" UNIQUE CONSTRAINT, btree ("position", championship)
...

This seems like a great idea, now we are certain that no tournament will have two champions, great!

Lets insert some data for our local tournament

INSERT INTO tournament_positions("position", championship, athlete) VALUES (1, 'local', 'John');
INSERT INTO tournament_positions("position", championship, athlete) VALUES (2, 'local', 'Max');

Problems begin

This was a very tight end and after a revision, it seems that Max actually finished earlier, so we need to update our data. Easy peasy just run a couple of update commands:

UPDATE tournament_positions SET position = 2 WHERE athlete = 'John';

Which will result in

ERROR:  duplicate key value violates unique constraint "uq_position_and_championship"
DETAIL:  Key ("position", championship)=(2, local) already exists.

Great our unique constraint is working!

But we really need to update the data. Of course lets use a transaction, so we can run both updates on a transaction:

BEGIN;
UPDATE tournament_positions SET position = 2 WHERE athlete = 'John';
UPDATE tournament_positions SET position = 1 WHERE athlete = 'Max';
COMMIT;

Which ends in pretty much the same error.

Giving it some thought we could use a temporal position, we could set John position to 99, then Max to 1 and John to 2. Thats seems possible, but it will not be an atomic transaction. We should certainly avoid this.

We could of course delete the constraint, make our changes and put it back in. This would rather be quick and fast since there only two entries, creating the constraint again would be fast. However, on the meantime anything could happen, what if there is a new insert?

If we could just disable the constraint for a transaction. After all, the data at the end of the transaction will be on the correct state.

Am I the first person running into this problem? certainly somebody else has found this issue before.

Enter deferred constraints

Deferred constraints can be set to be checked at the end of the transaction, rather than when running the insert/update commands. At the time of writing this only unique, primary key, foreign key and exclude constraints can be deferrable.

Deferrable constraints can be checked at the end of the transaction or when running the update/insert command. This behavior can be modified when starting the transaction. However, a default behavior can be set when defining the deferrable constraint, the two initial options are:

• Initially deferred
• Initially immediate

Deferrable constraints at work

My first attempt was to update the existing constraint to be deferrable:

ALTER TABLE "tournament_positions"
ALTER CONSTRAINT "uq_position_and_championship" DEFERRABLE;

However, this shows the following error:

ERROR:  constraint "uq_position_and_championship" of relation "tournament_positions" is not a foreign key constraint

So plan B, drop constraint and create it again as deferrable.

-- Drop constraint
ALTER TABLE tournament_positions
DROP CONSTRAINT uq_position_and_championship;
-- Create deferred constraint
ALTER TABLE tournament_positions
ADD CONSTRAINT uq_position_and_championship
UNIQUE (position, championship) DEFERRABLE INITIALLY DEFERRED;

Now you can see that the constraint definition has changed

\d+ tournament_positions

...
"uq_position_and_championship" UNIQUE CONSTRAINT, btree ("position", championship) DEFERRABLE INITIALLY DEFERRED
...

Run transaction

BEGIN;
SET CONSTRAINTS "uq_position_and_championship";
UPDATE tournament_positions SET position = 2 WHERE athlete = 'John';
UPDATE tournament_positions SET position = 1 WHERE athlete = 'Max';
COMMIT;

And we have the data on the desired state.

Note that the constraint is initially deferred, I could have obtained the same result by using an initially immediate constraint as follows:

-- Drop constraint
ALTER TABLE tournament_positions
DROP CONSTRAINT uq_position_and_championship;
-- Create deferred constraint
ALTER TABLE tournament_positions
ADD CONSTRAINT uq_position_and_championship
UNIQUE (position, championship) DEFERRABLE INITIALLY IMMEDIATE;
-- Run transaction
BEGIN;
SET CONSTRAINTS "uq_position_and_championship" DEFERRED;
UPDATE tournament_positions SET position = 1 WHERE athlete = 'John';
UPDATE tournament_positions SET position = 2 WHERE athlete = 'Max';
COMMIT;