Foreign key dependency list

[andreis]

Hello:
My developers need a list - sequence of DB tables they can truncate without running into Foreign key reference restriction (for DBUnit testing which supposedly allows to clear referenced table if the referencing one is clear). The DB has around 200 tables, 180 out of them have Foreign keys. Is there a way to generate such a sequence automatically, maybe using Erwin?

Thanks.
Andrei

 

[karuzi]

I have done something like this before. I don't have the code. Essentially, you write a query that creates the drop statements for all the FKs (A), create query that creates the add statements(B).

Run A
Truncate target tables
Run B

 

[andreis]

Thank you for the hint, the problem is, the truncation should be done without dropping the foreign keys. That's why we need a list of tables dependencies, in the order permitting clearing the tables with foreign keys staying in the database.

 

[karuzi]

The other way to do this is to use Enterprise Manager and script add / drop statements for the FKs. EM will order the FKs in the proper order.