Thank Gleb,
I misunderstood the meaning of the manual..
==== this is from the manual ======
|SERIAL| is an alias for |BIGINT UNSIGNED NOT NULL AUTO_INCREMENT|.
|SERIAL DEFAULT VALUE| in the definition of an integer column is an
alias for |NOT NULL AUTO_INCREMENT UNIQUE|.
====================
The primary key is a modifier, so would it be reasonable to modify the
source code:
adding an if statement,
if column already a unique key, then just rename the key to primary
other than creating another key named unique.
I also noticed that you can add as many indices as you want to a column.
This should also be prevented by the server. This is related to the
serial primary key definition redundancy.
In real life I have seen tables has more indices then the content of the
table.
Normally people would take a look at the index of the table
show index from <table>
but some people are not careful enough. So I would recommend that the
implementation team adding some checking as to the indices.
Kemin
Gleb Paharenko wrote:
>Hello.
>
>SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
>So you really specifying two keys (primary and unique). See:
> http://dev.mysql.com/doc/mysql/en/news-4-1-0.html
> http://bugs.mysql.com/bug.php?id=13140
>
>
>
>Kemin Zhou <kzhou@stripped> wrote:
>
>
>>I recently discovered that the following
>>
>>create table ttt (
>> id serial primary key,
>> txt text
>>);
>>
>>show index from ttt
>>
>>is telling me that there is a primary ke on id column with BTREE
>>and at the same time, there is another unique index on the id column.
>>
>>This is redundant.
>>
>>if the id column had been specified as
>>
>>id integer auto_increment primary key,
>>then there is only one primary key
>>
>>So it looks that there is a bug in the mysql source code.
>>Could some exper please confirm my opinion?
>>
>>I am using version 4.1
>>
>>Kemin
>>
>>
>>
>>
>>
>
>
>
>
