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

rudy r937 at interlog.com
Thu Apr 18 09:37:01 CDT 2002


> The AI index (my short-hand for 'auto increment index') is not used
> as a foreign key, but occasionaly it is used in queries. The primary
> function of the AI index is to insure that there is one unique field in
the
> tens of thousands of records added to each of these tables every day.

hi jay

thanks for the explanation of "AI"

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

http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#CR
EATE_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#exa
mple-AUTO_INCREMENT do *not* show the creation of an index on it

i have no idea what they meant by "it must be indexed"

note that if you declare the auto_increment column as PRIMARY KEY, then it
*will* have an index (other databases do this too, although they do not
make the keywords KEY and INDEX synonymous the way mysql does)

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?

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?


rudy




More information about the thelist mailing list