Re: BUG #5505: Busted referential integrity with triggers

I can understand firing the triggers. But what's up with not checking that the foreign key constraint is met? If the user has to manually ensure that values maintain referential integrity, why have foreign keys at all? The whole point of foreign keys is to make the database ensure referential integrity is maintained instead of having to do it manually.

Tommy McDaniel

-----Original Message-----
From: "Tom Lane" [tgl(at)sss(dot)pgh(dot)pa(dot)us]
Date: 06/14/2010 08:13 AM
To: "Tommy McDaniel" <tommstein(at)myway(dot)com>
CC: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #5505: Busted referential integrity with triggers

"Tommy McDaniel" <tommstein(at)myway(dot)com> writes:
> Let us also create a trigger to disable UPDATEs on table_2:
> ...
> And, we have now broken referential integrity.

Yup, this is not a bug, it's a feature. Triggers fire on
referential-integrity updates. (If they didn't, you could not for
example have a logging trigger log RI actions.) If you don't want
to break RI, you'd better think more carefully about what your
trigger does.

regards, tom lane