Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2011
    Posts
    2

    Unanswered: I get errno: 150 eventhough SET FOREIGN_KEY_CHECKS=0;

    I have a table with foreign keys. I want to change tinyint(5) 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)?
    Last edited by lagu2653; 11-07-11 at 16:49.

  2. #2
    Join Date
    Jun 2007
    Posts
    197
    Set foreign_key_checks=0 is used for insert,update,delete
    not for Alter

    If you want to change Parent table Column data type do below steps

    1- First Identify child tables of that particular parent table
    2- Delete the foreign keys associated to child table to that parent table
    3- Modify the child table column data type
    4- Modify the parent table column data type
    5- Apply again foreign keys

    Regards
    Senior MySQL DBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •