SQL deletion of a row returning- "ORA-02292: integrity constraint (..) violated - child record found"

Question

I've got a database consisting of four tables. Relation schema as follows in the picture:

enter image description here

And here are the rows:

enter image description here

Now I'm trying to delete the owner with owner id OW1 . Because the id is a primary key in owner table and foreign key in other table its not allowing me to delete the row. Here is the SQL I tried:

 delete from owners
 where ownerid = 'OW1' and petid = 'PT1'

And it returns :

ORA-02292: integrity constraint (TEST_1.ADDRESSES_OWNERS_FK) violated - child record found

And I'm not allowed to set the delete rule to 'CASCADE' in relation diagram. Please help :(

you can't delete it because it is a foreign key of PT1 on the pets table, as well as a foreign key on the contact and address table. You would need to delete these records first — Marshall Tigerus, Apr 22 '14 at 17:43
Can you delete the entries in CONTACTS and ADDRESS for the owner, as DELETE FROM contacts where ownerid = 'OW1'; and DELETE FROM addresses where ownerid = 'OW1';? — Joseph B, Apr 22 '14 at 17:43
@JosephB yes I can. But I have to remove the entry from owner table. If I can the delete them in one statement it is still acceptable. I mean I know I have to delete the foreign keys first in pets, contacts, address first then delete it in owners table, but it has to be done in same statement. — envyM6, Apr 22 '14 at 17:50
I want to try something similar they mentioned [here](http://stackoverflow.com/questions/3583459/cascade-delete-query)... just dont know how... — envyM6, Apr 22 '14 at 17:58
Why does it have to be done in one statement? Would an anonymous PL/SQL block count as a single statement for your purpose? And why does `petid` appear in the `owners` table at all - that's their favourite pet? (And why have you tagged this as mysql when you're getting an ORA error?) — Alex Poole, Apr 22 '14 at 18:18
@AlexPoole well Alex the pet id appears on the owner table to make sure no owner is registered without having a pet in the system... this a fragment of a whole database for a veterinary services — envyM6, Apr 22 '14 at 18:23
and mysql tag was auto suggested so I pressed it LOL :P... hands up, my bad!! — envyM6, Apr 22 '14 at 18:25
@envyM6 you could disable foreign key constraints http://stackoverflow.com/questions/15501673/how-to-temporarily-disable-a-foreign-key-constraint-in-mysql — Marshall Tigerus, Apr 22 '14 at 18:34

score 11 · Accepted Answer · answered Apr 22 '14 at 18:31

11

Well, if an anonymous block counts as one statement, just wrap your deletes in a block:

begin
  delete from addresses where ownerid = 'OW1';
  delete from contacts where ownerid = 'OW1';
  delete from pets where ownerid = 'OW1';
  delete from owners where ownerid = 'OW1';
end;
/

SQL Fiddle. Seems like a bit of a cheat, but if those are the conditions you've been given...

answered Apr 22 '14 at 18:31

Alex Poole

183,384
11
179
318

1

You could also disable the foreign key restraints, but yeah, this works too. – Marshall Tigerus Apr 22 '14 at 18:33
1

@MarshallTigerus - true, but that would be multiple statements, and I'm guessing that if the constraints can't be changed then they can't be disabled either. Who knows with artificial rules, though. (Though making then cascade is dangerous, so maybe that isn't so artificial; disabling them is also dangerous if it isn't a single-use system though). – Alex Poole Apr 22 '14 at 18:36
Agreed. With this many constraints it really should be an active/inactive flag system isntead of deleting stuff – Marshall Tigerus Apr 22 '14 at 18:38
Thanks lemme try it :) – envyM6 Apr 22 '14 at 18:42

score 11 · Answer 2 · edited Feb 02 '21 at 23:46

Sometimes, it is tedious to find out which child reference tables are resisting from deleting the data from the parent table. In such cases, we can find the references to the main table using this query.

   select * from
       all_constraints where
            r_constraint_name in
                 (select  constraint_name from all_constraints
                  where table_name= '<PARENT_TABLE_NAME>');

By going through the result list, one can easily find out the references. From there, you can temporarily disable constraints if you need to by:

ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;

And re-enable it when necessary. Hope this helps.

SQL deletion of a row returning- "ORA-02292: integrity constraint (..) violated - child record found"

2 Answers2