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