Issue #44 resolved

need operation to drop foreign key

Atsushi Odagiri avatarAtsushi Odagiri created an issue

No description provided.

Comments (13)

  1. Atsushi Odagiri

    On mysql, op.drop_constraint() causes error.

    For example, op.drop_constraint('fk_id', 't1') generate sql, "ALTER TABLE t1 DROP fk_id'. But that statement means drop column named fk_id.

    drop_constraint uses schema.Constraint, but mysql dialects requires subclasses of Constraint (e.g ForeignKeyConstraint, PrimaryKeyConstraint..)

    Maybe drop_constraint must detect kind of constraints.

  2. Mike Bayer

    can you show me the correct syntax for MySQL ?

    yes, the answer would be an extra parameter to drop_constraint() to suit MySQL's incompatibility here.

  3. Mike Bayer

    needs params for CHECK and UNIQUE as well, mysql breaking all the rules here. drop_constraint() alone should fail when run with MySQL if this param isn't present.

  4. Mike Bayer

    here's what I need you to test:

    1. alembic patch:

    diff -r 17b7299729fe1ff05e1638d555233875bc21caab alembic/operations.py
    --- a/alembic/operations.py	Mon Apr 30 15:17:18 2012 -0400
    +++ b/alembic/operations.py	Mon Apr 30 16:53:24 2012 -0400
    @@ -529,10 +529,29 @@
             # 0.7.6 and further raises on Index with no columns
             self.impl.drop_index(self._index(name, tablename, ['x']))
     
    -    def drop_constraint(self, name, tablename):
    -        """Drop a constraint of the given name"""
    +    def drop_constraint(self, name, tablename, type=None):
    +        """Drop a constraint of the given name
    +        
    +        :param name: name of the constraint.
    +        :param tablename: tablename:
    +        :param type: optional, required on MySQL.  can be 
    +        'foreignkey', 'unique', or 'check'
    +
    +        """
             t = self._table(tablename)
    -        const = schema.Constraint(name=name)
    +        types = {
    +            'foreignkey':schema.ForeignKeyConstraint,
    +            'unique':schema.UniqueConstraint,
    +            'check':schema.CheckContraint,
    +            None:schema.Constraint
    +        }
    +        try:
    +            const = types[type]
    +        except KeyError:
    +            raise TypeError("'type' can be one of %s" % 
    +                        ", ".join(repr(x) for x in types)) 
    +
    +        const = const(name=name)
             t.append_constraint(const)
             self.impl.drop_constraint(const)
     
    

    2. sqlalchemy patch:

    diff -r 572d4ebbca4b lib/sqlalchemy/dialects/mysql/base.py
    --- a/lib/sqlalchemy/dialects/mysql/base.py	Sun Apr 29 18:53:29 2012 -0400
    +++ b/lib/sqlalchemy/dialects/mysql/base.py	Mon Apr 30 16:54:11 2012 -0400
    @@ -1521,9 +1521,11 @@
             elif isinstance(constraint, sa_schema.UniqueConstraint):
                 qual = "INDEX "
                 const = self.preparer.format_constraint(constraint)
    +        elif isinstance(constraint, sa_schema.CheckConstraint):
    +            qual = "CHECK "
    +            const = self.preparer.format_constraint(constraint)
             else:
    -            qual = ""
    -            const = self.preparer.format_constraint(constraint)
    +            raise NotImplementedError("no generic 'DROP CONSTRAINT' in mysql")
             return "ALTER TABLE %s DROP %s%s" % \
                         (self.preparer.format_table(constraint.table),
                         qual, const)
    
  5. Mike Bayer

    we would need new tests both for sqlalchemy and alembic here, since this suggests sqlalchemy has a bug too regarding CHECK and generic constraints.

  6. Mike Bayer

    modify ForeignKeyConstraint in SQLAlchemy to accept just "name" alone as a kw arg if no column lists are given. all of these constraints need to be able to exist as "name only" objects.

  7. Mike Bayer

    another thought - we should fix sqlalchemy but let's put the full series of DROP CONSTRAINT compilations into alembic, just to make things easy for now.

  8. Log in to comment
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.