Thread: How to use a cross column exclude constraint
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!
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 <awolchute@tutanota.com> 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!
On Tue, Aug 23, 2022 at 7:48 AM chidamparam muthusamy <mchidamparam@gmail.com> wrote:
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 isCHECK(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.
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 david.g.johnston@gmail.com:
On Tue, Aug 23, 2022 at 7:48 AM chidamparam muthusamy <mchidamparam@gmail.com> wrote: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 isCHECK(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.
On Wednesday, August 24, 2022, <awolchute@tutanota.com> 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.
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 david.g.johnston@gmail.com:On Wednesday, August 24, 2022, <awolchute@tutanota.com> 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 / formedNode: [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.
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 <awolchute@tutanota.com> 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:-- nodeCREATE 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.
-- edgeCREATE 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.