integrity constraint - foreign key not found? 2

[slok]

I have 2 tables as follows

Table A
DATETIME NOT NULL DATE
REG_NO NOT NULL CHAR(6)
....
constraint pk_a primary key (reg_no, datetime),


Table Audit
DATETIME NOT NULL DATE
REG_NO NOT NULL CHAR(6)
....
constraint fk_audit foreign key (reg_no, datetime) references a



when I try to delete the records in audit and table A,
I got the errors

ORA-02291: integrity constraint (my_package.FK_AUDIT) violated - parent key not found


Sample of my codes are as follows


select mod_datetime into v_mod_datetime
from A where
datetime = p_datetime
AND reg_no = v_reg_no
for update nowait;


-- delete audit
delete from offence_audit where
datetime = p_datetime
AND reg_no = v_reg_no;

-- delete A
delete from A where
datetime = p_datetime
AND reg_no = v_reg_no;


what could be the problem?

 

[THoey]

Do you need to have a commit between the two deletes? Also, couldn't you just delete from table A with the CASCADE option? Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[slok]

how can I do a delete with a CASCADE

 

[jee]

add an "ON DELETE CASCADE" to your FK specification on the Audit table, then just delete from the A table.

You should not need to commit between the two deletes.

Also, are there any triggers that are being executed as a result of the deletes? - if so, they could be the source of the problem. To test, try disabling the triggers and then deleting.

The error message doesn't look like what you would get if there was a problem with the delete - it looks like the kind of error you would get if you tried to insert a row into A (or update the FK columns of an existing row) and there was no appropriate "parent" for the FK in Audit.