add "on delete" and "on update" to ForeignKey ?
i've looking for on delete and on update actions functionality, ie. on delete cascade. i've been using a foreign key subclass below and some db specific engine changes (attached) in get_col_spec method (though its straight ansi afaik, though not in mysql till 5.0), the affects of this on in memory representation of relation collections aren't clear to me, ie. if this is a use case where such bookkeeping would be nesc. even if its not such a case, this functionality is something i'd like to see in sqlalchemy, as on delete/on update fk column action specification is really helpful, i think.
# requires corresponding database specific generator changes ( see
# attached pg sample)
class ForeignKeyWithAction( rdb.ForeignKey ):
allowed_change_actions = ( None,
'restrict',
'cascade',
'set null',
'set default', )
def __init__(self, column, on_delete=None, on_update=None):
rdb.ForeignKey.__init__( self, column )
self.on_delete = self.on_update = None
if on_delete:
assert on_delete in self.allowed_change_actions
self.on_delete = "ON DELETE %s"%on_delete
if on_update:
assert on_update in self.allowed_change_actions
self.on_update = "ON UPDATE %s"%on_update
cheers,
kapil
Index: postgres.py
===================================================================
--- postgres.py (revision 1023)
+++ postgres.py (working copy)
@@ -312,7 +312,14 @@
if column.primary_key and not override_pk:
colspec += " PRIMARY KEY"
if column.foreign_key:
- colspec += " REFERENCES %s(%s)" % (column.column.foreign_key.column.table.fullname, column.column.foreign_key.column.name)
+
+ colspec += " REFERENCES %s(%s)" % (column.column.foreign_key.column.table.fullname, column.column.foreign_key.column.name)
+
+ if hasattr(column.foreign_key, 'on_delete') and column.foreign_key.on_delete:
+ colspec += " " + column.foreign_key.on_delete
+ if hasattr(column.foreign_key, 'on_update') and column.foreign_key.on_update:
+ colspec += " " + column.foreign_key.on_update
+
return colspec
def visit_sequence(self, sequence):
Comments (5)
-
Account Deleted -
Account Deleted (original author: kapil) in terms of other constraint and action items that might be of interest for schema module.
check constraints for columns
unique constraint for column
mysql does not support check constraints, however i'd rather not have all of sqlalchemy dummed down to mysql inadequacies, considering that all the others i checked for this feature do implement it (oracle, postgres, firebird). i think it better instead to support the feature, with standard engine option of warn_unsupported or some such, which when features not supported in schema are utilized determines if the engine operates silently without processing the additional instructions, or instead raises an exception ( probably better if both log).
-
repo owner - changed milestone to 0.3.0
-
repo owner - changed status to resolved
on delete and on update got added with the ForeignKeyConstraint addition. "check" and "unique" constraints arent too hard to add eihter, although "unique" has some collision with "UNIQUE INDEX", with regards to the question of what should the "unique=True" flag on column create. separate ticket/whatever can be opened for "check" and "unique".
-
repo owner - removed milestone
Removing milestone: 0.3.0 (automated comment)
- Log in to comment
(original author: kapil) re this being ansi compliant and implemented, i spent a few minutes googling found a few explicit references to this being part of sql-92 ansi standards for referential integrity actions
http://www.dbmsmag.com/9606d17.html
google cache of ppt on sqlserver impl http://72.14.207.104/search?q=cache:P3gmHXvMwrEJ:www.sql.co.il/ug/13/Maintaining%2520Referential%2520Integrity.ppt+ANSI+SQL-92+on+delete&hl=en&gl=us&ct=clnk&cd=1
both reference it as being part of the standard.
in terms of implementation, every db i've looked at supports this (postgres, oracle, firebird, sqlserver, mysql), the only laggard being mysql's support till v5, as support previous to that had idiosyncratic behavior and was non standards compliant, see some of the comments from the link below for more details.
http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html