Delete from two tables with circular foreign keys

Question

I have two tables

TableOne (TABLE_ONE_ID (pk), TABLE_TWO_ID (fk), ...(something else) )
TableTwo (TABLE_TWO_ID (pk), TABLE_ONE_ID (fk), ...(something else) )

How can I delete records from these tables?

P.S. I think that it is bad design, but this is not my fault and I don't have permission to change the database structure. I just need to know how to delete records from these tables.

http://stackoverflow.com/questions/7665914/oracle-how-to-delete-rows-with-bi-directional-dependencies might have your answer. — Greg Reynolds, Aug 31 '12 at 09:38
Have you tried something like [this][1] [1]: http://serverfault.com/questions/117432/how-to-delete-a-record-when-two-tables-have-foreign-key-referenced-to-each-other — lazarus, Aug 31 '12 at 09:39

score 18 · Accepted Answer · answered Aug 31 '12 at 09:39

18

I'm not sure of the best way to go about this but I would probably do something like:

UPDATE TableOne SET TABLE_TWO_ID = null;
DELETE FROM TableTwo;
DELETE FROM TableOne;

answered Aug 31 '12 at 09:39

Jim

22,354
6
52
80

4

What about table_two_id int **NOT** NULL ? – Stefan Steiger Jun 20 '16 at 15:51
1

@StefanSteiger In that case you could either set `table_one_id` to be null, remove the `NOT NULL` and readd or use one of the other answers such as @Andomar's. – Jim Jun 21 '16 at 08:31

Andomar · Answer 2 · 2012-09-01T09:10:38.810

8

You can disable the foreign key with:

alter table TableOne disable constraint fk_table_two_id;

After that, you should be able to delete the rows.

Per DaveCosta's comment, you could defer the constraint checks. That way the constraints are checked over the entire transaction, not each individual SQL statement. For example:

begin transaction;
set constraints all deferred; 
delete from TableTwo;
delete from TableOne;
commit transaction;

edited Sep 01 '12 at 09:10

answered Aug 31 '12 at 09:39

Andomar

232,371
49
380
404

2

If possible, I would say defer the constraint instead of disabling it, so it will allow the deletes but prevent a commit if the tables are out of sync. In a design like this, it might be wise for one of the two constraints to be permanently deferred. – Dave Costa Aug 31 '12 at 12:33
1

@DaveCosta: Interesting, where can I read more about deferring constraints? – Andomar Aug 31 '12 at 15:31
Here's one place to start: http://docs.oracle.com/cd/B28359_01/server.111/b28310/general005.htm – Dave Costa Aug 31 '12 at 17:19

score 7 · Answer 3 · answered Sep 01 '12 at 04:29

Either defer one of the foreign keys, or assign NULL (assuming it is NULL-able) to break the cycle.

BTW, how are you inserting the data? You must have done something to break the cycle there too.

NOTE: Disabling or deleting the FK may be OK if just one carefully controlled session is modifying the database, but would leave you wide open for data corruption in a concurrent environment where other clients may not be aware that the FK they expect is no longer enforced.

Delete from two tables with circular foreign keys

3 Answers3

Linked