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.

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, <tgl@sss.pgh.pa.us> wrote: