- The change in :ticket:`2721`, which is that the ``deferrable`` keyword

of :class:`.ForeignKeyConstraint` is silently ignored on the MySQL
backend, will be reverted as of 0.9; this keyword will now render again, raising
errors on MySQL as it is not understood - the same behavior will also
apply to the ``initially`` keyword.  In 0.8, the keywords will remain
ignored but a warning is emitted.   Additionally, the ``match`` keyword
now raises a :class:`.CompileError` on 0.9 and emits a warning on 0.8;
this keyword is not only silently ignored by MySQL but also breaks
the ON UPDATE/ON DELETE options.

To use a :class:`.ForeignKeyConstraint`
that does not render or renders differently on MySQL, use a custom
compilation option.  An example of this usage has been added to the
documentation, see :ref:`mysql_foreign_keys`.
[ticket:2721] [ticket:2839]

1 files changed, 40 insertions, 1 deletions

diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index d0f654fe2..445355e9f 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -265,6 +265,41 @@ http://dev.mysql.com/doc/refman/5.0/en/create-index.html
 
 http://dev.mysql.com/doc/refman/5.0/en/create-table.html
 
+.. _mysql_foreign_keys:
+
+MySQL Foreign Key Options
+-------------------------
+
+MySQL does not support the foreign key arguments "DEFERRABLE", "INITIALLY",
+or "MATCH".  Using the ``deferrable`` or ``initially`` keyword argument with
+:class:`.ForeignKeyConstraint` or :class:`.ForeignKey` will have the effect of these keywords being
+rendered in a DDL expression, which will then raise an error on MySQL.
+In order to use these keywords on a foreign key while having them ignored
+on a MySQL backend, use a custom compile rule::
+
+    from sqlalchemy.ext.compiler import compiles
+    from sqlalchemy.schema import ForeignKeyConstraint
+
+    @compiles(ForeignKeyConstraint, "mysql")
+    def process(element, compiler, **kw):
+        element.deferrable = element.initially = None
+        return compiler.visit_foreign_key_constraint(element, **kw)
+
+.. versionchanged:: 0.9.0 - the MySQL backend no longer silently ignores
+   the ``deferrable`` or ``initially`` keyword arguments of :class:`.ForeignKeyConstraint`
+   and :class:`.ForeignKey`.
+
+The "MATCH" keyword is in fact more insidious, and is explicitly disallowed
+by SQLAlchemy in conjunction with the MySQL backend.  This argument is silently
+ignored by MySQL, but in addition has the effect of ON UPDATE and ON DELETE options
+also being ignored by the backend.   Therefore MATCH should never be used with the
+MySQL backend; as is the case with DEFERRABLE and INITIALLY, custom compilation
+rules can be used to correct a MySQL ForeignKeyConstraint at DDL definition time.
+
+.. versionadded:: 0.9.0 - the MySQL backend will raise a :class:`.CompileError`
+   when the ``match`` keyword is used with :class:`.ForeignKeyConstraint`
+   or :class:`.ForeignKey`.
+
 """
 
 import datetime
@@ -1612,7 +1647,11 @@ class MySQLDDLCompiler(compiler.DDLCompiler):
                     (self.preparer.format_table(constraint.table),
                     qual, const)
 
-    def define_constraint_deferrability(self, constraint):
+    def define_constraint_match(self, constraint):
+        if constraint.match is not None:
+            raise exc.CompileError(
+                    "MySQL ignores the 'MATCH' keyword while at the same time "
+                    "causes ON UPDATE/ON DELETE clauses to be ignored.")
         return ""
 
 class MySQLTypeCompiler(compiler.GenericTypeCompiler):