SQL unique constraint of a max of two entries per user

Question

Is it possible to generate a unique constraint in SQL that will allow a single user (user_id) up to two entries that are enabled (enabled)? An example is as follows

user_id  | enabled
------------------
123      | true
123      | true
123      | false
456      | true

The above would be valid, but adding another user_id = 123 and enabled = true would fail because there would be three entries. Additionally adding user_id = 123 and enabled = false would be valid because the table would still satisfy the rule.

No, a unique constraint cannot allow duplicates. You probably need a trigger for this. — Gordon Linoff, May 22 '20 at 00:16
Does this answer your question? [How to write a constraint concerning a max number of rows in postgresql?](https://stackoverflow.com/questions/1743439/how-to-write-a-constraint-concerning-a-max-number-of-rows-in-postgresql) — philipxy, May 22 '20 at 01:20
Before considering posting please read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. — philipxy, May 22 '20 at 01:20

Erwin Brandstetter · Answer 1 · 2020-05-22T01:05:43.317

You could make it work by adding another boolean column to the UNIQUE or PRIMARY KEY constraint (or UNIQUE index):

CREATE TABLE tbl (
   user_id int
 , enabled bool
 , enabled_first bool DEFAULT true
 , PRIMARY KEY (user_id, enabled, enabled_first)
);

enabled_first tags the first of each instance with true. I made it DEFAULT true to allow simple insert for the first enabled per user_id - without mentioning the added enabled_first. An explicit enabled_first = false is required to insert a second instance.

NULL values are excluded automatically by the PK constraint I used. Be aware that a simple UNIQUE constraint still allows NULL values, working around your desired constraint. You would have to define all three columns NOT NULL additionally. See:

Allow null in unique column

db<>fiddle here

Of course, now the two true / false values are different internally, and you need to adjust write operations. This may or may not be acceptable. May even be desirable.

Welcome side-effect: Since the minimum payload (actual data size) is 8 bytes per index tuple, and boolean occupies 1 byte without requiring alignment padding, the index is still the same minimum size as for just (user_id, enabled).
Similar for the table: the added boolean does not increase physical storage. (May not apply for tables with more columns.) See:

score 2 · Accepted Answer · answered May 22 '20 at 00:20

You cannot allow two values of "enabled". But here is a solution that comes close to what you want without using triggers. The idea is to encode the value as numbers and enforce uniqueness on two of the values:

create table t (
    user_id int,
    enabled_code int,
    is_enabled boolean as (enabled_code <> 0),
    check (enabled_code in (0, 1, 2))
);

create unique index unq_t_enabled_code_1
    on t(user_id, enabled_code)
    where enabled_code = 1;

create unique index unq_t_enabled_code_2
    on t(user_id, enabled_code)
    where enabled_code = 2;

Inserting new values is a bit tricky, because you need to check if the value goes in slot "1" or "2". However, you can use is_enabled as the boolean value for querying.

score 1 · Answer 3 · answered May 22 '20 at 00:55

It has been explained already that a constraint or unique index only cannot enforce the logic that you want.

An alternative approach would be to use a materialized view. The logic is to use window functions to create an additional column in the view that resets every two rows having the same (user_id, enabled). You can then put a unique partial index on that column. Finally, you can create a trigger that refreshes the view everytime a record is inserted or updated, which effectively enforces the unique constraint.

-- table set-up
create table mytable(user_id int, enabled boolean);

-- materialized view set-up
create materialized view myview as 
select 
    user_id, 
    enabled, 
    (row_number() over(partition by user_id, enabled) - 1) % 2 rn 
from mytable;

-- unique partial index that enforces integrity
create unique index on myview(user_id, rn) where(enabled);

-- trigger code
create or replace function refresh_myview()
returns trigger language plpgsql
as $$
begin
    refresh materialized view myview;
    return null;
end$$;

create trigger refresh_myview
after insert or update
on mytable for each row
execute procedure refresh_myview();

With this set-up in place, let's insert the initial content:

insert into mytable values
    (123, true),
    (123, true),
    (234, false),
    (234, true);

This works, and the content of the view is now:

user_id | enabled | rn
------: | :------ | -:
    123 | t       |  0
    123 | t       |  1
    234 | f       |  0
    234 | t       |  0

Now if we try to insert a row that violates the constraint, an error is raised, and the insert is rejected.

insert into mytable values(123, true);
-- ERROR:  could not create unique index "myview_user_id_rn_idx"
-- DETAIL:  Key (user_id, rn)=(123, 0) is duplicated.
-- CONTEXT:  SQL statement "refresh materialized view myview"
-- PL/pgSQL function refresh_myview() line 3 at SQL statement

Demo on DB Fiddle

SQL unique constraint of a max of two entries per user

3 Answers3