Re: TRUNCATE

> >From my limited understanding of truncate in Oracle is it requires the
> user to first disable integrity constraints on the table before
> truncate will run.
>
> In SQL Server that truncate will not allow truncate if foreign key
> constraints exist, but does not execute user delete triggers.
>
> Can't remember nor confirm either of these now. But, for consistency
> sake we should enforce the foreign key case. But I really think it
> should apply to all constraints, system or user enforced (rules, user
> written triggers).
>
> Besides that, theres always Codds twelfth rule which I've always
> liked:
> The nonsubversion rule: If low-level access is permitted it should not
> bypass security or integrity rules.

Dare I go against Codd, but, really, I've found it very convenient to be
able to export a single table, TRUNCATE it, clean up the data in another
program, and pull it back in. It's much more of a pain to have to dump the
whole db (neccessary or at least sanity preserving if there are lots of
complicated foreign key or trigger rules) or to drop/recreate the
triggers/rules.

The security issue is important, though: it's very likely that I might want
to let an certain class of user DELETE a record (with all the usual
rules/triggers/RI applying), but not let them bypass all that to TRUNCATE.

But I still wouldn't want to see hassle-free truncation disappear in the
name of security or idiot-proofing, if there are reasonable compromises.

- J.

Joel BURTON | joel(at)joelburton(dot)com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant