maria-discuss team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

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@xxxxxxxxxxx