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. Regards, Aliaksei. |
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 [hidden email] |
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 <[hidden email] > <mailto:[hidden email]>> 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 > [hidden email] <mailto:[hidden email]> > -- Adrian Klaver [hidden email] |
Adrian Klaver <[hidden email]> 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 |
In reply to this post by aleksey ksenzov
As a workaround, create a table with only one column and one value = `false` and foreign to it.
On 22.11.2019 16:32, aleksey ksenzov wrote: > 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. |
In reply to this post by Tom Lane-2
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, <[hidden email]> wrote: Adrian Klaver <[hidden email]> writes: |
Free forum by Nabble | Edit this page |