Re: serial primary key produces two indexes
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 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
Re: serial primary key produces two indexes
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 > > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
serial primary key produces two indexes
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
