Mysql和FK的问题

Question

我在这里的一些桌子上遇到了麻烦。

我有这张表：

CREATE TABLE `smenuitem` (
    `nome` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `url` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `tipo` CHAR(4) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `ordemmenu` INT(10) NULL DEFAULT NULL,
    `codparent` INT(10) UNSIGNED NOT NULL,
    `codmenuitem` INT(10) UNSIGNED NOT NULL,
    `codmodulo` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`codmodulo`, `codmenuitem`, `codmenuitem2`),
    CONSTRAINT `FK_smenuitem_smodulos` FOREIGN KEY (`codmodulo`) REFERENCES `smodulos` (`codmodulo`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT

第二张表：

CREATE TABLE `smenuitememp` (
    `codempresa` INT(10) UNSIGNED NOT NULL,
    `codmodulo` INT(10) UNSIGNED NOT NULL,
    `codmenuitem` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`codmenuitem`, `codempresa`, `codmodulo`)
)
COLLATE='utf8_unicode_ci'

我的问题是我需要在 codmenuitem 之间建立一个 FK 我有这个 sql 命令导致错误：

ALTER TABLE `smenuitememp`  ADD CONSTRAINT `FK_smenuitememp_smenuitem` FOREIGN KEY (`codmenuitem`) REFERENCES `smenuitem` (`codmenuitem`);

当我尝试执行它时返回此错误：

有人有主意吗？

---

更新...我试图解决问题，并得到了一个新问题...T_T

CREATE TABLE `smenuitem` (
    `nome` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `url` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `tipo` CHAR(4) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `ordemmenu` INT(10) NULL DEFAULT NULL,
    `codparent` INT(10) UNSIGNED NOT NULL,
    `codmenuitem` INT(10) UNSIGNED NOT NULL,
    `codmodulo` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`codmodulo`, `codmenuitem`),
    INDEX `codmenuitem` (`codmenuitem`),
    CONSTRAINT `FK_smenuitem_smodulos` FOREIGN KEY (`codmodulo`) REFERENCES `smodulos` (`codmodulo`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT

我解决了在主表创建索引的问题。但我不知道为什么没有这个索引我就会遇到麻烦。如果有人能问我，我将不胜感激！

Answer 1

外键列必须引用包含主键最左侧前缀或父表中的唯一键的列。

换句话说，以下示例在 InnoDB 中有效：

CREATE TABLE Foo ( a INT, b INT, c INT, PRIMARY KEY (a,b,c) );
CREATE TABLE Bar ( x INT, y INT );

ALTER TABLE Bar ADD FOREIGN KEY (x,y) REFERENCES Foo(b,c); -- WRONG

ALTER TABLE Bar ADD FOREIGN KEY (x,y) REFERENCES Foo(a,c); -- WRONG

ALTER TABLE Bar ADD FOREIGN KEY (x,y) REFERENCES Foo(a,b); -- RIGHT

ALTER TABLE Bar ADD FOREIGN KEY (x) REFERENCES Foo(b); -- WRONG

ALTER TABLE Bar ADD FOREIGN KEY (x) REFERENCES Foo(a); -- RIGHT

您收到错误，因为您尝试执行 (x) 引用 Foo(b) 的等效操作。
您的列 codmenuitem 是父级主键中三列中的第二列。

如果 smenuitem.codemenuitem 引用 smenuitem.codmodulo 就会起作用，因为该列是父表主键中最左边的列。

---

关于您的后续问题：

请记住外键的工作方式。每次在子表中插入或更新行时，都需要在父表中查找行以验证引用列中是否存在该值。如果该列没有索引，则必须执行表扫描才能实现此查找，假设您的父表增长，这将非常昂贵。

如果您尝试根据多列索引的中间列查找行，则索引对您没有帮助。打个比方，这就像在电话簿中搜索所有具有特定中间名的人一样。

标准 ANSI SQL 要求引用的列是 PRIMARY KEY 或 UNIQUE KEY 的一部分，并且要求外键列与父级中主约束或唯一约束的所有列匹配。

但InnoDB 更加宽松。它仍然要求对父表中的引用列进行索引，以便查找能够高效，并且引用列位于索引的最左边。但非唯一索引是可以的；允许外键引用它。

这可能会导致奇怪的情况，例如子行引用父级中的多行，但预计您将处理此类异常情况。

---

我觉得有必要强调最后一点。如果您为父级中的非唯一索引列定义外键，您将获得异常数据。这可能会导致您的查询在进行联接时多次报告行。你不应该使用 InnoDB 的这种行为；您应该只为唯一的父列定义外键。