Merge "MYSQL: added support for drop check/constraint"

3 files changed, 51 insertions, 2 deletions

diff --git a/doc/build/changelog/unreleased_13/4650.rst b/doc/build/changelog/unreleased_13/4650.rst
new file mode 100644
index 000000000..9c6623e17
--- /dev/null
+++ b/doc/build/changelog/unreleased_13/4650.rst
@@ -0,0 +1,11 @@
+.. change::
+    :tags: mysql, bug
+    :tickets: 4650
+
+    Added support for DROP CHECK constraint which is required by MySQL 8.0.16
+    to drop a CHECK constraint; MariaDB supports plain DROP CONSTRAINT.  The
+    logic distinguishes between the two syntaxes by checking the server version
+    string for MariaDB presence.    Alembic migrations has already worked
+    around this issue by implementing its own DROP for MySQL / MariaDB CHECK
+    constraints, however this change implements it straight in Core so that its
+    available for general use.   Pull request courtesy Hannes Hansen.
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py
index 9cae3c689..ad5ab288c 100644
--- a/lib/sqlalchemy/dialects/mysql/base.py
+++ b/lib/sqlalchemy/dialects/mysql/base.py
@@ -1703,6 +1703,12 @@ class MySQLDDLCompiler(compiler.DDLCompiler):
         elif isinstance(constraint, sa_schema.UniqueConstraint):
             qual = "INDEX "
             const = self.preparer.format_constraint(constraint)
+        elif isinstance(constraint, sa_schema.CheckConstraint):
+            if self.dialect._is_mariadb:
+                qual = "CONSTRAINT "
+            else:
+                qual = "CHECK "
+            const = self.preparer.format_constraint(constraint)
         else:
             qual = ""
             const = self.preparer.format_constraint(constraint)
@@ -2397,11 +2403,13 @@ class MySQLDialect(default.DefaultDialect):
 
     @property
     def _is_mariadb(self):
-        return "MariaDB" in self.server_version_info
+        return (
+            self.server_version_info and "MariaDB" in self.server_version_info
+        )
 
     @property
     def _is_mysql(self):
-        return "MariaDB" not in self.server_version_info
+        return not self._is_mariadb
 
     @property
     def _is_mariadb_102(self):
diff --git a/test/dialect/mysql/test_compiler.py b/test/dialect/mysql/test_compiler.py
index 54767a913..60e11ca29 100644
--- a/test/dialect/mysql/test_compiler.py
+++ b/test/dialect/mysql/test_compiler.py
@@ -5,6 +5,7 @@ from sqlalchemy import BOOLEAN
 from sqlalchemy import Boolean
 from sqlalchemy import cast
 from sqlalchemy import CHAR
+from sqlalchemy import CheckConstraint
 from sqlalchemy import CLOB
 from sqlalchemy import Column
 from sqlalchemy import DATE
@@ -128,6 +129,35 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
             "CREATE INDEX test_idx2 ON testtbl (data(5))",
         )
 
+    def test_drop_constraint_mysql(self):
+        m = MetaData()
+        table_name = "testtbl"
+        constraint_name = "constraint"
+        constraint = CheckConstraint("data IS NOT NULL", name=constraint_name)
+        tbl = Table(table_name, m, Column("data", String(255)), constraint)
+        dialect = mysql.dialect()
+        self.assert_compile(
+            schema.DropConstraint(constraint),
+            "ALTER TABLE %s DROP CHECK `%s`"
+            % (table_name, constraint_name),
+            dialect=dialect
+        )
+
+    def test_drop_constraint_mariadb(self):
+        m = MetaData()
+        table_name = "testtbl"
+        constraint_name = "constraint"
+        constraint = CheckConstraint("data IS NOT NULL", name=constraint_name)
+        tbl = Table(table_name, m, Column("data", String(255)), constraint)
+        dialect = mysql.dialect()
+        dialect.server_version_info = (10, 1, 1, "MariaDB")
+        self.assert_compile(
+            schema.DropConstraint(constraint),
+            "ALTER TABLE %s DROP CONSTRAINT `%s`"
+            % (table_name, constraint_name),
+            dialect=dialect
+        )
+
     def test_create_index_with_length_quoted(self):
         m = MetaData()
         tbl = Table(