The ``deferrable`` keyword argument on :class:`.ForeignKey` and

:class:`.ForeignKeyConstraint` will not render the ``DEFERRABLE`` keyword
on the MySQL dialect.  For a long time we left this in place because
a non-deferrable foreign key would act very differently than a deferrable
one, but some environments just disable FKs on MySQL, so we'll be less
opinionated here. [ticket:2721]

4 files changed, 37 insertions, 0 deletions

diff --git a/doc/build/changelog/changelog_08.rst b/doc/build/changelog/changelog_08.rst
index 94ada9ac5..82e65272b 100644
--- a/doc/build/changelog/changelog_08.rst
+++ b/doc/build/changelog/changelog_08.rst
@@ -7,6 +7,17 @@
     :version: 0.8.2
 
     .. change::
+        :tags: bug, mysql
+        :tickets: 2721
+
+        The ``deferrable`` keyword argument on :class:`.ForeignKey` and
+        :class:`.ForeignKeyConstraint` will not render the ``DEFERRABLE`` keyword
+        on the MySQL dialect.  For a long time we left this in place because
+        a non-deferrable foreign key would act very differently than a deferrable
+        one, but some environments just disable FKs on MySQL, so we'll be less
+        opinionated here.
+
+    .. change::
         :tags: bug, ext, orm
         :tickets: 2730
 
diff --git a/doc/build/changelog/changelog_09.rst b/doc/build/changelog/changelog_09.rst
index 673a50a9a..c5ee84a7b 100644
--- a/doc/build/changelog/changelog_09.rst
+++ b/doc/build/changelog/changelog_09.rst
@@ -7,6 +7,17 @@
     :version: 0.9.0
 
     .. change::
+        :tags: bug, mysql
+        :tickets: 2721
+
+        The ``deferrable`` keyword argument on :class:`.ForeignKey` and
+        :class:`.ForeignKeyConstraint` will not render the ``DEFERRABLE`` keyword
+        on the MySQL dialect.  For a long time we left this in place because
+        a non-deferrable foreign key would act very differently than a deferrable
+        one, but some environments just disable FKs on MySQL, so we'll be less
+        opinionated here.  Also in 0.8.2.
+
+    .. change::
         :tags: bug, ext, orm
         :tickets: 2730
 
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index ad4650f6d..03827edb7 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -1567,6 +1567,8 @@ class MySQLDDLCompiler(compiler.DDLCompiler):
                     (self.preparer.format_table(constraint.table),
                     qual, const)
 
+    def define_constraint_deferrability(self, constraint):
+        return ""
 
 class MySQLTypeCompiler(compiler.GenericTypeCompiler):
     def _extend_numeric(self, type_, spec):
diff --git a/test/dialect/test_mysql.py b/test/dialect/test_mysql.py
index 728098d3a..2c459dead 100644
--- a/test/dialect/test_mysql.py
+++ b/test/dialect/test_mysql.py
@@ -82,6 +82,19 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "PRIMARY KEY (data) USING btree)",
             dialect=mysql.dialect())
 
+    def test_skip_deferrable_kw(self):
+        m = MetaData()
+        t1 = Table('t1', m, Column('id', Integer, primary_key=True))
+        t2 = Table('t2', m, Column('id', Integer,
+                        ForeignKey('t1.id', deferrable=True),
+                            primary_key=True))
+
+        self.assert_compile(
+            schema.CreateTable(t2),
+            "CREATE TABLE t2 (id INTEGER NOT NULL, "
+            "PRIMARY KEY (id), FOREIGN KEY(id) REFERENCES t1 (id))"
+        )
+
 class DialectTest(fixtures.TestBase):
     __only_on__ = 'mysql'