PostgreSQL › PostgreSQL - general

Constants in the foreighn key constraints

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

6 messages Options

aleksey ksenzov

Constants in the foreighn key constraints

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.

Adrian Klaver-4

Re: 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
[hidden email]

Adrian Klaver-4

Re: Constants in the foreighn key constraints

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]

Tom Lane-2

Re: Constants in the foreighn key constraints

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

Mimiko

Re: Constants in the foreighn key constraints

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.

aleksey ksenzov

Re: Constants in the foreighn key constraints

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