Sergei Golubchik added a comment - 2016-10-28 13:50 I don't think it's a bug, I do prefer an error over automatic dropping of a constraint.

Elena Stepanova added a comment - 2016-10-28 14:06 I don't have very strong objections against it, but then make the error message sensible, something like "can't drop column because it's referenced..." or whatever. The current one is meaningless.

Sergei Golubchik added a comment - 2017-03-22 18:09 I'll make it ERROR 1054 (42S22): Unknown column `a` in 'CHECK' , that should be clearer.

Mike Bayer added a comment - 2017-07-23 20:22 - edited Hi there - I would like to propose that this is the incorrect resolution to this issue. While at face value, the proposal to "automatically drop a constraint" sounds like the wrong thing, this is very much the standard approach to a constraint that relies solely upon a single column in a table. Consider the behavior in the same kind of situation for the following types of constraints: NOT NULL constraint - is implicitly dropped with the column: MariaDB [test]> create table t1 (a int NOT NULL, b int); Query OK, 0 rows affected (0.04 sec)   MariaDB [test]> alter table t1 drop column a; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 UNIQUE constraint - is implicitly dropped with the column: MariaDB [test]> create table t1 (a int, b int, unique key a (a)); Query OK, 0 rows affected (0.02 sec)   MariaDB [test]> alter table t1 drop column a; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 CHECK constraint - is implicitly dropped when you drop the whole table . The above behaviors illustrate that the implicit dropping of constraints is a completely reasonable behavior and already is the behavior in most cases for MariaDB as well as most other databases. The case of CHECK CONSTRAINT here is entirely inconsistent with that of all other kinds of constraints. The change is also backwards incompatible with all MySQL versions and MariaDB versions prior to 10.2. Application code that emits CREATE TABLE statements which include CHECK constraints now produce a table where columns cannot be dropped - this because MariaDB 10.2 has made CHECK constraints actually do something whereas in all prior version and MySQL versions, the CHECK syntax was silently ignored but accepted. Finally, preventing the column from being dropped due to a CHECK constraint that refers to it alone is inconsistent with the behavior of most other major database systems. Postgresql - CHECK constraint is implicitly dropped: psql (9.5.7) Type "help" for help.   test=# create table t1 (a int, b int, check(a>0)); CREATE TABLE test=# alter table t1 drop column a; ALTER TABLE test=# Oracle - CHECK constraint is implicitly dropped: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production   SQL> create table t1 (a int, b int, check(a>0));   Table created.   SQL> alter table t1 drop column a;   Table altered. Microsoft SQL Server does have this limitation: Msg 5074, Level 16, State 1, Line 4 The object 'CK__t1__a__25DD32AA' is dependent on column 'a'. Msg 4922, Level 16, State 9, Line 4 ALTER TABLE DROP COLUMN a failed because one or more objects access this column. However, in the case of SQL server, the change is at least not backwards incompatible. Given MariaDB's mission of staying as close to cross-compatibility with MySQL as possible, the decision to prevent DROP COLUMN without locating all CHECK constraints and explicitly dropping them as well will only make it that more difficult. At least provide an option to DROP COLUMN and/or a SQL mode setting which makes this possible, however as noted above it's inconsistent that no such limitation exists for all other kinds of constraints. Explicitly dropping constraints that were not given explicit names in particular is very inconvenient as the name of the constraint is required to handle the DROP.

Mike Bayer added a comment - 2017-07-23 20:24 downstream SQLAlchemy / Alembic issue: https://bitbucket.org/zzzeek/alembic/issues/440/cannot-drop-boolean-column-in-mysql

Sergei Golubchik added a comment - 2017-07-24 15:12 Okay. SQL Standard says: 11.23 <drop column definition> ... 7) If RESTRICT is specified, then C shall not be referenced in any of the following: ... b) The <search condition> of any constraint descriptor other than a table constraint descriptor that contains references to no other column and that is included in the table descriptor of T. Which means that DROP COLUMN xxx RESTRICT should automatically drop constraints that only refer to this one column, but should fail if there are constraints that refer to this and other columns. I suspect that DROP COLUMN xxx CASCADE should drop all constraints that refer to this column. Either way, dropping a column in the original test case should succeed. In the standard RESTRICT / CASCADE is not optional. We don't have that, so let's do RESTRICT .

Sergei Golubchik added a comment - 2017-08-12 15:12 - edited Assuming create table t1 (a int , b int , check (a>0)); If one does alter table t1 drop column a; it's clear that the constraint should be dropped. What should be the effect of 1 alter table t1 drop column a, add column a bigint ; Two possible outcomes here: drop the constraint, keep the constraint. 2 alter table t1 change column a c int ; Three possible outcomes here: drop the constraint, change the constraint, error. 3 alter table t1 change column a c int , change column b a int ; Two possible outcomes here: change the constraint, keep the constraint as it was.

Sergei Golubchik added a comment - 2017-08-12 15:39 let's do 1: drop, 2: change, 3: change. Comparing what the behavior for indexes, indexes are changed in 2 and 3, and in 1 the behavior is buggy and inconsistent, I'll fix it to drop.

Sergei Golubchik added a comment - 2017-08-12 16:28 The issue with renaming columns was moved to MDEV-13508