Constants in the foreighn key constraints

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Constants in the foreighn key constraints

aleksey ksenzov
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.

Regards,
Aliaksei.
Reply | Threaded
Open this post in threaded view
|

Re: Constants in the foreighn key constraints

Adrian Klaver-4
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Constants in the foreighn key constraints

Adrian Klaver-4
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 <[hidden email]
> <mailto:[hidden email]>> 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
>     [hidden email] <mailto:[hidden email]>
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Constants in the foreighn key constraints

Tom Lane-2
Adrian Klaver <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Constants in the foreighn key constraints

Mimiko
In reply to this post by aleksey ksenzov
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 = true parent.


Reply | Threaded
Open this post in threaded view
|

Re: Constants in the foreighn key constraints

aleksey ksenzov
In reply to this post by Tom Lane-2
Hi team. Thanks for the information. 
Looks like there're some architectural limitations for such foreign keys. 
Also thanks for the suggestions on how to make it behaving like I want on current postgres version.

On Sat, 23 Nov 2019, 19:11 Tom Lane, <[hidden email]> wrote:
Adrian Klaver <[hidden email]> 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