envyM6 envyM6 - 1 month ago 10
SQL Question

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

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 :(

Answer Source

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...