Unique constraint across two rows in PostgreSQL

May 15, 2015
postgresql, tech
You can use a unique index to ensure only one occurance of a value but what do you do if you want to limit uniqueness to two rows instead of one? PostgreSQL's partial indexing can help.

I recently had a requirement where I needed an account to have zero, one or two actions associated with it. One could be a single action and the other could be one of many repeating types. I didn’t want two single actions and I didn’t want two or more types of repeating actions. To solve this I used two partial indexes to split the data set and apply a unique constraint to each set.

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE accounts (
  id   integer NOT NULL,
  name text    NOT NULL
);

CREATE TABLE actions (
  id          integer NOT NULL,
  account_id  integer NOT NULL,
  repeat_type text    NOT NULL DEFAULT 'none'
);

INSERT INTO accounts (id, name) VALUES (1, 'Test 1'), (2, 'Test 2');

If I create a unique index on actions(account_id) then I will only be able to have a single action per account.

1
2
3
4
5
6
7
8
9
CREATE UNIQUE INDEX idx_unique_accounts ON actions(account_id);

INSERT INTO actions (id, account_id, repeat_type) VALUES (1, 1, 'none');
-- INSERT 0 1
INSERT INTO actions (id, account_id, repeat_type) VALUES (1, 1, 'weekly');
-- ERROR:  duplicate key value violates unique constraint "idx_unique_accounts"
-- DETAIL:  Key (account_id)=(1) already exists.

DROP INDEX idx_unique_accounts;

The solution is to create two partial indexes, one for the single action and one for the repeating action.

1
2
3
4
5
6
7
8
TRUNCATE TABLE actions;
CREATE UNIQUE INDEX idx_unique_single_actions    ON actions(account_id) WHERE (repeat_type = 'none');
CREATE UNIQUE INDEX idx_unique_repeating_actions ON actions(account_id) WHERE (repeat_type != 'none');

INSERT INTO actions (id, account_id, repeat_type) VALUES (1, 1, 'none');
-- INSERT 0 1
INSERT INTO actions (id, account_id, repeat_type) VALUES (1, 1, 'weekly');
-- INSERT 0 1

Now inserting another single action will result in an error.

1
2
3
INSERT INTO actions (id, account_id, repeat_type) VALUES (1, 1, 'none');
-- ERROR:  duplicate key value violates unique constraint "idx_unique_single_actions"
-- DETAIL:  Key (account_id)=(1) already exists.

Or inserting another repeating action, even of a different repeat type, will result in an error.

1
2
3
INSERT INTO actions (id, account_id, repeat_type) VALUES (1, 1, 'monthly');
-- ERROR:  duplicate key value violates unique constraint "idx_unique_repeating_actions"
-- DETAIL:  Key (account_id)=(1) already exists.
comments powered by Disqus