From: | aleksey ksenzov <alekseyksenzov(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Constants in the foreighn key constraints |
Date: | 2019-11-25 07:21:32 |
Message-ID: | CAB5pSh5tBPCRXHaJUrUpYXw_6HxwUhFY98C7OsP8VOFjyYPO-g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Jill Jade | 2019-11-25 07:55:13 | Pgadmin 4 schema visibility |
Previous Message | Mimiko | 2019-11-25 06:27:08 | Re: Constants in the foreighn key constraints |