[thelist] Auto Increment in database - a goofy question I am sure (long)

Jay Blanchard jay.blanchard at niicommunications.com
Thu Apr 18 10:02:00 CDT 2002


[snip]
i just had a look a the latest mysql docs, and found something puzzling

http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#CRE
ATE_TABLE says

   NOTE: There can be only one AUTO_INCREMENT column per table,
   and it must be indexed.

however, all the examples i've seen, in particular the tutorial 3.5.9 Using
AUTO_INCREMENT
http://www.mysql.com/documentation/mysql/bychapter/manual_Tutorial.html#exam
ple-AUTO_INCREMENT do *not* show the creation of an index on it

i have no idea what they meant by "it must be indexed"
[\snip]

from http://www.mysql.com/doc/C/R/CREATE_TABLE.html
"KEY is a synonym for INDEX."

You can also create other indexes,
http://www.mysql.com/doc/C/R/CREATE_INDEX.html

[snip value="2"]
i'm kind of curious about your remark that your auto_increment column is
"occasionaly ... used in queries" -- after new rows are added to the table,
how do you know what the values are, and how do you use them?
[/snip]

When I run certain queries for a phone number from these records it can have
multiple calls and call types associated with it. If I want to see each call
I can GROUP BY the AI column. I do not need to know the value, but I do need
to get unique rows. Having the unique value in each record also opens up
some other uses. It also provides me with an index for the records, where
there are no other columns with unique values to index on.

[snip value="3"]
also, your remark "to insure that there is one unique field in the tens of
thousands of records added to each of these tables every day" is
interesting -- why does there have to be a unique field?
[/snip]

see above

Jay





More information about the thelist mailing list