Non-pk Auto_incriment

[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