Deleting all data from tables

From: Alex <albu_at_my-deja.com>
Date: Thu, 09 Sep 1999 17:45:26 GMT
Message-ID: <7r8rnh$nvc$1@nnrp1.deja.com>

What's the best way to delete all data from a user's tables, while keeping the table structure? Simply running DELETE FROM <table_name> for all tables will not work because of a myriad of integrity constraints without the ON DELETE CASCADE.

Dropping all children before the parents doesn't seem appealing, because the solution isn't general. Also, how would this handle the case where a column that is a foreign key references a column in the same table?

One solution I can think of is to write a PL/SQL procedure that gets all integrity constraints for the user, drops them, gets all tables, deletes data from them, and creates the integrity constraints again.

Are there more elegant solutions? Any idea would be appreciated.

Alex

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Sep 09 1999 - 12:45:26 CDT