Newbie auto-increment question

[awingnut]

I'm a little fuzzy on the workings of auto-increment. It is not clear to me, what the difference is between auto-increment as a table option and auto-increment as a field option. I can't seem to get either to work (syntax errors).

Does a field have to be an index to get auto-imcremented (as opposed to a primary key)? What field gets incremented if the table option is set? What is the correct syntax for turning it on? I am trying to set it for an existing table:

alter table mytable add primary key (mykeyfield) auto_imcrement;

Could someone explain this to me and tell me what the correct syntax is for alter and create? TIA.

 

[sleipnir214]

Auto-increment as a table option"? I've never heard of this. To the best of my knowledge, auto_increment is only a column property.

You need to do two things to the table at once, both make the table-level change of adding a primary key and make the column-level change of adding the auto_increment. As such, your query needs to have two verbs in it.

Try:

ALTER TABLE mytable CHANGE mykeyfield mykeyfield INT UNSIGNED AUTO_INCREMENT, ADD PRIMARY KEY (mykeyfield)






Want the best answers? Ask the best questions! TANSTAAFL!

 

[awingnut]

Thanks for the reply. I mis-read the docs. The table option for auto_increment is to set a value.

I see now what I was doing wrong. The docs say "old_col_name column_definition". I thought "column_definition" meant exactly that. I didn't realize I was supposed to interprid it as: "old_col_name col_name column_definition".