drsxobip501258
2013-06-20 12:57 阅读 35
已采纳

MySQL:errno(150),无法创建表(与外键相关)

I want to use cascading in my project to simplify certain processes. I created these two queries as well as a couple more but all child tables throw the same error on execution. They worked when I used MyISAM as the engine, but on further testing and research I figured that it does not support cascading, so I switched the engine to InnoDB which triggered these errors. I've looked at a couple of forums and threads with the same problem but I just can't seem to figure out where the actual problem lies. Can someone help?

ParentTable:

CREATE TABLE IF NOT EXISTS `branches` ( `branch_id` int(11) NOT NULL AUTO_INCREMENT, `key` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `short_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `city` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `timezone` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, UNIQUE(`key`), PRIMARY KEY (`branch_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

Child Table:

CREATE TABLE IF NOT EXISTS `files` ( `file_id` int(11) NOT NULL AUTO_INCREMENT, `branch_id` int(11) NOT NULL, `path` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `use_google_analytics` BOOLEAN NOT NULL, FOREIGN KEY(`branch_id`) REFERENCES `branches`(`branch_id`) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (`file_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

Error:

1005: Can't create table 'files' (errno: 150)
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    duangan2307 duangan2307 2013-06-20 12:59

    You defined your foreign key

    ON DELETE CASCADE 
    

    But your branch_id is defined as NOT NULL. That won't work.

    You want your branch_id never be null but then the foreign key related data gets removed you want it set to NULL with the CASCADE option.

    So either change removd the ON DELETE CASCADE or allow NULL in the foreign key. This works

    SQLFiddle demo

    点赞 评论 复制链接分享

相关推荐