mysql - 如何创建具有复合外键的表

Question

我使用复合主键创建了主表。

父表结构如下:

CREATE TABLE `taskcategory` (
  `SiteID` int(10) unsigned NOT NULL DEFAULT 1,
  `TaskID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `TaskName` varchar(45) DEFAULT '',
  `TaskDescription` varchar(45) DEFAULT '',
  `IsInbuild` int(11) DEFAULT '1',
  PRIMARY KEY (`TaskID`,`SiteID`)
);

当我尝试使用上述父表引用创建带有外键的表时，我收到“无法创建表错误没有 150”错误。帮我做到这一点。

子表结构如下:

CREATE TABLE taskdetails (`SiteID` int(10) unsigned NOT NULL DEFAULT '1',
  `TaskID` int(10) unsigned NOT NULL DEFAULT '0',
  `SubtaskID` int(10) unsigned NOT NULL,
  `ScriptName` varchar(255) DEFAULT '',
  `FunctionName` varchar(255) DEFAULT '',
  `ButtonName` varchar(255) DEFAULT '',
  `IsInbuild` int(10) unsigned DEFAULT '1',
  `Description` varchar(255) DEFAULT '',
  PRIMARY KEY (`SubtaskID`,`TaskID`,`SiteID`),
                            INDEX (siteid, taskid),
                            FOREIGN KEY (siteid, taskid)
                              REFERENCES taskcategory(siteid, taskid)
                              ON UPDATE CASCADE ON DELETE CASCADE
                            ) ENGINE=INNODB;

帮我解决一下。

Answer 1

来自manual :

InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

因此，当您在父表中添加索引时，它会起作用(是的，我测试了它):

CREATE TABLE `taskcategory` (
  `SiteID` int(10) unsigned NOT NULL DEFAULT 1,
  `TaskID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `TaskName` varchar(45) DEFAULT '',
  `TaskDescription` varchar(45) DEFAULT '',
  `IsInbuild` int(11) DEFAULT '1',
  PRIMARY KEY (`TaskID`,`SiteID`)
  , INDEX (SiteID, TaskID)
) ENGINE=INNODB;

CREATE TABLE taskdetails (`SiteID` int(10) unsigned NOT NULL DEFAULT '1',
  `TaskID` int(10) unsigned NOT NULL DEFAULT '0',
  `SubtaskID` int(10) unsigned NOT NULL,
  `ScriptName` varchar(255) DEFAULT '',
  `FunctionName` varchar(255) DEFAULT '',
  `ButtonName` varchar(255) DEFAULT '',
  `IsInbuild` int(10) unsigned DEFAULT '1',
  `Description` varchar(255) DEFAULT '',
  PRIMARY KEY (`SubtaskID`,`TaskID`,`SiteID`)
                            ,INDEX (SiteID, TaskID)
                            ,FOREIGN KEY (SiteID, TaskID)
                              REFERENCES taskcategory(SiteID, TaskID)
                              ON UPDATE CASCADE ON DELETE CASCADE
                            ) ENGINE=INNODB;

这些列上已经有了主键(这意味着存在隐式索引)，但是列的顺序很重要!