CODEWITHSUNDEEP
mysqlinnodb
Fabrizio
Fabrizio

Reputation: 3776

MySQL - convert MyISAM into InnoDB getting error 1075

I am trying to convert a table from MyISAM into InnoDB, this is the definition and I am getting error #1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

The table has an AutoIncrement value and the field is indexed and it works with MyISAM. I am new to InnoDB so it might be a dumb question

CREATE TABLE `cart_item` (
 `cart_id` int(10) unsigned NOT NULL DEFAULT '0',
 `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 `design_number` int(10) unsigned NOT NULL,
 `logo_position_id` smallint(5) unsigned NOT NULL,
 `subst_style_id` varchar(10) DEFAULT NULL,
 `style_id` varchar(10) NOT NULL DEFAULT '',
 `subst_color_id` smallint(5) unsigned DEFAULT NULL,
 `color_id` smallint(5) unsigned NOT NULL,
 `size_id` smallint(5) unsigned NOT NULL,
 `qty` mediumint(8) unsigned NOT NULL,
 `active` enum('y','n') NOT NULL DEFAULT 'y',
 `date_last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `last_modified_by_id` mediumint(5) unsigned NOT NULL,
 `date_last_locked` datetime DEFAULT NULL,
 `last_locked_by_id` smallint(5) unsigned NOT NULL,
 `date_added` datetime NOT NULL,
 `subsite_logo_group_id` int(11) NOT NULL,
 `bundle` varchar(32) NOT NULL,
 `color_stop_1` varchar(4) DEFAULT NULL,
 PRIMARY KEY (`cart_id`,`id`),
 KEY `color_id` (`color_id`),
 KEY `style_id` (`style_id`),
 KEY `size_id` (`size_id`),
 KEY `design_number` (`design_number`),
 KEY `subsite_logo_group_id` (`subsite_logo_group_id`),
 KEY `date_added` (`date_added`),
 KEY `bundle` (`bundle`)
) ENGINE=InnoDB

Upvotes: 1

Views: 633

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115540

What you were doing on the MyISAM table, cannot be done with InnoDB. See my answer on a (similar) problem: creating primary key based on date

MySQL docs, in the Using AUTO_INCREMENT section, explain it:

For MyISAM tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

You may get similar behaviour in InnoDB but not with AUTO_INCREMENT. You'll have to use either some fancy trigger or a stored procedure for your Inserts that will take care of the (per cart_id) auto-increment.

Upvotes: 2

John Woo
John Woo

Reputation: 263723

AUTO_INCREMENT columns should be define as key, as what the error implies.

`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,

and set UNIQUE on the two column instead of primary key 

UNIQUE (`cart_id`,`id`),

Upvotes: 2

Michael Berkowski
Michael Berkowski

Reputation: 270637

You have a composite PRIMARY KEY defined on (cart_id, id), but the AUTO_INCREMENT requires an index on id alone. You can add a KEY for it (not a primary key, but just a plain index):

KEY `idx_id` (`id`)

I question the use of the composite PK on (cart_id, id) though, since id is alone a unique value by definition. Perhaps you should make id the PK, and create a separate index across the combination.

PRIMARY KEY (`id`),
KEY (`cart_id`, `id`)

It doesn't even need to be specified as UNIQUE because the AUTO_INCREMENT can't be repeated anyway. There is no way to violate uniqueness on the combination (cart_id, id).

Upvotes: 2

Related Questions