public inbox for [email protected]
help / color / mirror / Atom feedFrom: David G. Johnston <[email protected]>
To: chidamparam muthusamy <[email protected]>
Cc: [email protected]
Cc: Pgsql Novice <[email protected]>
Subject: Re: How to use a cross column exclude constraint
Date: Tue, 23 Aug 2022 08:55:09 -0700
Message-ID: <CAKFQuwb+eL7bYaE_9b9t7mGGj=c5SCYPmP0UoWitc86=+1UnCQ@mail.gmail.com> (raw)
In-Reply-To: <CABUk_4iSF9MagtgSX9ox5ZOqX0hMLo+FVN=xcJHksSW=aUriHQ@mail.gmail.com>
References: <[email protected]>
<CABUk_4iSF9MagtgSX9ox5ZOqX0hMLo+FVN=xcJHksSW=aUriHQ@mail.gmail.com>
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: <CAKFQuwb+eL7bYaE_9b9t7mGGj=c5SCYPmP0UoWitc86=+1UnCQ@mail.gmail.com>
* 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