"David Johnston" <polobo@xxxxxxxxx> writes: > I am trying to get a better understanding of how the following Foreign Keys > with Update Cascades and validation trigger interact. The basic setup is a > permission table where the two permission parts share a common > "group/parent" which is embedded into their id/PK and which change via the > FK cascade mechanism. Rest of my thoughts and questions follow the setup. Well, the short answer is that there's not very much behind the curtain here. The FK CASCADE mechanisms just run SQL queries (like the ones you showed in CONTEXT lines) to perform the necessary adjustments of the referencing table when something changes in the referenced table. If you have a trigger on the referencing table that prevents some of these updates, then the updates don't get done ... and the result will be that the FK condition no longer holds everywhere. It might be safer if your trigger actually threw errors, rather than silently disabling such updates. Then at least the original referenced-table update would get rolled back and the two tables would remain consistent. There have been occasional discussions of how to make this stuff a bit cleaner/safer, but it's hard to see what to do without basically breaking the ability to have user-defined triggers on the referenced table. There are lots of safe and useful things such a trigger can do; but editorializing on the effects of an FK update query isn't one of them. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
