public inbox for [email protected]
help / color / mirror / Atom feedHow to use a cross column exclude constraint
7+ messages / 3 participants
[nested] [flat]
* How to use a cross column exclude constraint
@ 2022-08-21 19:45 [email protected]
2022-08-23 14:47 ` Re: How to use a cross column exclude constraint chidamparam muthusamy <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: [email protected] @ 2022-08-21 19:45 UTC (permalink / raw)
To: Pgsql Novice <[email protected]>
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!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to use a cross column exclude constraint
2022-08-21 19:45 How to use a cross column exclude constraint [email protected]
@ 2022-08-23 14:47 ` chidamparam muthusamy <[email protected]>
2022-08-23 15:55 ` Re: How to use a cross column exclude constraint David G. Johnston <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: chidamparam muthusamy @ 2022-08-23 14:47 UTC (permalink / raw)
To: [email protected]; +Cc: Pgsql Novice <[email protected]>
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 protected]> 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!
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to use a cross column exclude constraint
2022-08-21 19:45 How to use a cross column exclude constraint [email protected]
2022-08-23 14:47 ` Re: How to use a cross column exclude constraint chidamparam muthusamy <[email protected]>
@ 2022-08-23 15:55 ` David G. Johnston <[email protected]>
2022-08-24 08:45 ` Re: How to use a cross column exclude constraint [email protected]
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2022-08-23 15:55 UTC (permalink / raw)
To: chidamparam muthusamy <[email protected]>; +Cc: [email protected]; Pgsql Novice <[email protected]>
On Tue, Aug 23, 2022 at 7:48 AM chidamparam muthusamy <
[email protected]> 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
> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to use a cross column exclude constraint
2022-08-21 19:45 How to use a cross column exclude constraint [email protected]
2022-08-23 14:47 ` Re: How to use a cross column exclude constraint chidamparam muthusamy <[email protected]>
2022-08-23 15:55 ` Re: How to use a cross column exclude constraint David G. Johnston <[email protected]>
@ 2022-08-24 08:45 ` [email protected]
2022-08-24 13:47 ` Re: How to use a cross column exclude constraint David G. Johnston <[email protected]>
2022-08-25 11:47 ` Re: How to use a cross column exclude constraint [email protected]
0 siblings, 2 replies; 7+ messages in thread
From: [email protected] @ 2022-08-24 08:45 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Pgsql Novice <[email protected]>; Mchidamparam <[email protected]>
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 protected]:
> On Tue, Aug 23, 2022 at 7:48 AM chidamparam muthusamy <> [email protected]> > 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
>> 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.
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to use a cross column exclude constraint
2022-08-21 19:45 How to use a cross column exclude constraint [email protected]
2022-08-23 14:47 ` Re: How to use a cross column exclude constraint chidamparam muthusamy <[email protected]>
2022-08-23 15:55 ` Re: How to use a cross column exclude constraint David G. Johnston <[email protected]>
2022-08-24 08:45 ` Re: How to use a cross column exclude constraint [email protected]
@ 2022-08-24 13:47 ` David G. Johnston <[email protected]>
1 sibling, 0 replies; 7+ messages in thread
From: David G. Johnston @ 2022-08-24 13:47 UTC (permalink / raw)
To: [email protected] <[email protected]>; +Cc: Pgsql Novice <[email protected]>; Mchidamparam <[email protected]>
On Wednesday, August 24, 2022, <[email protected]> wrote:
> 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
>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to use a cross column exclude constraint
2022-08-21 19:45 How to use a cross column exclude constraint [email protected]
2022-08-23 14:47 ` Re: How to use a cross column exclude constraint chidamparam muthusamy <[email protected]>
2022-08-23 15:55 ` Re: How to use a cross column exclude constraint David G. Johnston <[email protected]>
2022-08-24 08:45 ` Re: How to use a cross column exclude constraint [email protected]
@ 2022-08-25 11:47 ` [email protected]
2022-08-26 00:28 ` Re: How to use a cross column exclude constraint David G. Johnston <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: [email protected] @ 2022-08-25 11:47 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Pgsql Novice <[email protected]>
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 protected]:
>
>> On Wednesday, August 24, 2022, <>> [email protected]>> > wrote:
>>
>>> 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
>>>
>>
>> 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.
>>
>>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to use a cross column exclude constraint
2022-08-21 19:45 How to use a cross column exclude constraint [email protected]
2022-08-23 14:47 ` Re: How to use a cross column exclude constraint chidamparam muthusamy <[email protected]>
2022-08-23 15:55 ` Re: How to use a cross column exclude constraint David G. Johnston <[email protected]>
2022-08-24 08:45 ` Re: How to use a cross column exclude constraint [email protected]
2022-08-25 11:47 ` Re: How to use a cross column exclude constraint [email protected]
@ 2022-08-26 00:28 ` David G. Johnston <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: David G. Johnston @ 2022-08-26 00:28 UTC (permalink / raw)
To: [email protected]; +Cc: Pgsql Novice <[email protected]>
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 protected]> 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.
> 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?
> ...
> );
>
Yes, while redundant since id is already unique it is required for the
foreign key to work.
>
> -- 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?
> );
>
>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2022-08-26 00:28 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-08-21 19:45 How to use a cross column exclude constraint [email protected]
2022-08-23 14:47 ` chidamparam muthusamy <[email protected]>
2022-08-23 15:55 ` David G. Johnston <[email protected]>
2022-08-24 08:45 ` [email protected]
2022-08-24 13:47 ` David G. Johnston <[email protected]>
2022-08-25 11:47 ` [email protected]
2022-08-26 00:28 ` David G. Johnston <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox