I have a table with a lot of foreign keys. I want to change tinyint(3) to int(10).
I have:
| publishers | CREATE TABLE publishers (
publisher_id tinyint(5) unsigned NOT NULL AUTO_INCREMENT,
market_id tinyint(3) unsigned NOT NULL,
name varchar(50) NOT NULL,
code char(10) NOT NULL,
timezone varchar(50) NOT NULL DEFAULT ‘Europe/Stockholm’,
status enum(‘paused’,‘active’) NOT NULL DEFAULT ‘active’,
created timestamp NOT NULL DEFAULT ‘1971-01-01 00:00:00’,
updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (publisher_id),
UNIQUE KEY UNIQUE_MARKET_ID_NAME_CODE (market_id,name,code),
CONSTRAINT publishers_ibfk_1 FOREIGN KEY (market_id) REFERENCES markets (market_id) ON DELETE CASCADE ON UPDATE
CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=188 DEFAULT CHARSET=utf8 |
which I want to change to
| publishers | CREATE TABLE publishers (
publisher_id int(10) unsigned NOT NULL AUTO_INCREMENT,
market_id tinyint(3) unsigned NOT NULL,
name varchar(50) NOT NULL,
code char(10) NOT NULL,
timezone varchar(50) NOT NULL DEFAULT ‘Europe/Stockholm’,
status enum(‘paused’,‘active’) NOT NULL DEFAULT ‘active’,
created timestamp NOT NULL DEFAULT ‘1971-01-01 00:00:00’,
updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (publisher_id),
UNIQUE KEY UNIQUE_MARKET_ID_NAME_CODE (market_id,name,code),
CONSTRAINT publishers_ibfk_1 FOREIGN KEY (market_id) REFERENCES markets (market_id) ON DELETE CASCADE ON UPDATE
CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=188 DEFAULT CHARSET=utf8 |
when I run:
SET FOREIGN_KEY_CHECKS=0;
alter table publishers modify column publisher_id int(10) unsigned NOT NULL AUTO_INCREMENT;
SET FOREIGN_KEY_CHECKS=1;
I get:
ERROR 1025 (HY000): Error on rename of ‘./adxsearch_2011_10_21/#sql-60de_3670d4’ to ‘./adxsearch_2011_10_21/publishers’ (errno: 150)
How do I rectify this problem so I can change the column to int(10)?