Re: How to use a cross column exclude constraint

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)
Regards,
Chidamparam

On Mon, Aug 22, 2022 at 1:15 AM <awolchute(at)tutanota(dot)com> wrote:

>
> Hi All,
>
> Schema:
>
> CREATE TABLE record (
> id uuid primary key default ...,
> ...
> );
>
> CREATE TABLE record_pointer (
> id uuid primary key default ...,
> record_a_id uuid not null references record (id),
> record_b_id uuid not null references record (id),
> ...
> );
>
>
> I am trying to create an exclude constraint to make both record_a_id and
> record_b_id unique table wide, so that each row from "record" can ever be
> referenced once in "record_pointers".
>
> Eg. if I add a record_pointer row that has "this-is-random-uuid" as
> record_a_id, the value "this-is-random-uuid" can never be in record_a_id or
> record_b_id in any other row.
>
>
> Thank you in advance!
>
>
>