Description:
A table has a CHECK constraint whose expression uses no column, such as CHECK (1 = 0) or CHECK (true). ALTER TABLE ... DROP COLUMN drops this constraint without an error or warning, even when the dropped column has nothing to do with it. Afterwards the table accepts rows that the constraint rejected before. ADD COLUMN, RENAME COLUMN and MODIFY keep the constraint. ALGORITHM=INPLACE and ALGORITHM=COPY behave the same. A column-level constant CHECK (b INT CHECK (1 = 0)) is also dropped when column a is dropped.
The manual (ALTER TABLE, "CHECK constraints") says: "If a CHECK constraint refers only to a single column, dropping the column automatically drops the constraint." A constraint that uses no column does not refer to the dropped column.
Expected: the constraint is kept, and the last INSERT below fails with ERROR 3819.
How to repeat:
CREATE TABLE t (a INT, b INT, CONSTRAINT ck CHECK (1 = 0));
INSERT INTO t VALUES (1, 1); -- ERROR 3819 (HY000): Check constraint 'ck' is violated.
ALTER TABLE t ADD COLUMN c INT;
SELECT CONSTRAINT_NAME FROM information_schema.CHECK_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = DATABASE(); -- ck
ALTER TABLE t DROP COLUMN b; -- OK, no warning
SELECT CONSTRAINT_NAME FROM information_schema.CHECK_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = DATABASE(); -- empty
INSERT INTO t VALUES (1, 1); -- OK, 1 row inserted
