Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oQm0t-0002Gg-Mp for pgsql-novice@arkaria.postgresql.org; Wed, 24 Aug 2022 08:45:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oQm0s-0004k4-F4 for pgsql-novice@arkaria.postgresql.org; Wed, 24 Aug 2022 08:45:26 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oQm0s-0004ju-3c for pgsql-novice@lists.postgresql.org; Wed, 24 Aug 2022 08:45:26 +0000 Received: from w1.tutanota.de ([81.3.6.162]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oQm0l-0001to-4k for pgsql-novice@lists.postgresql.org; Wed, 24 Aug 2022 08:45:25 +0000 Received: from w3.tutanota.de (unknown [192.168.1.164]) by w1.tutanota.de (Postfix) with ESMTP id F2B95FBF923; Wed, 24 Aug 2022 08:45:17 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; t=1661330717; s=s1; d=tutanota.com; h=From:From:To:To:Subject:Subject:Content-Description:Content-ID:Content-Type:Content-Type:Content-Transfer-Encoding:Cc:Cc:Date:Date:In-Reply-To:In-Reply-To:MIME-Version:MIME-Version:Message-ID:Message-ID:Reply-To:References:References:Sender; bh=wN2Okxg3buuYjEWgKL7XAuxWHDlb+Rz5ejURn9Wa9YQ=; b=ZZpDNdwFNh0k8j+Nk44DREWFOrtRHPw7rjIoNQSi7ZxS9Hpuhy28QkD8weKQ09Cs dwNYLj1NtNHFwTdJfW7LMpREsnYeDfCjzkAbrzy9jdNGs2LyOLDVk1EqtcukF92KyeQ UVZRGvHmuJoTfk42SMCXsQ00zs6s2dy6bkGZe78VEuc8QA1cBHnAgUx9yHI064rCu1m 8o1sb1JEg96/xaPKQtEm2EXkkyWHaG8JYgfz8pY3DEgf/ZQ+AY71Q5fCvj6p3JHJ2mw utzzZBpNhuj7vbr2Zm/jwVqDS4SLrDFKlqx/Lid+43wXVxH1NyrfJDQcNjanv9IWBNC HKj05eAZ5A== Date: Wed, 24 Aug 2022 10:45:17 +0200 (CEST) From: awolchute@tutanota.com To: "David G. Johnston" Cc: Pgsql Novice , Mchidamparam Message-ID: In-Reply-To: References: Subject: Re: How to use a cross column exclude constraint MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_43555_490526727.1661330717976" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_43555_490526727.1661330717976 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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 lo= t 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 linkin= g 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 quer= ied / 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@gma= il.com> > wrote: > >> Refer=C2=A0>> https://www.postgresql.org/docs/current/ddl-constraints.ht= ml >> >> CREATE TABLE products ( product_no integer, name text, price nu= meric CHECK (price > 0), discounted_price numeric CHECK (discounted_pric= e > 0), >> CHECK (price > discounted_price)>> ); >> In the above example, column 'discounted_price'=C2=A0 value is checked t= hat 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 !=3D record_b_id) >> >>> >>> > > The declarative constraints available do not allow for a "diagonal" defin= ition.=C2=A0 CHECK constraints are row-limited but can reference any column= s.=C2=A0 Exclusion constraints are column-oriented, the specified columns a= re compared to the same columns in all other rows. > > You cannot declare that a value in column b exists or does not exist in c= olumn a on a different row.=C2=A0 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 no= n-normalized and the features of SQL are generally designed to help impleme= nt normalized data models.=C2=A0 You should consider whether you can redesi= gn things so that you can leverage the features present in the language; an= d the efficiencies and robustness that such features tend to have that cust= om trigger code may lack. > > David J. > > ------=_Part_43555_490526727.1661330717976 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi,

Thank you f= or 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 inform= ation about the link (so adding a record_id (fk) to the records table would= also add a ton of columns).
- the links are bid= irectional
- each "record" can be linked with ex= actly one "record", so a record linking to another does not allow the recor= d being referenced to be in any other link either.
- a graph of records and their connections (links) must be efficiently q= ueried / formed

Best= regards,

A




<= div>Aug 23, 2022, 15:55 by david.g.johnston@gmail.com:
On Tue= , Aug 23, 2022 at 7:48 AM chidamparam muthusamy <mchidampar= am@gmail.com> wrote:

= 
CREATE TABLE p=
roducts (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price)=

);
In the abov= e 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 c= olumns, 'record_a_id' and 'record_b_id' that is
CHECK(record_a_id !=3D record_b_id)
<= div>

=
= The declarative constraints available do not allow for a "diagonal" definit= ion.  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 v= alue in column b exists or does not exist in column a on a different row.&n= bsp; 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 th= e features of SQL are generally designed to help implement normalized data = models.  You should consider whether you can redesign things so that y= ou 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.


------=_Part_43555_490526727.1661330717976--