Re: Proposal: TRUNCATE TABLE table RESTRICT

At 10:41 AM 6/8/00 -0400, Tom Lane wrote:
>Don Baccus <dhogaza(at)pacifier(dot)com> writes:
>> If the references are implicit (no REFERENCE or FOREIGN KEY given
>> to inform the db of the relationship) then a RESTRICT option to
>> truncate does seem useful.
>
>Uh, if the references are implicit, how would RESTRICT know they exist?

Duh, sorry, haven't had my coffee yet. I should know better than
think about computers before coffee...got any?

OK ... then I'd suggest that allowing truncate in the face of explicit
foreign keys is a bug. Truncate should either refuse to do so in
all cases, or follow RI rules (do ON DELETE CASCADE/SET NULL/SET DEFAULT
or refuse to do it depending on the foreign key def). It would
presumably do so by calling the RI trigger for each row just as delete
does.

TRUNCATE's documented as being a quick alternative to delete,
so refusal is perhaps the best course. Or the documentation
can say "it's a lot faster if there are no foreign keys referencing
it, otherwise it's the same as DELETE FROM".

But breaking RI by leaving "dangling references" is a bug, pure
and simple.

- Don Baccus, Portland OR <dhogaza(at)pacifier(dot)com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.