Subject: How to use a cross column exclude constraint

From: [email-masked]
Date: Sun, 21 Aug 2022 21:45:17 +0200 (CEST)

Hi All,

Schema:
CREATE TABLE record (        id uuid primary key default ...,    ...);CREATE TABLE record_pointer (    id uuid primary key default ...,    record_a_id uuid not null references record (id),    record_b_id uuid not null references record (id),    ...);

I am trying to create an exclude constraint to make both record_a_id and record_b_id unique table wide, so that each row from "record" can ever be referenced once in "record_pointers". 

Eg. if I add a record_pointer row that has "this-is-random-uuid" as record_a_id, the value "this-is-random-uuid" can never be in record_a_id or record_b_id in any other row.

Thank you in advance!

---

From: chidamparam muthusamy <[email-masked]>
Date: Tue, 23 Aug 2022 20:17:46 +0530

Refer https://www.postgresql.org/docs/current/ddl-constraints.html

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

In the above example, column 'discounted_price'  value is checked that it
should be less than the column value, 'price'.
Is it possible to add similar check condition for the columns,
'record_a_id' and 'record_b_id' that is
CHECK(record_a_id != record_b_id)
Regards,
Chidamparam

On Mon, Aug 22, 2022 at 1:15 AM <[email-masked]> wrote:

>
> Hi All,
>
> Schema:
>
> CREATE TABLE record (
>     id uuid primary key default ...,
>     ...
> );
>
> CREATE TABLE record_pointer (
>     id uuid primary key default ...,
>     record_a_id uuid not null references record (id),
>     record_b_id uuid not null references record (id),
>     ...
> );
>
>
> I am trying to create an exclude constraint to make both record_a_id and
> record_b_id unique table wide, so that each row from "record" can ever be
> referenced once in "record_pointers".
>
> Eg. if I add a record_pointer row that has "this-is-random-uuid" as
> record_a_id, the value "this-is-random-uuid" can never be in record_a_id or
> record_b_id in any other row.
>
>
> Thank you in advance!
>
>
>

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 23 Aug 2022 08:55:09 -0700

> CHECK(record_a_id != record_b_id)
The declarative constraints available do not allow for a "diagonal"
definition.  CHECK constraints are row-limited but can reference any
columns.  Exclusion constraints are column-oriented, the specified columns
are compared to the same columns in all other rows.

You cannot declare that a value in column b exists or does not exist in
column a on a different row.  You can write a trigger to that effect if
you'd like.

The absence of a declarative feature for this is because this model is
non-normalized and the features of SQL are generally designed to help
implement normalized data models.  You should consider whether you can
redesign things so that you can leverage the features present in the
language; and the efficiencies and robustness that such features tend to
have that custom trigger code may lack.

David J.

---

From: [email-masked]
Date: Wed, 24 Aug 2022 10:45:17 +0200 (CEST)

Hi,

Thank you for the insight!

How would you go about modeling my problem correctly?

The domain constraints are:
- there are many "records"
- there are 1:1 links between "records", and the links (table) contain a lot of information about the link (so adding a record_id (fk) to the records table would also add a ton of columns).
- the links are bidirectional
- each "record" can be linked with exactly one "record", so a record linking to another does not allow the record being referenced to be in any other link either.
- a graph of records and their connections (links) must be efficiently queried / formed

Best regards,

A

Aug 23, 2022, 15:55 by [email-masked]:

> On Tue, Aug 23, 2022 at 7:48 AM chidamparam muthusamy <> [email-masked]> > wrote:
>
>> Refer >> https://www.postgresql.org/docs/current/ddl-constraints.html
>>
>> CREATE TABLE products (    product_no integer,    name text,    price numeric CHECK (price > 0),    discounted_price numeric CHECK (discounted_price > 0),    >> CHECK (price > discounted_price)>> );
>> In the above example, column 'discounted_price'  value is checked that it should be less than the column value, 'price'.
>> Is it possible to add similar check condition for the columns, 'record_a_id' and 'record_b_id' that is
> The declarative constraints available do not allow for a "diagonal" definition.  CHECK constraints are row-limited but can reference any columns.  Exclusion constraints are column-oriented, the specified columns are compared to the same columns in all other rows.
>
> You cannot declare that a value in column b exists or does not exist in column a on a different row.  You can write a trigger to that effect if you'd like.
>
> The absence of a declarative feature for this is because this model is non-normalized and the features of SQL are generally designed to help implement normalized data models.  You should consider whether you can redesign things so that you can leverage the features present in the language; and the efficiencies and robustness that such features tend to have that custom trigger code may lack.
>
> David J.
>
>

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 24 Aug 2022 06:47:44 -0700

On Wednesday, August 24, 2022, <[email-masked]> wrote:

> - there are 1:1 links between "records", and the links (table) contain a
> lot of information about the link (so adding a record_id (fk) to the
> records table would also add a ton of columns).
> - each "record" can be linked with exactly one "record", so a record
> linking to another does not allow the record being referenced to be in any
> other link either.

Node: [node_id PK, edge_id {FK edge.edge_id}, {Unique: node_id, edge_id)]
Edge: [edge_id PK, …]
Node-Edge: [(node_id, edge_id) {FK node.node_id, node.edge_id}, slot {check
slot in (1,2); not null}, {PK: (edge_id, slot)}, {Unique: node_id}]

That doesn’t enforce “not zero” or missing records, which is possible but
generally a pain, but does enforce that a node may have at most one edge,
and each edge has at most two nodes.

With a deferred not null constraint on node.esge_id I think you can solve
prevent missing links problem, assuming you always add nodes in pairs.
You’d do so ething similar with edge.edge_id if you wanted to avoid
dangling edges (edges without nodes).

David J.

---

From: [email-masked]
Date: Thu, 25 Aug 2022 13:47:35 +0200 (CEST)

Hi,

What does this part of your annotation mean: "slot {check slot in (1,2); not null}, {PK: (edge_id, slot)}, {Unique: node_id}]"? The whole Node-Edge part is a bit fuzzy for me.

I have written the following schema:

-- node
CREATE TABLE api_endpoints (
  id UUID PRIMARY KEY,
  api_endoint_edge_id UUID REFERENCES api_endpoint_edges (id),
  UNIQUE (id, api_endpoint_edge_id), -- did I interpret this correctly?
  ...   
);

-- edge
CREATE TABLE api_endpoint_edges (
  id UUID PRIMARY KEY,
  ...  -- ton of data 
);

-- node-edge, how should i name this table? is just dropping the pluralization readable?
CREATE TABLE api_endpoint_edge (
  id UUID PRIMARY KEY,
  api_endoint_id UUID REFERENCES api_endpoints (id),
  api_endoint_edge_id UUID PRIMARY KEY REFERENCES api_endpoints (id),
  -- what is slot?
);

Thank you for helping me get started with Postgres!

>
> Aug 24, 2022, 13:47 by [email-masked]:
>
>> On Wednesday, August 24, 2022,  <>> [email-masked]>> > wrote:
>>
>>> Hi,
>>>
>> Node-Edge: [(node_id, edge_id) {FK node.node_id, node.edge_id}, slot {check slot in (1,2); not null}, {PK: (edge_id, slot)}, {Unique: node_id}]
>>
>> That doesn’t enforce “not zero” or missing records, which is possible but generally a pain, but does enforce that a node may have at most one edge, and each edge has at most two nodes.
>>
>> With a deferred not null constraint on node.esge_id I think you can solve prevent missing links problem, assuming you always add nodes in pairs.  You’d do so ething similar with edge.edge_id if you wanted to avoid dangling edges (edges without nodes).
>>
>> David J.
>>
>>
>
>

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 25 Aug 2022 17:28:22 -0700

The convention on these lists is to inline/trim or bottom/trim post your
replies like I did below and previously:

On Thu, Aug 25, 2022 at 4:47 AM <[email-masked]> wrote:

>
> What does this part of your annotation mean: "slot {check slot in (1,2);
> not null}, {PK: (edge_id, slot)}, {Unique: node_id}]"? The whole Node-Edge
> part is a bit fuzzy for me.
>
>
Slot is there to ensure that no more than 2 nodes can be attached to the
same edge.  Since each slot requires a number, the numbers must be unique,
and only the numbers 1 and 2 are available, that is accomplished.

>   UNIQUE (id, api_endpoint_edge_id), -- did I interpret this correctly?

Yes, while redundant since id is already unique it is required for the
foreign key to work.

>   api_endoint_edge_id UUID PRIMARY KEY REFERENCES api_endpoints (id),
IMO table names should not be plural - tables are also types and types are
named singular (e.g., integer).  Joining tables usually just combine the
names of the tables they join.

The joining table isn't technically required, you could put slot and those
constraints on the node table as well.

You don't get to have two primary keys on a table, and api_edpoint_edge_id
isn't unique anyway.  The combination of edge_id and node_id is unique.
The id field (and I abhor using "id" for a column name) doesn't really do
much here, there are not going to be external joins to it.

CREATE TABLE api_endpoint (
  api_endpoint_id uuid primary key,
  api_endpoint_edge_id uuid references
(api_endpoint_edge.api_endpoint_edge_id),
  unique (api_endpoint_edge_id, api_endpoint_id) -- this goes away if slot
moves here; with a modified check constraint probably...
  -- edge first to make the index more generally useful for searching for
edges, and api_endpoint_id is already first in the PK index
);

CREATE TABLE api_endpoint_edge (
  api_endpoint_edge_id uuid primary key
);

CREATE TABLE api_endpoint_edge (
  api_endpoint_id uuid not null primary key,
  api_endpoint_edge_id uuid not null,

  foreign key (api_endpoint_id, api_endpoint_edge_id)
    references api_endpoint (api_endpoint_id, api_endpoint_edge_id),

  -- you can place these on api_endpoint and get rid of this table
  slot integer check (slot IN (1,2)) not null,
  unique(api_endpoint_edge_id, slot)
);

David J.