Обсуждение: Constants in the foreighn key constraints

On 11/22/19 6:32 AM, aleksey ksenzov wrote:
> Hi team.
> Latest time we faced several issues which wouldn't arise provided we 
> have possibility to use constants in foreign key constraints.
> brief example where it would be helpful:
> 
> table_a
> ( id uuid,
> parent_id uuid,
> is_deleted boolean
> )
> having possibility of FK (parent_id, false) to (id, is_deleted)
> would disallow setting parent record deleted flag to true if they have 
> children, or insert record with is_deleted = true parent.

Postgres version?

Look at triggers, in particular CONSTRAINT triggers:

https://www.postgresql.org/docs/12/sql-createtrigger.html

> 
> Regards,
> Aliaksei.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 11/22/19 11:36 PM, aleksey ksenzov wrote:

Please reply to list also.
Ccing list.

> We're already on 12.
> 
> While I understand I can do everything with triggers/functions, for me 
> it looks like a good idea to have possibility to use constants in 
> constraints, so it would be very nice if postgres community could add 
> this functionality in the nearest releases.
> Regards,
> Aliaksei.
> 
> 
> On Fri, Nov 22, 2019 at 4:25 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 11/22/19 6:32 AM, aleksey ksenzov wrote:
>      > Hi team.
>      > Latest time we faced several issues which wouldn't arise provided we
>      > have possibility to use constants in foreign key constraints.
>      > brief example where it would be helpful:
>      >
>      > table_a
>      > ( id uuid,
>      > parent_id uuid,
>      > is_deleted boolean
>      > )
>      > having possibility of FK (parent_id, false) to (id, is_deleted)
>      > would disallow setting parent record deleted flag to true if they
>     have
>      > children, or insert record with is_deleted = true parent.
> 
>     Postgres version?
> 
>     Look at triggers, in particular CONSTRAINT triggers:
> 
>     https://www.postgresql.org/docs/12/sql-createtrigger.html
> 
>      >
>      > Regards,
>      > Aliaksei.
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> Please reply to list also.
> Ccing list.

> On 11/22/19 11:36 PM, aleksey ksenzov wrote:
>> While I understand I can do everything with triggers/functions, for me 
>> it looks like a good idea to have possibility to use constants in 
>> constraints, so it would be very nice if postgres community could add 
>> this functionality in the nearest releases.

It seems quite unlikely to me that we'd add such a thing.  It'd be
a weird wart on the foreign-key feature.  Notable problems:

* How would it interact with referential actions, notably
ON UPDATE CASCADE, ON UPDATE/DELETE SET NULL, ON UPDATE/DELETE SET DEFAULT?
I guess you could disallow those options for such a foreign key,
but anytime you have a feature that's that non-orthogonal with
existing ones, you have to ask yourself if you've designed it right.

* Such FKs couldn't be displayed in the information_schema views,
at least not without violating the letter and spirit of the SQL spec.
We already have some cases of constraints that can't be shown in
information_schema, but that's not the sort of wart I want more of.

BTW, it seems to me that you can get the same behavior with existing
features: make a regular multicolumn foreign key constraint, and then
add a CHECK constraint restricting what value one of the referencing
columns can have.  Yeah, this requires useless storage of a column
that will only ever have one value.  I think that's an okay limitation
for a niche use-case.  It also generalizes more easily to cases where
there's more than exactly one allowed value for a referencing column.

            regards, tom lane

As a workaround, create a table with only one column and one value = `false` and foreign to it.

On 22.11.2019 16:32, aleksey ksenzov wrote:
> Latest time we faced several issues which wouldn't arise provided we have possibility to use constants in foreign key
constraints.
> brief example where it would be helpful:
> 
> table_a
> ( id uuid,
> parent_id uuid,
> is_deleted boolean
> )
> having possibility of FK (parent_id, false) to (id, is_deleted)
> would disallow setting parent record deleted flag to true if they have children, or insert record with is_deleted =
trueparent.