ON UPDATE CASCADE phrase not supported in Oracle

onupdate="CASCADE" argument in a ForeignKey is translated to an ON UPDATE CASCADE phrase, but that phrase is not supported by Oracle (version 11.1). So far as I know, in Oracle, "update cascade" must be implemented by hand, with triggers.

For example, a TurboGears project using user authentication attempts to define

user_group_table = Table('tg_user_group', metadata, Column('user_id', Integer, ForeignKey('tg_user.user_id', onupdate="CASCADE", ondelete="CASCADE")), Column('group_id', Integer, ForeignKey('tg_group.group_id', onupdate="CASCADE", ondelete="CASCADE")) )

... which, when generation is attempted, produces

File "/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.4p2-py2.6.egg/sqlalchemy/engine/base.py", line 931, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-00907: missing right parenthesis '\nCREATE TABLE tg_user_group (\n\tuser_id INTEGER, \n\tgroup_id INTEGER, \n\t FOREIGN KEY(group_id) REFERENCES tg_group (group_id) ON DELETE CASCADE ON UPDATE CASCADE, \n\t FOREIGN KEY(user_id) REFERENCES tg_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE\n)\n\n' {}

... which matches what happens if the generated SQL is entered directly into Oracle via SQL*Plus:

CREATE TABLE tg_user_group ( user_id INTEGER, group_id INTEGER, FOREIGN KEY(group_id) REFERENCES tg_group (group_id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY(user_id) REFERENCES tg_user (user_id) ON DELETE CASCADE ON UPDATE CASCADE )

FOREIGN KEY(group_id) REFERENCES tg_group (group_id) ON DELETE CASCADE ON UPDATE CASCADE, * ERROR at line 4: ORA-00907: missing right parenthesis

I suggest a warning or error message when the onupdate keyword is used in Oracle. Actually building in trigger creation for Oracle would probably be more trouble than it's worth.

Comments (4)