maria-discuss team mailing list archive

[Peter Laursen <peter_laursen@xxxxxxxxxx>]

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.
>
>
>
> -- 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
>>
>
>