SQL 外键和参考键有什么区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8595695/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is difference between foreign key and reference key?
提问by Anderson
I am very confused about those two terms. Are they the same or different?
我对这两个术语感到非常困惑。它们是相同的还是不同的？
Some books and people say they are the same and others say they are different.
有些书和人说它们是一样的，有些人说它们是不同的。
I tried but couldn't find a conclusive answer.
我试过了，但找不到决定性的答案。
回答by Niels Abildgaard
I am supposing that you are talking about using the REFERENCESwhere the FOREIGN KEYkeyword is not used, eg.
我假设您正在谈论使用未使用关键字的REFERENCES地方FOREIGN KEY，例如。
author_id INTEGER REFERENCES author(id)
... instead of ...
... 代替 ...
author_id INTEGER,
FOREIGN KEY(author_id) REFERENCES author(id)
The answer is, that it is simply shorthand syntax for the same thing. The main concern when altering between the two should be readability.
答案是，它只是同一事物的简写语法。在两者之间切换时的主要关注点应该是可读性。
回答by Mike Sherrill 'Cat Recall'
"Reference key" isn't a normal technical term in relational modeling or in SQL implementation in US English.
“参考键”在美国英语中不是关系建模或 SQL 实现中的普通技术术语。
A foreign key "references" a key in some other table; could that be where the confusion comes from?
外键“引用”其他表中的键；那会是混乱的来源吗？
回答by maneesha
You don't really call something a reference key... They are the same thing... you might see the word referencesused for example in sqlite: you might use syntax like this to start a db of authors and books. This lets you show that one author can have many books. This tells the db that the books.author_id(defined a couple of lines up) references author.id
您并没有真正将某些东西称为参考键……它们是相同的东西……您可能会看到例如在 sqlite 中使用的单词references：您可能会使用这样的语法来启动作者和书籍的数据库。这让您可以证明一个作者可以拥有多本书。这告诉数据库books.author_id（定义了几行）引用author.id
CREATE TABLE 'author' (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
firstname varchar(255)
lastname varchar(255)
);
CREATE TABLE 'books' (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
author_id INTEGER,
title varchar(255),
published date,
FOREIGN KEY(author_id) REFERENCES author(id)
);
回答by alpha
A foreign key mustrefer to a primary key. When using REFERENCES constraint simply, then it isn't necessary that the referenced key be a primary key.
外键必须引用主键。当简单地使用 REFERENCES 约束时，被引用的键不必是主键。
回答by onedaywhen
Perhaps you are using the term "reference key" somewhat loosely?
也许您使用术语“参考键”有点松散？
A foreign key value in one row is said to "reference" the row that contains the corresponding key value. Note the word "reference" in the prior sentence is a verb, so we may say we have a referencing foreign key value and a referenced key value.
一行中的外键值被称为“引用”包含相应键值的行。注意前一句中的“reference”这个词是动词，所以我们可以说我们有一个引用外键值和一个被引用键值。
Although it is the key values, rather than the table key constraint, that is being referenced, I suppose loosely speaking we could say "referenced key" to mean the rows that comprise the values that may potentially be referenced. I then see how "referenced key" could become "referenced key" but not belie its origin.
尽管引用的是键值，而不是表键约束，但我想粗略地说，我们可以说“引用键”是指包含可能被引用的值的行。然后我看到“被引用的密钥”如何变成“被引用的密钥”，但不能掩盖它的起源。
回答by Matt Junior
A foreign key "references" a key in some other table. That key in some other table is called Referenced key. You'll probably hear a lot about this if you're using Graphic feature on phpmyadmin.
外键“引用”其他表中的键。其他表中的那个键称为引用键。如果您在 phpmyadmin 上使用图形功能，您可能会听到很多关于此的信息。
回答by Sebastian Nedumala
There are 2 ways to declare a foreign key(s):
有两种方法可以声明外键：
if the foreign key is a SINGLE attribute:
REFERENCES ()if foreign keys are a LIST of attributes
如果外键是 SINGLE 属性：
REFERENCES()如果外键是属性列表
FOREIGN KEY () REFERENCES
外键 () 参考
回答by Sebastian Nedumala
The only and most important difference between the two keywords 'FOREIGN KEY" and "REFERENCES" keywords is though both of them make the data to be child data of the parent table, the "FOREIGN KEY" is used to create a table level constraint whereas REFERENCES keyword can be used to create column level constraint only. Column level constraints can be created only while creating the table only. But table level constraints can be added using ALTER TABLE command.
两个关键字“FOREIGN KEY”和“REFERENCES”之间唯一和最重要的区别是虽然它们都使数据成为父表的子数据，但“FOREIGN KEY”用于创建表级约束，而REFERENCES 关键字只能用于创建列级约束。只有在创建表时才能创建列级约束。但可以使用 ALTER TABLE 命令添加表级约束。
