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 <[EMAIL PROTECTED]> 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
