dinsdag 4 december 2007

Oracle and ON UPDATE CASCADE

We get errors from the following SQL on an Oracle database:

ALTER TABLE table_name
ADD CONSTRAINT constriant_name
FOREIGN KEY (TERMINOLOGY_ID)
REFERENCES TERMINOLOGY(TERMINOLOGY_ID) ON DELETE NO ACTION ON UPDATE CASCADE;


Finally we found out 2 things:
Oracle has no ON DELETE NO ACTION, use ON DELETE SET NULL instead
Oracle has no ON UPDATE at all for constraints so use trigger

So the fixed SQL looks like:

ALTER TABLE table_name
ADD CONSTRAINT constriant_name
FOREIGN KEY (TERMINOLOGY_ID)
REFERENCES TERMINOLOGY(TERMINOLOGY_ID) ON DELETE SET NULL;


and a trigger

sources:
http://www.psoug.org/reference/constraints.html
http://www.liacs.nl/databases/show.cgi?noaction
http://www.thescripts.com/forum/thread740824.html

Geen opmerkingen:

Een reactie plaatsen