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.
First i'd say look at the exclusion constraint and your unique keys. Those are defined in a mutually-auto-canceling manner. e..g
insert mugs 10, 11, and 12 in shelf 3 :
insert into
shelves(shelf_id,l_mug_id,c_mug_id,r_mug_id)
values(3,10,11,12);
INSERT 0 1
then insert mugs 13,14,15 in
shelf 4 :
insert into
shelves(shelf_id,l_mug_id,c_mug_id,r_mug_id)
values(4,13,14,15);
ERROR: conflicting key value violates exclusion constraint
"shelves_l_mug_id_c_mug_id_r_mug_id_excl"
DETAIL: Key (l_mug_id, c_mug_id, r_mug_id)=(13, 14, 15)
conflicts with existing key (l_mug_id, c_mug_id, r_mug_id)=(10,
11, 12).
in order to bypass the exclusion constraint one of the mags must match a previous mug in the same position :
insert into shelves(shelf_id,l_mug_id,c_mug_id,r_mug_id) values(4,13,14,12);
but then :
ERROR:
duplicate key value violates unique constraint
"shelves_r_mug_id_key"
DETAIL: Key (r_mug_id)=(12) already exists.
So your exclusion constraint
does not do what you would expect.
You can think of your mugs positions by pulling the shelves buckets in order to make them look like they have a linear shape. Then you would have a unique key on this linear store. What Laurenz said is most probably correct.
hmm just thinking , would
imagining making 64*3 long integers with all three mug ids side
by side ,and using bitwise operations along with exclusion
constraints might yield some results ?
Any suggestions would be appreciated. Regards, Rhys Peace & Love | Live Long & Prosper
