CODEWITHSUNDEEP
mysqlforeign-keysintalterbigint
deif
deif

Reputation: 1389

mysql alter int column to bigint with foreign keys

I want to change the datatype of some primary-key columns in my database from INT to BIGINT. The following definition is a toy-example to illustrate the problem:

CREATE TABLE IF NOT EXISTS `owner` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `thing_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `thing_id` (`thing_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

DROP TABLE IF EXISTS `thing`;
CREATE TABLE IF NOT EXISTS `thing` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

ALTER TABLE `owner`
  ADD CONSTRAINT `owner_ibfk_1` FOREIGN KEY (`thing_id`) REFERENCES `thing` (`id`);

Now when i try to execut one of the following commands:

ALTER TABLE `thing` CHANGE `id` `id` BIGINT NOT NULL AUTO_INCREMENT;
ALTER TABLE `owner` CHANGE `thing_id` `thing_id` BIGINT NOT NULL;

i'm running into an error

#1025 - Error on rename of './debug/#[temp-name]' to './debug/[tablename]' (errno: 150)

SHOW INODB STATUS outputs:

LATEST FOREIGN KEY ERROR
------------------------
120126 13:34:03 Error in foreign key constraint of table debug/owner:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
,
 CONSTRAINT "owner_ibfk_1" FOREIGN KEY ("thing_id") REFERENCES "thing" ("id")

I'm guessing that the foreign key definition blocks changing the column type on either side. The naive approach to solve this problem would be to delete the foreign key definitions, alter the columns and re-define the foreign keys. is there a better solution?

Upvotes: 15

Views: 20942

Answers (4)

Ben Birney
Ben Birney

Reputation: 907

Even though you changed the column size of id and thing_id, the raw data in the rows is still 4 byte integers instead of 8 byte integers. But you can convert the data.

Try this solution instead. I had to do something similar recently on a large data set, converting INT columns to BIGINT when the data set threatened to grow too large.

ALTER TABLE `owner`
DROP FOREIGN KEY `owner_ibfk_1`;

ALTER TABLE `thing` CHANGE `id` `id` BIGINT NOT NULL AUTO_INCREMENT;
ALTER TABLE `owner` CHANGE `thing_id` `thing_id` BIGINT NOT NULL;

UPDATE `thing` SET `id` = CAST(`id` AS UNSIGNED INTEGER);
UPDATE `owner` SET `thing_id` = CAST(`thing_id` AS UNSIGNED INTEGER);

-- Now the data are BIGINTs; re-create the foreign key constraint

ALTER TABLE `owner`
  ADD CONSTRAINT `owner_ibfk_1` FOREIGN KEY (`thing_id`) REFERENCES `thing` (`id`);

Upvotes: 0

Oliver
Oliver

Reputation: 117

I had a similar problem the solution is the change clause:

ALTER TABLE table_name CHANGE id id BIGINT(20) NOT NULL AUTO_INCREMENT;

That worked for me.

Upvotes: -1

Devart
Devart

Reputation: 121922

I could suggest you to rename such fields in GUI tool - dbForge Studio for MySQL (free trial edition):

Just select the field you want to rename in the Database Explorer, click on Refactoring->Rename command, enter new name in openned window, and press OK, it will rename the field and recreate all foreign keys automatically.

Upvotes: 3

lqez
lqez

Reputation: 3008

Even with SET foreign_key_checks = 0, you can't alter the type of the constraint column. From MySQL doc : http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

However, even if foreign_key_checks = 0, InnoDB does not permit the creation of a foreign key constraint where a column references a nonmatching column type.

So, I agree with the comment of Devart. Just drop it and create it again.

Upvotes: 9

Related Questions