maria-discuss team mailing list archive
Mailing list archive
Re: ALTER TABLE removes check constraint
Ruben Safir <ruben@xxxxxxxxxxxx>
Wed, 22 Nov 2017 13:43:54 -0500
Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.4.0
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
> -- Peter
> On Wed, Nov 22, 2017 at 3:28 PM, Peter Laursen <peter_laursen@xxxxxxxxxx>
>> 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@xxxxxxxxxxx>
>>> 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
>>>> that (unnamed) CHECK constraints (ie. defined "column-wise") may be
>>>> 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
>>> Chief Architect MariaDB
>>> and security@xxxxxxxxxxx
> Mailing list: https://launchpad.net/~maria-discuss
> Post to : maria-discuss@xxxxxxxxxxxxxxxxxxx
> 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
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!
Being so tracked is for FARM ANIMALS and and extermination camps,
but incompatible with living as a free human being. -RI Safir 2013