Re: How to use a cross column exclude constraint

On Tue, Aug 23, 2022 at 7:48 AM chidamparam muthusamy <
mchidamparam(at)gmail(dot)com> 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.