On Sat, 2025-03-08 at 14:01 -0500, Rhys A.D. Stewart wrote: > 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. I believe that this can only be solved by keeping a tally of the used mugs in a second table that is maintained by a trigger. Yours, Laurenz Albe
