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