Re: Foreign key constaint can be broken

>> I found a way to break a foreign key constraint in PostgreSQL
>> [ ie, make a rule that defeats an ON DELETE CASCADE operation ]
>
> This isn't a bug, it's just the way things work. Rules (and triggers)
> apply to the commands that implement foreign key updates, so a poorly
> written rule can make those queries do the wrong thing. The rule can
> make your regular queries do the wrong thing too, so it's not like you'd
> be fine if it were done some other way. There are a number of real
> applications that would be broken if rules/triggers *didn't* apply to
> FK queries --- for example, using a trigger to implement logging --- so
> we've concluded this is the most useful way for it to be done.

It may suggest that a rule may have a optionnal specifier to tell the
context in which it should be applied, for instance :

CREATE RULE ... ON [ ALL | INTERNAL | EXTERNAL ] UPDATE TO ...

Where "INTERNAL" would tag foreign key stuff while "EXTERNAL" would be
only the user stuff.

I'm not sure it would not add to the confusion, though.

--
Fabien.