maria-discuss team mailing list archive

[Peter Laursen <peter_laursen@xxxxxxxxxx>]

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