Non-pk Auto_incriment

[gigawatz]

According to the version history,

Quote:

SQLyog 5.20 (November 2006)

• It is now possible to use the auto_increment attribute with an INTEGER column that is not defined as PK. CREATE TABLE will create the UNIQUE KEY needed for you. Also the Migration Tool now supports such autoincrement field.

However, I was not aware of this update until after I had created my database and tables with an eariler version of SQLyog. The earlier version required all Auto_Incriment fields to be part of the PK. I didn't think this was part of the MySQL requirements so I did some additional research and found that my intentions were valid. Therefore, before posting a defect, I searched the update log and found the update. I downloaded and installed the SQLyog Community Edition – MySQL GUI v5.22a in hopes of altering my tables to meet my desired specifications. However, this was not to be. When I went to Alter one of my tables, the GUI accepted my request, however, when I went to execute the change, I received the following message:

Quote:

Error No. 1075

Incorrect table definition; there can be only one auto column and it must be defined as a key

I did some further research to find that I can create a new table without triggering this error, it is only on the Alter.

Did I find a bug? Can anyone else reproduce this?

Edit:

Also, I am certain that there was only one autonumber pre-existing in the table, therefore the error was referencing the key requirement.

[peterlaursen]

1) With CREATE TABLE SQLyog does everything itself.  

2) With ALTER TABLE you will have to help yourself.

You will need to create an index first on the column that shall be auto-increment.

The reason is a (pretty technical) issue in MySQL with UNIQUE keys and ALTER TABLE .

 and I'll have to vacuumize my memory if I shall remember all details …

An example:

1)

Start using CREATE TABLE GUI to create this:

Code:

CREATE TABLE `keytest` ( `id` bigint(20) NOT NULL auto_increment, `id2` bigint(20) NOT NULL, `t` varchar(20) default NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1

2)

Now create an index with MANAGE INDEXES like

Code:

CREATE TABLE `keytest` ( `id` bigint(20) NOT NULL auto_increment, `id2` bigint(20) NOT NULL, `t` varchar(20) default NULL,
PRIMARY KEY (`id`),
KEY `id2` (`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

3)

ALTER TABLE GUI now lets you define:

Code:

CREATE TABLE `keytest` ( `id` bigint(20) NOT NULL, `id2` bigint(20) NOT NULL auto_increment, `t` varchar(20) default NULL,
PRIMARY KEY (`id`),
KEY `id2` (`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

With CREATE TABLE however you can go directly to step 3!

Do you use CREATE TABLE or ALTER TABLE?

[peterlaursen]

There was a FAQ actually: http://webyog.com/faq/28_139_en.html

This one explains why we chose NOT to let ALTER TABLE create the index

[gigawatz]

Thank you both for your answers. You both have been quite informative.

My apologies for not finding the FAQ and resolving this issue myself.

[Author]

Posts