public inbox for [email protected]  

help / color / mirror / Atom feed

From: [email protected]
To: David G. Johnston <[email protected]>
Cc: Pgsql Novice <[email protected]>
Cc: Mchidamparam <[email protected]>
Subject: Re: How to use a cross column exclude constraint
Date: Wed, 24 Aug 2022 10:45:17 +0200 (CEST)
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKFQuwb+eL7bYaE_9b9t7mGGj=c5SCYPmP0UoWitc86=+1UnCQ@mail.gmail.com>
References: <[email protected]>
	<CABUk_4iSF9MagtgSX9ox5ZOqX0hMLo+FVN=xcJHksSW=aUriHQ@mail.gmail.com>
	<CAKFQuwb+eL7bYaE_9b9t7mGGj=c5SCYPmP0UoWitc86=+1UnCQ@mail.gmail.com>

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

view thread (7+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: How to use a cross column exclude constraint
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox