What I want to achieve is the following:

    bar1 int,
    bar2 int,
    type varchar(16),
    FOREIGN KEY bar1 REFERENCES bar(id),

ALTER TABLE foo ADD CONSTRAINT unique_link (bar1, bar2);

INSERT INTO foo (bar1, bar2, type) VALUES (1, 2, 'simple link')
--next line should fail, as {2, 1} as a set is already present, just in a different order
INSERT INTO foo (bar1, bar2, type) VALUES (2, 1, 'simple link')

Is it possible in any way? Maybe with some triggers maybe, or some auto calculated hashes of the bar* columns? Thanks for the help!


Yes, you can create a unique index for that:

create unique index on foo ( least(bar1,bar2), greatest(bar1,bar2) );
| improve this answer | |
  • Thanks, you do need the least, greatest functions! – Hodossy Szabolcs Feb 20 '19 at 7:44
  • 1
    @HodossySzabolcs Note that this solution does not prohibit bar1=bar2. – Akina Feb 20 '19 at 7:58
  • @Akina: that is true. But that can be achieved with a simple check constraint - although I didn't see anything rin the question that would suggest that this is needed – a_horse_with_no_name Feb 20 '19 at 8:01
  • Of course, maybe it is allowed. But most importantly, the question author is warned if he forgot... – Akina Feb 20 '19 at 8:07
  • Sure, it would not happen, but good to be reminded of it! Thanks @Akina – Hodossy Szabolcs Feb 20 '19 at 9:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.