On delete cascade Derby 10.4.1.3

[Andrzej Kasprzyszyn <an...@gmail.com>]

Hi All,

I have a problem with delete cascade functionality on Derby 10.4.1.3.
I have a foreign key constructions like this:

ALTER TABLE ct_library ADD CONSTRAINT ct_library_fk1
FOREIGN KEY (usr)
REFERENCES ct_user (id)
ON DELETE CASCADE


ALTER TABLE ct_book ADD CONSTRAINT ct_book_fk4
FOREIGN KEY (lib)
REFERENCES ct_library (id)

Derby requires that a parent table (ct_library) and a child table
(ct_book) have to have the same cascade action (delete).

'...the delete rule of foreign key must be CASCADE. (The relationship
would cause the table to be delete-connected to the same table through
multiple relationships and such relationships must have the same
delete rule (NO ACTION, RESTRICT or CASCADE).) '.

I can not change foreign key constructions. I have to figure out a
solution for cascade deleting.

I have tried to remove all ON DELETE CASCADE clauses and use AFTER
DELETE TRIGGER.

CREATE TRIGGER COUNTRY_DEL_TRIG
AFTER DELETE ON COUNTRY
REFERENCING OLD_TABLE AS DELETEDCOUNTRY
FOR EACH STATEMENT
DELETE FROM COUNTRY_PROPERTY WHERE COUNTRY_ID IN (SELECT COUNTRY_CODE
FROM DELETEDCOUNTRY)

But executing above trigger causes following exception:
"DELETE on table 'COUNTRY' caused a violation of foreign key
constraint 'CP_FK1' for key (01). The statement has been rolled back."

CP_FK1 definition:
ALTER TABLE country_property ADD CONSTRAINT cp_fk1
       FOREIGN KEY (country_id)
       REFERENCES country (country_code)
;

Do you have any ides?
Thank you for any comments.

Regards,
Andrzej