Greetings All,
I have the following table:
CREATE TABLE shelves(
shelf_id bigint PRIMARY KEY,
l_mug_id bigint UNIQUE,
c_mug_id bigint UNIQUE,
r_mug_id bigint UNIQUE,
CHECK (l_mug_id <> c_mug_id AND l_mug_id <> r_mug_id AND c_mug_id
<> r_mug_id),
EXCLUDE USING gist (l_mug_id WITH <>, c_mug_id WITH <>, r_mug_id
WITH <>) -- Not working as expected (or my expectations are wrong).
);
And some data:
INSERT INTO shelves VALUES (1, 7, 2, 1);
INSERT INTO shelves VALUES (2, 3, null, null);
INSERT INTO shelves VALUES (3, null, 1, 4);
INSERT INTO shelves VALUES (4, 4, 5, null);
Mugs on shelves, fascinating. A mug_id can only appear once in the
entire table. The check constraint handles not having the same mug_id
in each row and the unique constraints does the same for the column.
But how do I get around checking multiple columns for the same mug_id.
I'm thinking an exclusion constraint, but (a) I do not know if I am
overthinking it and (b) the exclusion constraint I have does not work
as expected, or my expectations are way off.
Any suggestions would be appreciated.
Regards,
Rhys
Peace & Love | Live Long & Prosper
