Question 217880 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql: in a link table check a condition on inserted values among related table

I have a schema like this one:

create table users (

  id bigint primary key generated always as identity,
  name text not null

);

create table feature (

  id bigint primary key generated always as identity,
  value text not null check (value != '')

);

create unique index ux1 on feature(value);

-- link table
create table user_feature (

  id bigint generated always as identity,
  user_id bigint not null references users(id),
  feature_id bigint not null references feature(id)
);

create unique index ux2 on user_feature(user_id, feature_id);

-- feature values
insert into feature (value) values ('A'), ('B'), ('C'), ('D');

So an user can have 0 or more features, he can have just one type of same feature but I would like add some logic/conditions between the features that an user can have.

For example let’s say feature B and C are incompatibles, so user can have none of them, B or C but not both, not B and C together.

What is, if there is, a proper way to do this ?

I was reading and if am not wrong the only option is a trigger right (on insert / update)?
I am not used to triggers, if someone can at least gives an example.

I am using version 14.

Thanks for the help.

Tags:

2

Answers


  1. 0

    Yes you definitely need to enforce such behavior with triggers. Here is some example code for checking that a user cannot have feature B and C:

    CREATE OR REPLACE FUNCTION check_feature_compatibility() RETURNS     TRIGGER AS $$
BEGIN
-- Check for conflicting features B and C
IF NEW.feature_id IN (SELECT id FROM feature WHERE value IN ('B', 'C')) THEN
    IF EXISTS (SELECT 1 FROM user_feature WHERE user_id = NEW.user_id 
           AND feature_id IN (SELECT id FROM feature WHERE value IN ('B', 'C'))
           AND id != NEW.id) THEN
               RAISE EXCEPTION 'User cannot have both feature B and C';
   END IF;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER user_feature_check BEFORE INSERT OR UPDATE ON user_feature
FOR EACH ROW EXECUTE FUNCTION check_feature_compatibility();

    Behaviour:

    When updating a user with this data:

    user-dataerror

    Login or Signup to reply.
  2. 0

    I would avoid triggers. If two users insert values B and C in separate transactions, your trigger won’t catch it since each session will see only the value it inserted.

    You can use a unique index with a case expression instead:

    create unique index u3 on user_feature(user_id, 
                                       (case when feature_id in (2 /* B */, 3 /* C */) then 1 
                                             else null 
                                        end));

    This unique index

    • ignores any feature that is not B or C (provided that B has ID 2 and C has ID 3)
    • prevents insertion of B and C for the same user (since they are mapped to the same value in the CASE expression

    However, this approach is somewhat limited:

    • your features need to adhere to an equivalence relation, i.e. if B and C are disallowed and C and D are disallowed, then B and D must be disallowed, as well
    • your primary keys must be know at constraint creation time (I’d forego the generated as identity in that case and provide the PK values explicitly)

    SQL Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search