Delete data from table with FKs

[tnayfeh]

I have a scheduled package that is supposed to delete all data in 8 tables as below.

DELETE FROM PPES_DIM_USAGE
DELETE FROM PPES_DISPATCHER
DELETE FROM PPES_LEVEL_USAGE
DELETE FROM PPES_MEASURE_USAGE
DELETE FROM PPES_REQUEST
DELETE FROM PPES_SESSION
DELETE FROM PPES_USER_SOURCE
DELETE FROM PPES_ADMIN_SOURCE

However I get error message when I try to run this since some tables include FK and PK. "Delete statement conflicted with table REFERENCE constraint 'FK...."
Is there a way to run this anyways?

Thanks.

 

[JohnDTampaBay]

You will need to find out all the foreign keys for the tables in question. Delete from the tables in the opposite order that you would insert into them.

For example when a new customer places an order, you would insert into CUSTOMERS first, then into ORDERS, then into ORDER_DETAILS. Therefore, you should delete from ORDER_DETAILS, then ORDERS, then CUSTOMERS.

Execute each DELETE in a Query Analyzer window and see which table is failing. Execute the DELETE from the referencing table before the DELETE in question. Good luck!

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[SQLSister]

Or you can set up a cascading relationship so that if you delete from Customers all the orders are automatically deleted. Look up Cascade delete in Book online.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief