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_laur...@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 <s...@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 secur...@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
