[Maria-discuss] ALTER TABLE removes check constraint
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
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
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
OK, I can see it is the same thing happening here
DROP TABLE tab; CREATE TABLE tab( id INT, num INT KEY); ALTER TABLE `test`.`tab` CHANGE `num` `num` BIGINT; SHOW CREATE TABLE tab; /* CREATE TABLE `tab` ( `id` int(11) DEFAULT NULL, `num` bigint(20) NOT NULL, -- <-- no KEY here anymore PRIMARY KEY (`num`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 */
However for indexes there is the SHOW INDEX command that will return a lsit of all indexes no matter what syntax variant was used to create them. Is there something similar for (CHECK) CONSTRAINTS that returns a list of all (CHECK) CONSTRAINTs for a table (or a complete database/schema)? I don't find anything in Information_Schema anywhere.
Such 'metadata instrumentation' is extremely important for client development.
-- Peter
On Wed, Nov 22, 2017 at 3:28 PM, Peter Laursen peter_laursen@webyog.com wrote:
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
On 11/22/2017 10:56 AM, Peter Laursen wrote:
OK, I can see it is the same thing happening here
DROP TABLE tab; CREATE TABLE tab( id INT, num INT KEY); ALTER TABLE `test`.`tab` CHANGE `num` `num` BIGINT; SHOW CREATE TABLE tab; /* CREATE TABLE `tab` ( `id` int(11) DEFAULT NULL, `num` bigint(20) NOT NULL, -- <-- no KEY here anymore PRIMARY KEY (`num`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 */
However for indexes there is the SHOW INDEX command that will return a lsit of all indexes no matter what syntax variant was used to create them. Is there something similar for (CHECK) CONSTRAINTS that returns a list of all (CHECK) CONSTRAINTs for a table (or a complete database/schema)? I don't find anything in Information_Schema anywhere.
Such 'metadata instrumentation' is extremely important for client development.
-- Peter
On Wed, Nov 22, 2017 at 3:28 PM, Peter Laursen peter_laursen@webyog.com wrote:
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.
It always has to my memory and how can it be any other way?
-- 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
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Hi, Peter!
On Nov 22, Peter Laursen wrote:
OK, I can see it is the same thing happening here
DROP TABLE tab; CREATE TABLE tab( id INT, num INT KEY); ALTER TABLE `test`.`tab` CHANGE `num` `num` BIGINT; SHOW CREATE TABLE tab; /* CREATE TABLE `tab` ( `id` int(11) DEFAULT NULL, `num` bigint(20) NOT NULL, -- <-- no KEY here anymore PRIMARY KEY (`num`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 */
However for indexes there is the SHOW INDEX command that will return a lsit of all indexes no matter what syntax variant was used to create them. Is there something similar for (CHECK) CONSTRAINTS that returns a list of all (CHECK) CONSTRAINTs for a table (or a complete database/schema)? I don't find anything in Information_Schema anywhere.
Such 'metadata instrumentation' is extremely important for client development.
The standard specifies, that it must be INFORMATION_SCHEMA.CHECK_CONSTRAINTS, but it's not implemented at the moment :(
I've created https://jira.mariadb.org/browse/MDEV-14474 for it.
Regards, Sergei Chief Architect MariaDB and security@mariadb.org
participants (3)
-
Peter Laursen -
Ruben Safir -
Sergei Golubchik