SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    Sweden
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    I get errno: 150 eventhough SET FOREIGN_KEY_CHECKS=0;

    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)?

  2. #2
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    Sweden
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, was supposed to be tinyint(5)!

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    Sweden
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I solved it by
    1. Dropping the Foreign Key constraints for all tables affected.
    2. Altering my tables.
    3. Re-inserting my foreign key constraints.


Bookmarks

Posting Permissions

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