[Sqlalchemy-tickets] [sqlalchemy] #1438: ON UPDATE CASCADE phrase not supported in Oracle

[sqlalchemy <mi...@zz...>]

#1438: ON UPDATE CASCADE phrase not supported in Oracle
-----------------------------+----------------------------------------------
 Reporter:  catherinedevlin  |          Owner:  zzzeek                
     Type:  defect           |         Status:  new                   
 Priority:  medium           |      Milestone:                        
Component:  (none)           |       Severity:  no triage selected yet
 Keywords:                   |   Status_field:  awaiting triage       
-----------------------------+----------------------------------------------
 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.

-- 
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/1438>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python