blocks|key|1062385|text|来自MySQL+documentation|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1062386|1062387|+InnoDB允许外键约束引用非唯一键。这是对标准SQL的InnoDB扩展。|blockquote|1062388|1062389|1062390|但是，在引用表的非唯一列上避免使用外键是有实际原因的。也就是说，在这种情况下，"ON+DELETE+CASCADE“的语义应该是什么？|1062391|The+documentation+further+advises|1062392|1062393|对非唯一键或包含NULL值的键的外键引用的处理未定义良好(...)建议您使用仅引用唯一键(包括主键)而不引用空键的外键。|1062394|1062395|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html|1|http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html^0|2|J|0|0|0|0|0|0|0|0|X|1|0|0|0|0^^$0|@$1|2|3|4|5|6|7|11|8|@]|9|@$A|12|B|13|1|14]]|C|$]]|$1|D|3|-4|5|6|7|15|8|@]|9|@]|C|$]]|$1|E|3|F|5|G|7|16|8|@]|9|@]|C|$]]|$1|H|3|-4|5|6|7|17|8|@]|9|@]|C|$]]|$1|I|3|-4|5|6|7|18|8|@]|9|@]|C|$]]|$1|J|3|K|5|6|7|19|8|@]|9|@]|C|$]]|$1|L|3|M|5|6|7|1A|8|@]|9|@$A|1B|B|1C|1|1D]]|C|$]]|$1|N|3|-4|5|6|7|1E|8|@]|9|@]|C|$]]|$1|O|3|P|5|G|7|1F|8|@]|9|@]|C|$]]|$1|Q|3|-4|5|6|7|1G|8|@]|9|@]|C|$]]|$1|R|3|-4|5|6|7|1H|8|@]|9|@]|C|$]]]|S|$T|$5|U|V|W|C|$X|Y]]|Z|$5|U|V|W|C|$X|10]]]]

From <a href="http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html" rel="noreferrer">MySQL documentation</a>:

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

However, there is a pratical reason to avoid foreign keys on non-unique columns of referenced table. That is, what should be the semantic of "ON DELETE CASCADE" in that case?

<a href="http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html" rel="noreferrer">The documentation further advises</a>:

<blockquote>
 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.
</blockquote>

blocks|key|3607723|text|您的分析是正确的；键不必是唯一的，并且约束将作用于匹配的行集。这通常不是一个有用的行为，但在您想要它的情况下也会出现这种情况。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3607724|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Your analysis is correct; the keys don't have to be unique, and constraints will act on the set of matching rows. Not usually a useful behavior, but situations can come up where it's what you want.

blocks|key|4454614|text|是的，基本上您可以为任何表中的任何列创建外键。不过，大多数情况下，您会将它们创建为主键。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4454615|如果您确实使用了不指向主键的外键，那么出于性能的考虑，您可能还希望为被引用的列创建一个(非唯一)索引。|4454616|这取决于您正在使用的RDBMS。我认为有些人隐含地为你做了这件事，或者使用了一些其他的技巧。RTM。|4454617|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Yes, you can create foreign keys to basically any column(s) in any table. Most times you'll create them to the primary key, though.

If you do use foreign keys that don't point to a primary key, you might also want to create a (non-unique) index to the column(s) being referenced for the sake of performance.

Depends on the RDBMS you're using. I think some do this for you implicitly, or use some other tricks. RTM.

blocks|key|1062310|text|当发生这种情况时，通常意味着两个外键正在相互链接。通常，包含作为主键的键的表甚至不在模式中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1062311|示例：“学院”和“学生”这两个表都包含一个名为ZIPCODE的列。|1062312|如果我们快速检查一下|1062313|SELECT+*+FROM+COLLEGES+JOIN+STUDENTS+ON+COLLEGES.ZIPCODE+=+STUDENTS.ZIPCODE|code-block|syntax|javascript|1062314|我们可能会发现这种关系是多对多的。如果我们的模式有一个名为ZIPCODE的表，其中包含主键ZIPCODE，那么实际发生的事情就很明显了。|1062315|但是我们的模式没有这样的表。然而，仅仅因为我们的模式没有这样的表并不意味着这样的数据不存在。在USPO的某个地方，就有这样一张桌子。COLLEGES.ZIPCODE和STUDENTS.ZIPCODE都是对该表的引用，即使我们不承认这一点。|1062316|这更多地与数据哲学有关，而不是建立数据库的实践，但它巧妙地说明了一些基本的东西:数据具有我们发现的特征，而不仅仅是我们发明的特征。当然，我们发现的东西可能是别人发明的。ZIPCODE当然就是这种情况。|1062317|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|U|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|V|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

When this happens, it usually means that two foreign keys are being linked to each other.
Often the table that would contain the key as a primary key isn't even in the schema.

Example: Two tables, COLLEGES and STUDENTS, both contain a column called ZIPCODE.

If we do a quick check on 

<pre><code>SELECT * FROM COLLEGES JOIN STUDENTS ON COLLEGES.ZIPCODE = STUDENTS.ZIPCODE
</code></pre>

We might discover that the relationship is many to many. If our schema had a table called ZIPCODES, with primary key ZIPCODE, it would be obvious what's really going on. 

But our schema has no such table. Just because our schema has no such table doesn't mean that such data doesn't exist, however. somewhere, out in USPO land, there is just such a table. And both COLLEGES.ZIPCODE and STUDENTS.ZIPCODE are references to that table, even if we don't acknowledge it.

This has more to do with the philosophy of data than the practice of building databases, but it neatly illustrates something fundamental: the data has characteristics that we discover, and not only characteristics that we invent. Of course, what we discover could be what somebody else invented. That's certainly the case with ZIPCODE.

blocks|key|3607895|text|PostgreSQL也拒绝了这一点(无论如何，即使它是possible，也不意味着它是一个好主意)：|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|3607896|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"|code-block|syntax|javascript|3607897|entityMap^0|R|8|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

PostgreSQL also refuses this (anyway, even if it is possible, it does not mean it is a good idea):

<pre><code>essais=&gt; CREATE TABLE Cities (name TEXT, country TEXT);
CREATE TABLE
essais=&gt; INSERT INTO Cities VALUES ('Syracuse', 'USA');
INSERT 0 1
essais=&gt; INSERT INTO Cities VALUES ('Syracuse', 'Greece');
INSERT 0 1
essais=&gt; INSERT INTO Cities VALUES ('Paris', 'France');
INSERT 0 1
essais=&gt; INSERT INTO Cities VALUES ('Aramits', 'France');
INSERT 0 1
essais=&gt; INSERT INTO Cities VALUES ('Paris', 'USA');
INSERT 0 1

essais=&gt; CREATE TABLE People (name TEXT, city TEXT REFERENCES Cities(name));
ERROR: there is no unique constraint matching given keys for referenced table "cities"
</code></pre>

blocks|key|3608014|text|亡灵法术。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3608015|正如其他人已经说过的，您不应该将非唯一键引用为外键。|3608016|但是您能做的(没有delete+cascade危险)是添加一个检查约束(至少在MS-SQL中)。|3608017|这与外键不完全相同，但至少可以防止插入无效/孤立/死数据。|3608018|查看此处作为参考(您必须将MS-SQL代码移植到MySQL语法)：|3608019|Foreign+Key+to+non-primary+key|offset|length|3608020|编辑：|style|BOLD|3608021|根据Mysql+CHECK+Constraint的说法，在寻找否决的原因时，MySQL并不真正支持检查约束。|3608022|出于兼容性的原因，您可以在DDL查询中定义它们，但它们会被忽略...|3608023|但是正如这里提到的，您可以创建一个BEFORE+INSERT和BEFORE+UPDATE触发器，当数据的要求不满足时，它将抛出一个错误，这基本上是一样的，只是它是一个更大的混乱。|CODE|3608024|有关问题如下：|3608025|3608026|我认为外键意味着单行必须引用单行，但我正在查看一些表，在这些表中，情况肯定不是这样。|blockquote|3608027|3608028|3608029|在任何正常的RDBMS中，这都是正确的。|3608030|这在MySQL中是可能的，这只是另一个原因|3608031|MySQL是一个优秀的关系型数据库管理系统。|3608032|它可能很快，但在速度的祭坛上牺牲引用完整性和数据质量并不是我对高质量rdbms的看法。|3608033|事实上，如果它不兼容ACID，那么它根本就不是一个真正的(正常运行的)+RDBMS。|3608034|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/18435065/foreign-key-to-non-primary-key/26427936#26427936|1|https://stackoverflow.com/questions/14247655/mysql-check-constraint^0|0|0|0|0|0|0|U|0|0|0|3|0|2|M|1|0|0|H|D|V|D|0|0|0|0|0|0|0|0|0|L|0|0|0^^$0|@$1|2|3|4|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1R|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|1S|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|1T|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|1U|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|1V|8|@]|9|@$L|1W|M|1X|1|1Y]]|A|$]]|$1|N|3|O|5|6|7|1Z|8|@$L|20|M|21|P|Q]]|9|@]|A|$]]|$1|R|3|S|5|6|7|22|8|@]|9|@$L|23|M|24|1|25]]|A|$]]|$1|T|3|U|5|6|7|26|8|@]|9|@]|A|$]]|$1|V|3|W|5|6|7|27|8|@$L|28|M|29|P|X]|$L|2A|M|2B|P|X]]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|2C|8|@]|9|@]|A|$]]|$1|10|3|-4|5|6|7|2D|8|@]|9|@]|A|$]]|$1|11|3|12|5|13|7|2E|8|@]|9|@]|A|$]]|$1|14|3|-4|5|6|7|2F|8|@]|9|@]|A|$]]|$1|15|3|-4|5|6|7|2G|8|@]|9|@]|A|$]]|$1|16|3|17|5|6|7|2H|8|@]|9|@]|A|$]]|$1|18|3|19|5|6|7|2I|8|@]|9|@]|A|$]]|$1|1A|3|1B|5|6|7|2J|8|@$L|2K|M|2L|P|Q]]|9|@]|A|$]]|$1|1C|3|1D|5|6|7|2M|8|@]|9|@]|A|$]]|$1|1E|3|1F|5|6|7|2N|8|@]|9|@]|A|$]]|$1|1G|3|-4|5|6|7|2O|8|@]|9|@]|A|$]]]|1H|$1I|$5|1J|1K|1L|A|$1M|1N]]|1O|$5|1J|1K|1L|A|$1M|1P]]]]

Necromancing. 
As others already said, you shouldn't reference a non-unique key as foreign key. 
But what you can do instead (without delete cascade danger) is adding a check-constraint (at least in MS-SQL). 
That's not exactly the same as a foreign key, but at least it will prevent the insertion of invalid/orphaned/dead data.

See here for reference (you'll have to port the MS-SQL code to MySQL syntax): 
<a href="https://stackoverflow.com/questions/18435065/foreign-key-to-non-primary-key/26427936#26427936">Foreign Key to non-primary key</a>

Edit: 
Searching for the reasons for the downvote, according to <a href="https://stackoverflow.com/questions/14247655/mysql-check-constraint">Mysql CHECK Constraint</a>, MySQL doesn't really support CHECK constraints. 
You can define them in your DDL query for compatibility reasons, but they are just ignored...

But as mentioned there, you can create a <code>BEFORE INSERT</code> and <code>BEFORE UPDATE</code> trigger, which will throw an error when the requirements of the data are not met, which is basically the same thing, except that it's an even bigger mess.

As to the question:

<blockquote>
 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.
</blockquote>

In any sane RDBMS, this is true. 
The fact that this is possible in MySQL is just one more reason why 
MySQL is an in-sane RDBMS. 
It may be fast, but sacrificing referential integrity and data quality on the altar of speed is not my idea of a quality-rdbms. 
In fact, if it's not ACID-compliant, it's not really a (correctly functioning) RDBMS at all.

blocks|key|3607815|text|我们谈论的是什么数据库？在SQL2005中，不能创建引用没有唯一约束(主键或其他约束)的列的外键约束。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3607816|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.|code-block|syntax|javascript|3607817|如果您真的可以做到这一点，您将有效地拥有一个多对多关系，这在没有中间表的情况下是不可能的。我真的很有兴趣听到更多关于这个的..。|3607818|请参阅this+related+question和answers。|offset|length|3607819|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/278982/are-foreign-keys-indexed-automatically-in-sql-server^0|0|0|0|3|L|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|X|8|@]|9|@$K|Y|L|Z|1|10]]|A|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

What database are we talking about? In SQL 2005, I cannot create a foreign key constraint that references a column that does not have a unique constraint (primary key or otherwise).

<pre><code>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.
</code></pre>

If you could actually do this, you would effectively have a many-to-many relationship, which is not possible without an intermediate table. I would be truly interested in hearing more about this ...

See <a href="https://stackoverflow.com/questions/278982/are-foreign-keys-indexed-automatically-in-sql-server">this related question</a> and answers as well.

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. Table1 has column1 with a foreign key constraint on column2 in table2, BUT there are many records in table2 with the same value in column2. There's also non-unique index on column2. What does this mean? Does a foreign key constraint simply mean that at least one record must exist with the right values in the right columns? I thought it meant there must be exactly one such record (not sure how nulls fit in to the picture, but I'm less concerned about that at the moment).

update: Apparently, this behavior is specific to MySQL, which is what I was using, but I didn't mention it in my original question.

Can a foreign key reference a non-unique index?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我认为外键意味着单行必须引用单行，但我正在查看一些表，在这些表中，情况肯定不是这样的。Table1的column1对table2中的column2具有外键约束，但是在table2中有许多记录在column2中具有相同的值。在column2上也有非唯一索引。这是什么意思？外键约束是否仅仅意味着必须至少有一条记录在正确的列中具有正确的值？我认为这意味着必须有一个这样的记录(不确定null值如何适合图片

问外键可以引用非唯一索引吗？
EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问外键可以引用非唯一索引吗？EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问外键可以引用非唯一索引吗？
EN