public inbox for [email protected]  

help / color / mirror / Atom feed

From: Rhys A.D. Stewart <[email protected]>
To: [email protected]
Subject: exclusion constraint question
Date: Sat, 8 Mar 2025 14:01:04 -0500
Message-ID: <CACg0vTnm2cxCmmkMutEL+=4hac0MnVgWykYFVndO8WV_WQMN8Q@mail.gmail.com> (raw)

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

view thread (5+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: exclusion constraint question
  In-Reply-To: <CACg0vTnm2cxCmmkMutEL+=4hac0MnVgWykYFVndO8WV_WQMN8Q@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox