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)