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:
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.
