Menu
I've got a database consisting of four tables. Relation schema as follows in the picture:
And here are the rows:
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 :(
275
To correct this problem, you need to update or delete the value into the child table first and then you can delete the corresponding value into the parent table. For example, if you had created the following foreign key (parent-child relationship).
In order to remedy this error, you will need to insert the value that you attempted to place in the child table into the parent table first. Once inserted as a parent row, you can go back and insert the value into the child table.
The ORA-02292 error indicates that an “integrity constraint <constraint name> was violated – child record found”. What this indicates is that the user attempted to delete a record from a parent table (which is referenced by a foreign key), but a record in the child table exists.
Integrity constraint violations occur when an insert, update, or delete statement violates a primary key, foreign key, check, or unique constraint or a unique index. Message. Value. Description. Attempt to insert duplicate key row in object <object_name> with unique index <index_name> .
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...
85
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
