Postgres unique constraint on groups of rows

Question

I'm using postgresql 10.12

I have labeled entities. Some are standard, some are not. Standard entities are shared among all users, whereas not standard entities are user owned. So let's say I have a table Entity with a text column Label, and a column user_id which is null for standard entities.

CREATE TABLE Entity
(
  id uuid NOT NULL PRIMARY KEY,
  user_id integer,
  label text NOT NULL,
)

Here is my constraint : two not standard entities belonging to different users can have the same label. Standard entities labels are unique, and entities of a given users have unique labels. The hard part is: a label must be unique within a group of standard entities + a given user's entities.

I'm using sqlAlchemy, here is the constraints I've made so far:

__table_args__ = (
    UniqueConstraint("label", "user_id", name="_entity_label_user_uc"),
    db.Index(
        "_entity_standard_label_uc",
        label,
        user_id.is_(None),
        unique=True,
        postgresql_where=(user_id.is_(None)),
    ),
)

My problem with this constraint is that I do not guarantee that a user entity won't have a standard entity label.

Example:

+----+---------+------------+
| id | user_id |   label    |
+----+---------+------------+
|  1 | null    | std_ent    |
|  2 | 42      | user_ent_1 |
|  3 | 42      | user_ent_2 |
|  4 | 43      | user_ent_1 |
+----+---------+------------+

This is a valid table. I want to make sure that it is not possible anymore to create an entity with label std_ent, that user 42 cannot create another entity with label user_ent_1 or user_ent_2 and that user 43 cannot create another entity with label user_ent_1.

With my current constraints, it is still possible for users 42 and 43 to create an entity with label std_ent, which is what I want to fix.

Any idea?

user_2144390 · Answer

If your unique constraint(s) are doing their job of preventing users from entering duplicate labels for their own "user entities" then you can prevent them from entering the label of a "standard entity" by adding a trigger.

You create a function …

CREATE OR REPLACE FUNCTION public.std_label_check()
    RETURNS trigger
    LANGUAGE plpgsql
AS $function$
    begin
        if exists(
                select * from entity 
                where label = new.label and user_id is null) then
            raise exception '"%" is already a standard entity', new.label;
        end if;
        return new;
    end;
$function$
;

… and then attach it as a trigger to the table

CREATE TRIGGER entity_std_label_check
BEFORE INSERT 
ON public.entity FOR EACH ROW
EXECUTE PROCEDURE std_label_check()

Postgres unique constraint on groups of rows

1 Answers