Re: Constants in the foreighn key constraints

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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
>