Question :
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!
Answer :
Yes, you can create a unique index for that:
create unique index on foo ( least(bar1,bar2), greatest(bar1,bar2) );