FK是否总是与字段“id”相关？父表的？不好的做法是否指向另一个领域？
想象我有一个表 Nation（名称，iso_code_3，语言，..）和一个表 Culture（语言），其中包含字段（名称，...）。现在我必须在表 Nations 中创建一个指向表 Culture 的外键（字段语言）（你知道，每个国家都使用一种语言）。
所以现在我想知道表 Nations 中的外键，应该引用字段“id”，还是可以直接引用字段“name”（在这两种情况下，都是表 Culture 的）？
我的意见：例如，如果我想检索包含所使用语言的 Nation 行，最好引用“名称”字段。这样我就可以避免使用 INNER JOIN 子句。
问候
哈维
imagine i have a table Nation (name, iso_code_3, language, ..) and a table Culture (languages) with fields (name, ...). Now i have to create a foreing key (field language) in the table Nations that point to the table Culture (you know, a language is spoken in each nation).
So now I'm wondering about the foreign key in the table Nations, should be referenced to the field "id" or could i reference it directly to the field "name" (in both cases, of the table Culture) ??
My opinion: if for example I want to retrieve a row of Nation containing the language that is spoken, would be better to reference to the field "name". In that way I would avoid an INNER JOIN clause.
Regards
Javi
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
外键通常应引用另一个表的主键。是的 - 直接绑定到实际数据字段可能看起来很诱人 - 但如果它不是唯一的，不清楚你引用的是哪一行怎么办？
外键必须始终引用引用表中至少是唯一的列 - 主键始终是唯一的，因此当然是主要候选者。
不要试图走捷径 - SQL 数据库非常擅长解析 JOIN - 不要为了“快速”获胜而牺牲数据完整性......
A foreign key should typically reference the primary key of the other table. Yes - it might look tempting to bind to the actual data field directly - but what if that is not unique, not clear which row you refer to??
A foreign key must always refer to a column in the referenced table that is at least UNIQUE - the primary key always is UNIQUE, so that' the prime candidate, of course.
Don't try to take shortcuts - SQL databases are pretty good at resolving JOINs - don't sacrifice data integrity for the sake of a "quickie" win.....
您可以将其指向任何唯一的字段。如果将其指向具有字符串类型数据的字段，则连接速度会比将其指向整数代理键慢，特别是如果字符串数据很长。
You can point it to any field which is unique. If you point it to a field with string type data, the join will be slower than if you point it to an integer surrogate key particulalry if the string data is long.
从你的描述中并不清楚“文化”和“语言”是否是同一概念。例如，西班牙和秘鲁可以说有不同的文化，但都有西班牙语。西班牙还有加泰罗尼亚语和巴斯克语。秘鲁也有克丘亚语。
一旦您弄清楚了这一点，您可能会发现您正在执行多对多联接，而不是在任一表中使用 PK。如果这是正确的，那么它不仅仅与姓名和识别号码有关。
作为另一个领域的示例，邮件系统经常对邮政编码进行联接，即使数据库中没有邮政编码主表。这确实是多对多连接。有时这样做是正确的，但很少。最常见的是，正确的做法是为所识别的实体（在您的例子中为“语言”）建立一个主表，然后执行两个普通的旧 FK-PK 连接。不用担心三路连接。只有几百行，延迟不值得考虑。
It's not clear from your description whether "culture" and "language" identify the same concept. For example, Spain and Peru might be said to have different cultures, but both have the Spanish language. Spain also has Catalan and Basque. Peru also has Quechua.
Once you clear this up, you might discover that you are doing a many-to-many join, not using the PK in either table. If this is correct, it has to do with a lot more than name versus identifying number.
As an example from another field, mailing systems often do joins on zip-code, even though there is no master table of zip-codes in the database. That's really a many-to-many join. It's sometimes the right thing to do, but rarely. Most commonly, the right thing is to establish a master table for the entity identified (in your case "Language") and then do two plain old FK-PK joins. Don't worry about a three way join. With only a few hundred rows, the delay is not worth considering.
外键可以引用它引用的表中的任何候选键。为了简单起见，通常为每个表选择一个键（通常指定为“主”键）以一致地用于对该表的所有外键引用 - 但如果您有一个不这样做的充分理由。选择要使用的密钥的良好标准是：熟悉、简单和稳定性< /a>.
对于任何列（无论是否为键），“Id”都是一个糟糕的名称。
A foreign key can reference any candidate key in the table it references. For simplicity's sake it usually makes sense to pick one key per table (usually designated the "primary" key) to be used consistently for all foreign key references to that table - but there is no absolute reason why you must do that if you have a sound reason to do otherwise. Good criteria for choosing what keys to use are: Familiarity, Simplicity and Stability.
"Id" is a poor name for any column - key or not.
可以在其他表的任何强制执行唯一性的列上创建外键。主键或唯一索引。
在您的情况下，如果您使语言独一无二，那么将其指向语言是一个好主意。
Foreign Key can be created on any column of other table that has a Uniqueness enforced. Either a Primary Key or a Unique Index.
In your case it would be a good idea to point it to Language, if you make the Language unique.
在您的示例中，您具有多对多关系，因为 1 个国家/地区可能会说多种语言，因此 1 个国家/地区可能会使用 1 种语言。
因此，您实际上应该由一个关系表来表示，它代表您的点对点关系。
该表必须包含其他两个表的 PK。
根据定义，这 2 个 FK 的组合就是您的 CountryLanguage 表的 PK。
IN your exemple, you have a many-to-many relationship, since 1 country may speak several languages, an 1 language may be spoken in several countries.
So you should in fact be represented by a relationship table, that represents your m-to-m relation.
That table must contain the PK of both other tables.
The combination of those 2 FK is by definition the PK of your CountryLanguage table.