maria-discuss team mailing list archive

[Peter Laursen <peter_laursen@xxxxxxxxxx>]

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