Search Postgresql Archives

Re: Constants in the foreighn key constraints

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxx> wrote:
Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux