Deleting rows with integrity constraint

Question

I have Table_A and Table_B having Table_A a FK to Table_B. I want to delete some rows from Table B, so I have a query like

DELETE FROM TABLE_B
WHERE TABLE_B.id IN(
    SELECT TABLE_A.B_id 
    FROM TABLE_A
    WHERE condition
)

So if I try to execute this, I am getting an error of integrity constraint violated due to the FK from A.

The problem I am having is that, if I first delete the problematic rows from Table_A (those that match condition), the inner SELECT don't return the expected result (because those rows have been already deleted), so the Delete doesn't work as expected.

I am completely noob in SQL so my question is, how can I do this? Is there a way to store the result of the inner select, then delete rows from Table_A and then delete from Table_B?

I don't want to create a new table or anything similar. Thanks.

This question provides you with information on how to disable the constraint for a short time: http://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-t-sql. Alternatively you can take a look here for cascade delete constraints: http://stackoverflow.com/questions/6260688/sql-server-cascade-delete — Rob Baillie, Feb 25 '14 at 14:16

score 2 · Accepted Answer · answered Feb 25 '14 at 14:17

2

Sure - use a temporary table:

SELECT TABLE_A.B_id 
INTO #TEMP_A
FROM TABLE_A
WHERE condition

DELETE FROM TABLE_A
WHERE B_id IN 
(SELECT B_id FROM #TEMP_A) 

DELETE FROM TABLE_B
WHERE id IN 
(SELECT B_id FROM #TEMP_A) 

DROP TABLE #TEMP_A

answered Feb 25 '14 at 14:17

D Stanley

149,601
11
178
240

sorry for my ignorance but, is the symbol # needed, or I can call the temp table whatever I want? Some reason, I get an error if I use it... – mario595 Feb 25 '14 at 14:33
The `#` signifies a temporary table - the name after the `#` can be any valid SQL table name. Are you using MS SQL Server? – D Stanley Feb 25 '14 at 14:35
It is Oracle. I am getting this error SQL Error: ORA-00911: invalid character – mario595 Feb 25 '14 at 14:44
Oracle is different - you tagged your question with `sql-server` which is meant for questions on MS SQL Server . I'll re-tag it as Oracle and amend my answer. – D Stanley Feb 25 '14 at 14:59

score 0 · Answer 2 · answered Feb 25 '14 at 15:08

If the constraint is deferrable, the following will work:

SET constraint fk_table_b_table_a DEFERRED;

DELETE FROM TABLE_B
WHERE TABLE_B.id IN(
    SELECT TABLE_A.B_id 
    FROM TABLE_A
    WHERE condition
);

DELETE TABLE_A
    WHERE condition
);

Unlike disabling a constraint, setting a constraint to deferred is session specific. Also, it will still enforce the constraint; it just does it when the commit is issued, instead of when the statement is executed.

Deleting rows with integrity constraint

2 Answers2