2

What I want to achieve is the following:

CREATE TABLE foo (
    bar1 int,
    bar2 int,
    type varchar(16),
    FOREIGN KEY bar1 REFERENCES bar(id),
    FOREIGN KEY bar2 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!

3

Yes, you can create a unique index for that:

create unique index on foo ( least(bar1,bar2), greatest(bar1,bar2) );
  • Thanks, you do need the least, greatest functions! – Hodossy Szabolcs Feb 20 at 7:44
  • 1
    @HodossySzabolcs Note that this solution does not prohibit bar1=bar2. – Akina Feb 20 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 at 8:01
  • Of course, maybe it is allowed. But most importantly, the question author is warned if he forgot... – Akina Feb 20 at 8:07
  • Sure, it would not happen, but good to be reminded of it! Thanks @Akina – Hodossy Szabolcs Feb 20 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.