Postgresql unique constraint with pair of columns

user2439427 0
Viewed 27

I have a table where i would like to add a unique constraint on pair of columns.

DummyTable 
    Id    name1    type1         name2    type2 
    1     hello    firstname     world    lastname

I need to add a unique constraint with the combination of the 4 columns like

add constraint name_type_unique
unique ((name1, type1), (name2, type2));

Which means name and type should be unique either way around. It should not be possible to have values like:

Id    name1    type1         name2    type2 
1     hello    firstname     world    lastname
2     world    lastname      hello    firstname

Is that somehow possible?

postgresql
1 Answers

I don't know of a native solution, and I can't promise efficiency, but have you considered a trigger?

create function dummy_table_insert_trigger()
returns trigger
as
$BODY$
DECLARE
  existing integer;
BEGIN
    select max (id)
    into existing
    from dummy_table t
    where
     (t.name1 = NEW.name2 and t.type1 = NEW.type2) or
     (t.name2 = NEW.name1 and t.type2 = NEW.type1);
   
   if existing is not null then
     raise exception 'Conflicting record id % exists', existing;
   end if;
   
   return NEW;
END;
$BODY$
language plpgsql;

create trigger dummy_table_trigger
before insert on dummy_table
for each row
execute function dummy_table_insert_trigger()
Hambone0
Related