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, <t...@sss.pgh.pa.us> wrote: > Adrian Klaver <adrian.kla...@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 >
