Adding unique constraint for self-referencing association table in PostgreSQL

Question

SQLFiddle

I have a table of ingredients and a self-referencing association table that links ingredients to each other.

I want to add a constraint that ensures each association between 2 ingredients can only exist once. If "salt" has been associated with "pepper" I want to prevent another row in the association table associating "pepper" to "salt".

I know I can use a trigger but because that's more complex I'd prefer to use a CONSTRAINT.

(Using PostgreSQL 14+)

• a: Is this possible using a CONSTRAINT?
• b: If not is a trigger the most straightforward solution or is there a better way?

Answer 1

One option uses a unique index on the greatest/least values of the tuple of columns:

create unique index ingredient_to_ingredient_idx 
on ingredient_to_ingredient( 
    greatest(ingredient_a_id, ingredient_b_id),
    least(ingredient_a_id, ingredient_b_id)
);

Demo on DB Fiddle:

-- I want this to produce a constraint error
insert into ingredient_to_ingredient (ingredient_a_id, ingredient_b_id) values (2, 1); 

-- ERROR:  duplicate key value violates unique constraint "ingredient_to_ingredient_idx"
-- DETAIL:  Key (GREATEST(ingredient_a_id, ingredient_b_id), LEAST(ingredient_a_id, ingredient_b_id))=(2, 1) already exists.

---

An alternative is an exclusion constraint:

alter table ingredient_to_ingredient
add constraint ingredient_to_ingredient_excl exclude using gist(
        least(ingredient_a_id, ingredient_b_id)    with =,
        greatest(ingredient_a_id, ingredient_b_id) with =
);

Which would yield the following error for the faulty insert statement:

-- ERROR:  conflicting key value violates exclusion constraint "ingredient_to_ingredient_excl"
-- DETAIL:  Key (LEAST(ingredient_a_id, ingredient_b_id), GREATEST(ingredient_a_id, ingredient_b_id))=(1, 2) conflicts with existing key (LEAST(ingredient_a_id, ingredient_b_id), GREATEST(ingredient_a_id, ingredient_b_id))=(1, 2).

Fiddle