Test case:

CREATE TABLE checks (a INT CHECK (a>2), b INT CHECK (b>2), CONSTRAINT a_greater CHECK (a>b));

SHOW CREATE TABLE checks;

ALTER TABLE `test`.`checks` CHANGE `a` `a` INT(11) NULL;

SHOW CREATE TABLE checks;

/' -- and now one constraint is gone!

CREATE TABLE `checks` (

`a` int(11) DEFAULT NULL,

`b` int(11) DEFAULT NULL CHECK (`b` > 2),

CONSTRAINT `a_greater` CHECK (`a` > `b`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 */

This docs page https://mariadb.com/kb/en/library/constraint/ does not say that (unnamed) CHECK constraints (ie. defined "column-wise") may be removed like this.

In this case the ALTER changes one column to what it already was and still the CONSTRAINT is removed. A more realistic scenario is that someone has an INT column that is about to overflow and wants to change it to a BIGINT.

I am not sure if this is an oversight/a bug or not. But if it is not, I think the docs page should clearly state that "an unnamed CHECK constraint is droppped implicitly when an ALTER TABLE statement is executed and this ALTER TABLE statement references the particular column and the ALTER TABLE statement does not recreate the CONSTRAINT".

There will be other cases where such ALTER TABLE would violate the CONSTRAINT due to already existing data. Wasn't it better if the ALTER TABLE failed then, rather than dropping the CONSTRAINT? What do standards say? How do other RDBMS behave with this? Does somebody know more about this?

What say?

-- Peter

-- Webyog