maria-discuss team mailing list archive

[Ruben Safir <ruben@xxxxxxxxxxxx>]

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@xxxxxxxxxx>
> 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@xxxxxxxxxxx>
>> 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@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
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