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_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. >> >>
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 <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 > -- So many immigrant groups have swept through our town that Brooklyn, like Atlantis, reaches mythological proportions in the mind of the world - RI Safir 1998 http://www.mrbrklyn.com DRM is THEFT - We are the STAKEHOLDERS - RI Safir 2002 http://www.nylxs.com - Leadership Development in Free Software http://www2.mrbrklyn.com/resources - Unpublished Archive http://www.coinhangout.com - coins! http://www.brooklyn-living.com Being so tracked is for FARM ANIMALS and and extermination camps, but incompatible with living as a free human being. -RI Safir 2013 _______________________________________________ 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
