envyM6 envyM6 - 6 months ago 60
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

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

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

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download