database 外键命名方案
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/199498/
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
Foreign Key naming scheme
提问by nickf
I'm just getting started working with foreign keys for the first time and I'm wondering if there's a standard naming scheme to use for them?
我刚刚开始第一次使用外键，我想知道是否有标准命名方案可用于它们？
Given these tables:
鉴于这些表：
task (id, userid, title)
note (id, taskid, userid, note);
user (id, name)
Where Tasks have Notes, Tasks are owned by Users, and Users author Notes.
任务有注释，任务归用户所有，用户创作注释。
How would the three foreign keys be named in this situation? Or alternatively, does it even matter at all?
在这种情况下如何命名三个外键？或者，它甚至根本不重要吗？
Update: This question is about foreign key names, not field names!
更新：这个问题是关于外键名称，而不是字段名称！
回答by Greg Beech
The standard convention in SQL Server is:
SQL Server 中的标准约定是：
FK_ForeignKeyTable_PrimaryKeyTable
So, for example, the key between notes and tasks would be:
因此，例如，笔记和任务之间的关键是：
FK_note_task
And the key between tasks and users would be:
任务和用户之间的关键是：
FK_task_user
This gives you an 'at a glance' view of which tables are involved in the key, so it makes it easy to see which tables a particular one (the first one named) depends on (the second one named). In this scenario the complete set of keys would be:
这为您提供了键中涉及哪些表的“一目了然”视图，因此可以轻松查看特定表（命名的第一个）依赖于哪些表（命名的第二个）。在这种情况下，完整的密钥集将是：
FK_task_user
FK_note_task
FK_note_user
So you can see that tasks depend on users, and notes depend on both tasks and users.
所以你可以看到任务依赖于用户，笔记同时依赖于任务和用户。
回答by onedaywhen
I use two underscore characters as delimiter i.e.
我使用两个下划线字符作为分隔符，即
fk__ForeignKeyTable__PrimaryKeyTable
This is because table names will occasionally contain underscore characters themselves. This follows the naming convention for constraints generally because data elements' names will frequently contain underscore characters e.g.
这是因为表名本身偶尔会包含下划线字符。这通常遵循约束的命名约定，因为数据元素的名称将经常包含下划线字符，例如
CREATE TABLE NaturalPersons (
...
person_death_date DATETIME,
person_death_reason VARCHAR(30)
CONSTRAINT person_death_reason__not_zero_length
CHECK (DATALENGTH(person_death_reason) > 0),
CONSTRAINT person_death_date__person_death_reason__interaction
CHECK ((person_death_date IS NULL AND person_death_reason IS NULL)
OR (person_death_date IS NOT NULL AND person_death_reason IS NOT NULL))
...
回答by EvilTeach
How about FK_TABLENAME_COLUMNNAME?
怎么样FK_TABLENAME_COLUMNNAME？
Keep It Simple Stupid whenever possible.
ķEEP我牛逼小号imple小号tupid只要有可能。
回答by bvj
A note from Microsoft concerning SQL Server:
Microsoft 关于 SQL Server 的说明：
A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.
FOREIGN KEY 约束不必只链接到另一个表中的 PRIMARY KEY 约束；它也可以定义为引用另一个表中的 UNIQUE 约束的列。
so, I'll use terms describing dependency instead of the conventional primary/foreign relationship terms.
因此，我将使用描述依赖性的术语而不是传统的主要/外部关系术语。
When referencing the PRIMARY KEY of the independent (parent)table by the similarly named column(s) in the dependent (child)table, I omit the column name(s):
在从属（子）表中通过类似命名的列引用独立（父）表的 PRIMARY KEY 时，我省略了列名：
FK_ChildTable_ParentTable
When referencing other columns, or the column names vary between the two tables, or just to be explicit:
当引用其他列时，或者两个表之间的列名不同，或者只是为了明确：
FK_ChildTable_childColumn_ParentTable_parentColumn
回答by Steve Moyer
I usually just leave my PK named id, and then concatenate my table name and key column name when naming FKs in other tables. I never bother with camel-casing, because some databases discard case-sensitivity and simply return all upper or lower case names anyway. In any case, here's what my version of your tables would look like:
我通常只保留我的 PK 命名为 id，然后在命名其他表中的 FK 时连接我的表名和键列名。我从不打扰驼峰式大小写，因为有些数据库会丢弃大小写敏感，并且无论如何都简单地返回所有大写或小写名称。无论如何，这就是我的表格版本的样子：
task (id, userid, title);
note (id, taskid, userid, note);
user (id, name);
Note that I also name my tables in the singular, because a row represents one of the objects I'm persisting. Many of these conventions are personal preference. I'd suggest that it's more important to choose a convention and always use it, than it is to adopt someone else's convention.
请注意，我也用单数命名我的表，因为一行代表我正在持久化的对象之一。这些约定中有许多是个人偏好。我建议选择一个约定并始终使用它比采用其他人的约定更重要。
回答by SSISPissesMeOff
This is probably over-kill, but it works for me. It helps me a great deal when I am dealing with VLDBs especially. I use the following:
这可能是过度杀伤，但它对我有用。特别是在处理 VLDB 时，它对我有很大帮助。我使用以下内容：
CONSTRAINT [FK_ChildTableName_ChildColName_ParentTableName_PrimaryKeyColName]
Of course if for some reason you are not referencing a primary key you must be referencing a column contained in a unique constraint, in this case:
当然，如果由于某种原因您没有引用主键，您必须引用包含在唯一约束中的列，在这种情况下：
CONSTRAINT [FK_ChildTableName_ChildColumnName_ParentTableName_ColumnInUniqueConstaintName]
Can it be long, yes. Has it helped keep info clear for reports, or gotten me a quick jump on that the potential issue is during a prod-alert 100% would love to know peoples thoughts on this naming convention.
可以长吗，可以。它是否有助于保持报告的信息清晰，或者让我快速了解潜在问题是在 prod-alert 期间 100% 想知道人们对这个命名约定的想法。
回答by Cary Bondoc
My usual approach is
我通常的做法是
FK_ColumnNameOfForeignKey_TableNameOfReference_ColumnNameOfReference
Or in other terms
或者换个说法
FK_ChildColumnName_ParentTableName_ParentColumnName
This way I can name two foreign keys that reference the same table like a history_info tablewith column actionBy and actionTofrom users_infotable
这样我就可以命名两个引用同一个表的外键，比如history_info tablewith column actionBy and actionTofrom users_infotable
It will be like
它会像
FK_actionBy_usersInfo_name - For actionBy
FK_actionTo_usersInfo_name - For actionTo
Note that:
注意：
I didn't include the child table name because it seems common sense to me, I am in the table of the child so I can easily assume the child's table name. The total character of it is 26 and fits well to the 30 character limit of oraclewhich was stated by Charles Burns on a comment here
我没有包括子表名称，因为这对我来说似乎是常识，我在子表中，所以我可以轻松地假设子表名称。它的总字符数为 26，非常符合Charles Burns 在此处的评论中所述的 oracle 的 30 个字符限制
Note for readers: Many of the best practices listed below do not work in Oracle because of its 30 character name limit. A table name or column name may already be close to 30 characters, so a convention combining the two into a single name requires a truncation standard or other tricks. – Charles Burns
读者注意事项：由于其 30 个字符的名称限制，下面列出的许多最佳实践在 Oracle 中不起作用。表名或列名可能已经接近 30 个字符，因此将两者合并为一个名称的约定需要截断标准或其他技巧。— 查尔斯·伯恩斯
回答by Chad Kieffer
Based on the answers and comments here, a naming convention which includes the FK table, FK field, and PK table (FK_FKTbl_FKCol_PKTbl) should avoid FK constraint name collisions.
根据此处的答案和评论，包含 FK 表、FK 字段和 PK 表 (FK_FKTbl_FKCol_PKTbl) 的命名约定应避免 FK 约束名称冲突。
So, for the given tables here:
因此，对于此处给定的表：
fk_task_userid_user
fk_note_userid_user
So, if you add a column to track who last modified a task or a note...
因此，如果您添加一列来跟踪谁上次修改了任务或笔记...
fk_task_modifiedby_user
fk_note_modifiedby_user
回答by user12345
If you aren't referencing your FK's that often and using MySQL (and InnoDB) then you can just let MySQL name the FK for you.
如果您不经常引用您的 FK 并使用 MySQL（和 InnoDB），那么您可以让 MySQL 为您命名 FK。
At a later time you can find the FK name you need by running a query.
稍后您可以通过运行查询找到您需要的 FK 名称。
回答by coldserenity
Try using upper-cased Version 4 UUID with first octet replaced by FK and '_' (underscore) instead of '-' (dash).
尝试使用大写的第 4 版 UUID，其中第一个八位字节由 FK 和“_”（下划线）代替“-”（破折号）替换。
E.g.
例如
FK_4VPO_K4S2_A6M1_RQLEYLT1VQYVFK_1786_45A6_A17C_F158C0FB343EFK_45A5_4CFA_84B0_E18906927B53
FK_4VPO_K4S2_A6M1_RQLEYLT1VQYVFK_1786_45A6_A17C_F158C0FB343EFK_45A5_4CFA_84B0_E18906927B53
Rationale is the following
理由如下
- Strict generation algorithm => uniform names;
- Key length is less than 30 characters, which is naming length limitation in Oracle (before 12c);
- If your entity name changes you don't need to rename your FKlike in entity-name based approach (if DB supports table rename operator);
- One would seldom use foreign key constraint's name. E.g. DB tool usually shows what the constraint applies to. No need to be afraid of cryptic look, because you can avoid using it for "decryption".
- 严格生成算法 =>统一名称；
- 密钥长度小于30个字符，这是Oracle中的命名长度限制（12c之前）；
- 如果您的实体名称更改，则不需要像基于实体名称的方法那样重命名 FK（如果 DB 支持表重命名运算符）；
- 人们很少使用外键约束的名称。例如，DB 工具通常会显示约束适用于什么。无需害怕神秘的外观，因为您可以避免将其用于“解密”。
