MYSQL：外键自动索引化

Question

下面是一组命令：

alter table opportunities add column ownerId int null;
alter table opportunities add foreign key (ownerId) references users (id) on delete set null on update cascade;

产生如下错误：

表taous /＃sql-318c_27的外键约束中存在错误： 表“ taous”。“＃sql-318c_27”中没有索引，其中列作为第一列出现。 约束条件： foreign key（ownerId）references users（id）on delete set null on update cascade ;

因此我理解引用列上缺少索引。

现在，foreign key constraints的mysql文档说明：

InnoDB需要对外键和引用键创建索引，以便外键检查可以快速进行而不需要扫描表。 在引用表中，必须有一个索引，其中外键列按相同顺序列出为第一个列。如果不存在这样的索引，则会自动在引用表上创建此类索引。（与某些较旧版本形成对比，在这些版本中必须显式创建索引或失败创建外键约束）index_name，如果给定，将按先前描述的方式使用。

我运行的是mysql 5.1（xampp，windows）。因此，我期望在创建外键时自动索引列。 有任何想法为什么自动索引可能会失败？ 还有一点： 只有当通过PDO（一个数据库更新工具）运行sql命令时才会出现错误。直接在mysql控制台中运行时没有问题。 谢谢 Gidi

- shealtiel

---

出于好奇，您通过PDO连接的用户是否实际具有创建索引的权限？ - Eric Petroelje

一个用户可以被允许创建表但不允许创建索引吗？无论如何，我使用root连接。 - shealtiel

回复：创建表但不包括索引：是的，那些需要单独的权限。请注意，主机名是用户身份的一部分；'root'@'localhost'与'root'@'%'是不同的帐户。根据您连接的位置，您可能会获得不同的权限。尝试使用SHOW GRANTS命令以及SELECT CURRENT_USER()获取完整的用户名。 - outis

只是为了确保不会错过显而易见的事情：users表是否已经定义了一个主键，而且该主键正好由列id组成？ - user330315

@a_horse_with_no_name，感谢您对此进行质疑，用户ID是它的外键（唯一的外键）。 - shealtiel

@gidireich：用户必须将ID作为主键而不是外键。如果它不是主键，则无法创建引用它的外键。 - user330315

Answer 1

这个错误的另一个原因可能是引用表和被引用表列的类型不匹配。

例如，如果 opportunities 表的 ownerId 列是 INT 类型，但 users 表的 Id 列不是 INT 类型。

但这并不能解释为什么它可以在控制台中工作而不是在 PDO 中，但也许能帮助其他人。

- Markoorn

Answer 2

我的理解是自动索引在引用表上而不是被引用表上。

在您的情况下，自动索引将在opportunities中的ownerId添加索引...但实际上它并不需要，因为您已经这样做了。

我不太明白"taous"."#sql-318c_27"来自哪里，但假设它与users表有关，我认为错误是因为id没有建立索引。

- Dancrumb