mysql 外键唯一,关于mysql：外键可以引用非唯一索引吗？

我认为外键意味着单行必须引用单行，但是我正在查看某些表，但事实并非如此。 表1在表2的列2上有一个具有外键约束的列1，但是在表2中有很多记录在列2中具有相同的值。 在column2上也有非唯一索引。 这是什么意思？ 外键约束是否仅表示至少一条记录必须存在，且在正确的列中具有正确的值？ 我认为这意味着必须有一个这样的记录(不确定空值是否适合图片，但目前我对此不太担心)。

更新：显然，此行为特定于MySQL，这是我所使用的，但我没有在最初的问题中提及它。

您在谈论哪个数据库？

数据库的类型重要吗？ 我本以为这是一条通用的SQL规则。

从MySQL文档中：

InnoDB allows a foreign key constraint to reference a non-unique key. This is an InnoDB extension to standard SQL.

但是，出于实际原因，要避免在引用表的非唯一列上使用外键。也就是说，在这种情况下" ON DELETE CASCADE"的语义应该是什么？

该文档进一步建议：

The handling of foreign key references to nonunique keys or keys that contain NULL values is not well defined (...) You are advised to use foreign keys that reference only UNIQUE (including PRIMARY) and NOT NULL keys.

感谢您引用MySQLs文档。我没有在问题中特别提到MySQL，但这就是我所使用的。我将更新我的问题以反映这一点。

MySQL的5.1版文档更进一步："对非唯一键[sic]或包含NULL值的键的外键引用的定义不明确。...建议您使用仅引用UNIQUE和NOT NULL键的外键。"

通过引用文献的链接，这个答案会更好。

级联删除是一个问题，因为级联效果是在删除第一个孩子而不是最后一个孩子时发生的。

您的分析是正确的；键不必是唯一的，约束将作用于匹配行的集合。通常这不是有用的行为，但是可能会出现您想要的情况。

感谢您的帮助，混沌。但这确实使我对外键的理解混乱了……

刚开始，我对"您的分析"应该指的东西有些困惑。对于其他读者，我认为您的意思是，只要有至少一行，外键就可以引用多行。

您对外键的理解被正确地弄乱了，因为这是一个虚假的答案。即使找到了可以执行此操作的数据库(我没有测试MySQL)，实现这种外键关系的数据库设计也非常非常糟糕。

@cdonner：好吧，我会以为正确是对我的回答是虚假的某种辩护。我应该怎么做，请撒谎告诉那个人您不能这样做，因为我认为这通常是个坏主意(我愿意这样做)？是的，您可以在mysql中完成。

@allyourcode：确实，您不应该放弃以前对外键的理解。它对99％的非病理病例正确。

@chaos：我的立场是正确的-至少在某种程度上。我对MySQL知之甚少。您能举一个有用的例子吗？

发生这种情况时，通常意味着两个外键相互链接。

通常，包含键作为主键的表甚至不在架构中。

示例：两个表COLLEGES和STUDENTS都包含一个名为ZIPCODE的列。

如果我们快速检查一下

SELECT * FROM COLLEGES JOIN STUDENTS ON COLLEGES.ZIPCODE = STUDENTS.ZIPCODE

我们可能会发现这种关系是多对多的。如果我们的架构中有一个名为ZIPCODES的表，且具有主键ZIPCODE，那么显然发生了什么。

但是我们的架构没有这样的表。但是，仅仅因为我们的模式没有这样的表并不意味着不存在这样的数据。在USPO土地上的某个地方，有一张桌子。而且，即使我们不认可该表，但COLLEGES.ZIPCODE和STUDENTS.ZIPCODE都是对该表的引用。

与构建数据库的实践相比，这更多地与数据的哲学有关，但是它巧妙地说明了一些基本知识：数据具有我们发现的特征，而不仅仅是我们发明的特征。当然，我们发现的可能是其他人发明的。 ZIPCODE肯定是这种情况。

您因为"两个外键相互链接"而迷失了我。我真的不知道那意味着什么。

是的，您基本上可以为任何表中的任何列创建外键。不过，大多数情况下，您会将它们创建为主键。

如果您确实使用不指向主键的外键，则出于性能考虑，您可能还想为被引用的列创建一个(非唯一的)索引。

取决于您使用的RDBMS。我认为有些人暗中为您做到这一点，或使用其他技巧。 RTM。

MySQL(InnoDB)在引用列和被引用列上需要一个(左侧)索引。 JADP。

PostgreSQL也拒绝了这一点(无论如何，即使这是可能的，也不意味着这是个好主意)：

essais=> CREATE TABLE Cities (name TEXT, country TEXT);

CREATE TABLE

essais=> INSERT INTO Cities VALUES ('Syracuse', 'USA');

INSERT 0 1

essais=> INSERT INTO Cities VALUES ('Syracuse', 'Greece');

INSERT 0 1

essais=> INSERT INTO Cities VALUES ('Paris', 'France');

INSERT 0 1

essais=> INSERT INTO Cities VALUES ('Aramits', 'France');

INSERT 0 1

essais=> INSERT INTO Cities VALUES ('Paris', 'USA');

INSERT 0 1

essais=> CREATE TABLE People (name TEXT, city TEXT REFERENCES Cities(name));

ERROR:  there is no unique constraint matching given keys for referenced table"cities"

没有提及。而且，由于MySQL在检查，约束和控件方面特别草率，因此使用MySQL可以对非唯一键进行完整性引用也就不足为奇了。

我认为这与马虎无关。 Postgresql在关键字MATCH PARTIAL中保留了此功能，但尚未实现afaik。

死灵法师。

正如其他人已经说过的那样，您不应将非唯一键称为外键。

但是，您可以做的(没有删除级联的危险)可以添加检查约束(至少在MS-SQL中)。

这与外键并不完全相同，但是至少它将防止插入无效/孤立/无效的数据。

请参阅此处以供参考(您必须将MS-SQL代码移植到MySQL语法)：

非主键的外键

编辑：

根据Mysql CHECK Constraint的说法，搜索下降的原因时，MySQL并不真正支持CHECK约束。

您可以出于兼容性原因在DDL查询中定义它们，但是它们只会被忽略...

但是，正如那里提到的，您可以创建一个BEFORE INSERT和BEFORE UPDATE触发器，当不满足数据要求时，将引发错误，这基本上是相同的事情，只是它更大了。

关于这个问题：

I thought a foreign key meant that a single row must reference a

single row, but I'm looking at some tables where this is definitely

not the case.

在任何理智的RDBMS中，都是如此。

在MySQL中这是可能的事实仅仅是

MySQL是一个疯狂的RDBMS。

它可能很快，但是牺牲速度上的参照完整性和数据质量并不是我对quality-rdbms的想法。

实际上，如果它不符合ACID，则根本不是一个(正确运行的)RDBMS。

InnoDB非常接近于符合ACID。恕我直言，足够接近大多数应用程序。当您确实不需要兼容ACID的数据库时，它可以通过MyISAM来提高性能。这是一个高质量的rdbms，但是需要一个好的DBA才能知道何时是最佳选择。

我们在谈论什么数据库？在SQL 2005中，我无法创建引用没有唯一约束(主键或其他)的列的外键约束。

create table t1

(

id int identity,

fk int

);

create table t2

(

id int identity,

);

CREATE NONCLUSTERED INDEX [IX_t2] ON [t2]

(

[id] ASC

);

ALTER TABLE t1 with NOCHECK

ADD CONSTRAINT FK_t2 FOREIGN KEY (fk)

REFERENCES t2 (id) ;

Msg 1776, Level 16, State 0, Line 1

There are no primary or candidate keys in the referenced table 't2'

that match the referencing column list in the foreign key 'FK_t2'.

Msg 1750, Level 16, State 0, Line 1

Could not create constraint. See previous errors.

如果您实际上可以执行此操作，则将有效地建立多对多关系，而没有中间表是不可能的。我真的很想听到更多有关此的信息...

以及与此相关的问题和答案。

@allyourcode：您是否介意共享数据库中具有m：n外键关系的这两个表？