mysql - 尝试制作这些表格时遇到麻烦

Question

大家好，我在尝试创建这 5 个表时遇到了问题。据我所知，这个问题必须与冠军有关。每次我尝试导入 sql 文件时，都会收到 errno 150 ，指出无法创建冠军表。

澄清一下，一个冠军只能有一个派系，但一个派系可以由多个冠军组成。角色和亲和性的关系相同。感谢您提前提供的任何帮助。

DROP TABLE IF EXISTS `champion`;
DROP TABLE IF EXISTS `role`;
DROP TABLE IF EXISTS `build`;
DROP TABLE IF EXISTS `faction`;
DROP TABLE IF EXISTS `build_type`;
DROP TABLE IF EXISTS `affinity`;


-- roles table 
-- Table consisting of the roles various champions play

CREATE TABLE `role` (
        `role_id` int(11) NOT NULL AUTO_INCREMENT,
        `role_name` varchar(255) NOT NULL,
        PRIMARY KEY (`role_id`)
)ENGINE=InnoDB;

-- build table
CREATE TABLE `build` (
        `build_id` int(11) NOT NULL AUTO_INCREMENT,
        `build_name` varchar(255) NOT NULL,
        PRIMARY KEY (`build_id`),
        UNIQUE KEY (`build_name`)
)ENGINE=InnoDB;

-- faction table 
-- Table consisting of the faction each champion belongs to

CREATE TABLE `faction`(
        `faction_id` int(11) NOT NULL AUTO_INCREMENT,
        `faction_name` varchar(255) NOT NULL, 
        PRIMARY KEY (`faction_id`)
)ENGINE=InnoDB;


-- Champions table 
-- Table consisting of various champions in League of Legends

CREATE TABLE `champion`(
        `champion_id` int(11) NOT NULL AUTO_INCREMENT,
        `champion_name` varchar(255) NOT NULL,
        `f_id` int(11) NOT NULL, 
         `r_id` int(11) NOT NULL,
        `a_id` int(11) NOT NULL, 
        PRIMARY KEY (`champion_id`),
        FOREIGN KEY (`r_id`) REFERENCES `role` (`role_id`)
                ON DELETE CASCADE 
                ON UPDATE CASCADE, 
        FOREIGN KEY (`f_id`) REFERENCES `faction` (`faction_id`)
                ON DELETE CASCADE 
                ON UPDATE CASCADE,
        FOREIGN KEY (`a_id`) REFERENCES `affinity` (`affinity_id`)
                ON DELETE CASCADE 
                ON UPDATE CASCADE,
        UNIQUE KEY (`champion_name`)
)ENGINE=InnoDB;

-- build_type table 
CREATE TABLE `build_type`(
        `cid` int(11) NOT NULL, 
        `bid` int(11) NOT NULL, 
        PRIMARY KEY (`cid`, `bid`),
        FOREIGN KEY (`cid`) REFERENCES `champion` (`champion_id`)
                ON DELETE CASCADE 
                ON UPDATE CASCADE,
        FOREIGN KEY (`bid`) REFERENCES `build` (`build_id`)
                ON DELETE CASCADE 
                ON UPDATE CASCADE
)ENGINE=InnoDB;


-- affinity table 
-- Table consisting of the affinity a certain champion synergizes
-- with

CREATE TABLE `affinity`(
        `affinity_id` int(11) NOT NULL AUTO_INCREMENT, 
        `affinity_name` varchar(255) NOT NULL,
        PRIMARY KEY (`affinity_id`)
)ENGINE=InnoDB;

Answer 1

这是因为 champion 表中有一个列 a_id 引用了尚不存在的表 affinity。要解决此问题，您必须首先在 champion 表

之前创建 affinity 表

这个

CREATE TABLE `affinity`(
        `affinity_id` int(11) NOT NULL AUTO_INCREMENT, 
        `affinity_name` varchar(255) NOT NULL,
        PRIMARY KEY (`affinity_id`)
)ENGINE=InnoDB;

必须先于

CREATE TABLE `champion`(
        `champion_id` int(11) NOT NULL AUTO_INCREMENT,
        `champion_name` varchar(255) NOT NULL,
        `f_id` int(11) NOT NULL, 
         `r_id` int(11) NOT NULL,
        `a_id` int(11) NOT NULL, 
        PRIMARY KEY (`champion_id`),
        FOREIGN KEY (`r_id`) REFERENCES `role` (`role_id`)
                ON DELETE CASCADE 
                ON UPDATE CASCADE, 
        FOREIGN KEY (`f_id`) REFERENCES `faction` (`faction_id`)
                ON DELETE CASCADE 
                ON UPDATE CASCADE,
        FOREIGN KEY (`a_id`) REFERENCES `affinity` (`affinity_id`)
                ON DELETE CASCADE 
                ON UPDATE CASCADE,
        UNIQUE KEY (`champion_name`)
)ENGINE=InnoDB;