I don't know if it always was the case. But I bet that all GUI tools (and unaware users) will remove the CONSTRAINT in what interface they have for ALTER TABLE. They will need to check in SHOW CREATE TABLE if an unnamed CHECK constraint was specified with the column_definition and if there is a " .. CHECK(expression) .." will need to be added inside the ALTER statement generated by the client in order to 'reinstantiate' the CONSTRAINT. We don't in SQLyog currently and I am 99.999999999999999999999% sure that nobody else do either. :-).

As conclusion I'd say: use named - not unnamed - CHECK constraints!

But thanks for the explanation.

-- Peter

On Wed, Nov 22, 2017 at 2:56 PM, Sergei Golubchik <serg@mariadb.org> wrote:

Hi, Peter!

On Nov 22, Peter Laursen wrote:
> 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.

This was always the case, wasn't it? The semantic is and always was

ALTER TABLE table_name CHANGE old_column_name new_column_definition

That is, you define the column as

`a` INT(11) NULL

without the constraint, so it's not present anymore. MariaDB doesn't
yet support the standard syntax:

ALTER TABLE table ALTER column SET DATA TYPE type

Regards,
Sergei
Chief Architect MariaDB
and security@mariadb.org