Re: How to use a cross column exclude constraint
| От | David G. Johnston |
|---|---|
| Тема | Re: How to use a cross column exclude constraint |
| Дата | |
| Msg-id | CAKFQuwYnANC8y5uSJtA8DvjvXUOwhuZJkjyCy8xFB1LFVqrE5w@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: How to use a cross column exclude constraint (awolchute@tutanota.com) |
| Список | pgsql-novice |
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.
В списке pgsql-novice по дате отправления: