ID #1122

Can I use the auto_increment attribute without using that column as a Primary Key.



MySQL allows for that. The rules are:

  • There can only be one auto_increment column in a table
  • This column must be defined as a KEY

We introduced GUI support for this with SQLyog 5.2. You can now check the 'auto_increment' checkbox from the CREATE TABLE pane. SQLyog then issues a 'CREATE TABLE' statement that includes a definition/declaration of a UNIQUE KEY on that column.

Note that if you later remove the auto_increment attribute from that column the KEY remains in existence. We could drop the KEY, but we won't as we cannot be sure if it is needed for some other purpose. You will have to drop it from the 'Manage Indexes' window if you do not want it anymore. Maintaining indexes that are not used puts additional load on the server and may slow down operations on the table.

This is also the reason why we have not enabled the 'fully-automatic' creation of such autoincrement-field from ALTER TABLE. If a user 'moves' the autoincrement attribute from one column to another several times a corresponding number of KEYs would result. So the implementation with ALTER TABLE is done so that you will need first to create the KEY - and next you can assign the autoincrement attribute from ALTER TABLE.

Tags: -

Related entries:

You can comment this FAQ