Permit TRUNCATE TABLE when referencing tables are empty
TRUNCATE TABLE is an efficient command to empty a table, as deallocates all extents and the extend allocation is all that is logged.
However, its usefulness is reduced by the fact that you cannot use it when there are referencing foreign keys. In the general case, this makes sense, as if the referencing table has data, truncating the table is almost bound to cause a constraint violation. The exception is if all rows in the referencing table has a NULL value in the FK column, but verifying this could be expensive.
However, you may want to use TRUNCATE TABLE on a set of related tables, for which you have set up foreign-key relations, for instance:
TRUNCATE TABLE child
TRUNCATE TABLE parent
Generally, you don't want to remove and restore FK-constraints as this potentially could lead to errors.
It seems that it would not be difficult to add logic to TRUNCATE TABLE, so rather giving up on a foreign key, it would check if the referencing tables actually have any extents allocated. (Which they would not have, it the child tables also have been subject to TRUNCATE TABLE.) I said "extents" here, rather than "rows", because if the table has been emptied with DELETE, I believe there are situation when a heap still can have many extents that has to be verified that the table is empty.
Note: there are two similar suggestions in this are:
which aim at the same problem, but with different solutions. I'm not saying that my solution is the best one, but I added it as a food for thought. It is likely to be sufficient if one of these three are implemented.