SQL 主键/外键命名约定

Primary key/foreign Key naming convention

提问by Jeremy

In our dev group we have a raging debate regarding the naming convention for Primary and Foreign Keys. There's basically two schools of thought in our group:

在我们的开发组中，我们对主键和外键的命名约定进行了激烈的辩论。我们小组基本上有两种思想流派：

1:

1：

Primary Table (Employee)   
Primary Key is called ID

Foreign table (Event)  
Foreign key is called EmployeeID

or

或者

2:

2：

Primary Table (Employee)  
Primary Key is called EmployeeID

Foreign table (Event)  
Foreign key is called EmployeeID

I prefer not to duplicate the name of the table in any of the columns (So I prefer option 1 above). Conceptually, it is consistent with a lot of the recommended practices in other languages, where you don't use the name of the object in its property names. I think that naming the foreign key EmployeeID(or Employee_IDmight be better) tells the reader that it is the IDcolumn of the EmployeeTable.

我不想在任何列中重复表的名称（所以我更喜欢上面的选项 1）。从概念上讲，它与其他语言中的许多推荐做法一致，在这些做法中，您不在其属性名称中使用对象的名称。我认为命名外键EmployeeID（或Employee_ID可能更好）告诉读者它是表的ID列Employee。

Some others prefer option 2 where you name the primary key prefixed with the table name so that the column name is the same throughout the database. I see that point, but you now can not visually distinguish a primary key from a foreign key.

其他一些人更喜欢选项 2，其中您命名主键并以表名为前缀，以便整个数据库中的列名都相同。我明白这一点，但您现在无法从视觉上区分主键和外键。

Also, I think it's redundant to have the table name in the column name, because if you think of the table as an entity and a column as a property or attribute of that entity, you think of it as the ID attribute of the Employee, not the EmployeeIDattribute of an employee. I don't go an ask my coworker what his PersonAgeor PersonGenderis. I ask him what his Age is.

另外，我认为在列名中包含表名是多余的，因为如果您将表视为实体，将列视为该实体的属性或属性，您会将其视为 的 ID 属性，而Employee不是EmployeeID员工的属性。我不走了问我的同事他什么PersonAge或者PersonGender是。我问他他的年龄是多少。

So like I said, it's a raging debate and we go on and on and on about it. I'm interested to get some new perspectives.

所以就像我说的，这是一场激烈的辩论，我们一直在谈论它。我有兴趣获得一些新的观点。

采纳答案by Russell Steen

It doesn't really matter. I've never run into a system where there is a real difference between choice 1 and choice 2.

这并不重要。我从未遇到过选择 1 和选择 2 之间存在真正差异的系统。

Jeff Atwood had a great article a while back on this topic. Basically people debate and argue the most furiously those topics which they cannot be proven wrong on. Or from a different angle, those topics which can only be won through filibuster style endurance based last-man-standing arguments.

杰夫·阿特伍德 (Jeff Atwood) 不久前发表了一篇关于此主题的精彩文章。基本上，人们最激烈地辩论和争论那些他们不能被证明是错误的话题。或者从另一个角度来看，那些只能通过基于最后一个人的立场的阻挠式忍耐力才能赢得的话题。

Pick one and tell them to focus on issues that actually impact your code.

选择一个并告诉他们专注于实际影响您的代码的问题。

EDIT: If you want to have fun, have them specify at length why their method is superior for recursive table references.

编辑：如果您想玩得开心，请让他们详细说明为什么他们的方法优于递归表引用。

回答by Steven Huwig

If the two columns have the same name in both tables (convention #2), you can use the USING syntax in SQL to save some typing and some boilerplate noise:

如果两列在两个表中具有相同的名称（约定＃2），您可以在 SQL 中使用 USING 语法来节省一些输入和一些样板噪音：

SELECT name, address, amount
  FROM employees JOIN payroll USING (employee_id)

Another argument in favor of convention #2 is that it's the way the relational modelwas designed.

支持约定 #2 的另一个论点是，这是关系模型的设计方式。

The significance of each column is partially conveyed by labeling it with the name of the corresponding domain.

每列的重要性通过用相应域的名称对其进行标记来部分传达。

回答by KM.

I think it depends on your how you application is put together. If you use ORM or design your tables to represent objects then option 1 may be for you.

我认为这取决于您的应用程序是如何组合在一起的。如果您使用 ORM 或设计您的表格来表示对象，那么选项 1 可能适合您。

I like to code the database as its own layer. I control everything and the app just calls stored procedures. It is nice to have result sets with complete column names, especially when there are many tables joined and many columns returned. With this stype of application, I like option 2. I really like to see column names match on joins. I've worked on old systems where they didn't match and it was a nightmare,

我喜欢将数据库编码为它自己的层。我控制一切，应用程序只调用存储过程。结果集具有完整的列名是很好的，尤其是当有很多表连接并且返回很多列时。对于这种类型的应用程序，我喜欢选项 2。我真的很喜欢在连接上看到列名匹配。我曾在旧系统上工作，它们不匹配，这是一场噩梦，

回答by Charles Bretana

Neither convention works in all cases, so why have one at all? Use Common sense...

两种约定都适用于所有情况，那么为什么要使用一个呢？使用常识...

e.g., for self-referencing table, when there are more than one FK column that self-references the same table's PK, you HAVE to violate both "standards", since the two FK columns can't be named the same... e.g., EmployeeTable with EmployeeId PK, SupervisorId FK, MentorId Fk, PartnerId FK, ...

例如，对于自引用表，当有多个 FK 列自引用同一个表的 PK 时，您必须违反这两个“标准”，因为两个 FK 列不能命名相同......例如, EmployeeTable 与 EmployeeId PK, SupervisorId FK, MentorId Fk, PartnerId FK, ...

回答by Wouter

Have you considered the following?

您是否考虑过以下问题？

Primary Table (Employee)   
Primary Key is PK_Employee

Foreign table (Event)  
Foreign key is called FK_Employee

回答by MatBailie

I agree that there is little to choose between them. To me a much more significant thing about either standard is the "standard" part.

我同意他们之间几乎没有选择。对我来说，关于任一标准的更重要的事情是“标准”部分。

If people start 'doing their own thing' they should be strung up by their nethers. IMHO :)

如果人们开始“做自己的事”，他们应该被他们的下界绞死。恕我直言 ：）

回答by Jarett Millard

The convention we use where I work is pretty close to A, with the exception that we name tables in the plural form (ie, "employees") and use underscores between the table and column name. The benefit of it is that to refer to a column, it's either "employees _ id" or "employees.id", depending on how you want to access it. If you need to specify what table the column is coming from, "employees.employees _ id" is definitely redundant.

我们在我工作的地方使用的约定与 A 非常接近，除了我们以复数形式命名表（即“员工”）并在表名和列名之间使用下划线。它的好处是引用一个列，它要么是“employees _ id”，要么是“employees.id”，这取决于你想如何访问它。如果需要指定列来自哪个表，“employees.employees _ id”绝对是多余的。

回答by Bruce Patin

If you are looking at application code, not just database queries, some things seem clear to me:

如果您正在查看应用程序代码，而不仅仅是数据库查询，有些事情对我来说似乎很清楚：

1. Table definitions usually directly map to a class that describes one object, so they should be singular. To describe a collection of an object, I usually append "Array" or "List" or "Collection" to the singular name, as it more clearly than use of plurals indicates not only that it is a collection, but what kind of a collection it is. In that view, I see a table name as not the name of the collection, but the name of the type of object of which it is a collection. A DBA who doesn't write application code might miss this point.

2. The data I deal with often uses "ID" for non-key identification purposes. To eliminate confusion between key "ID"s and non-key "ID"s, for the primary key name, we use "Key" (that's what it is, isn't it?) prefixed with the table name or an abbreviation of the table name. This prefixing (and I reserve this only for the primary key) makes the key name unique, which is especially important because we use variable names that are the same as the database column names, and most classes have a parent, identified by the name of the parent key. This also is needed to make sure that it is not a reserved keyword, which "Key" alone is. To facilitate keeping key variable names consistent, and to provide for programs that do natural joins, foreign keys have the same name as is used in the table in which they are the primary key. I have more than once encountered programs which work much better this way using natural joins. On this last point, I admit a problem with self-referencing tables, which I have used. In this case, I would make an exception to the foreign key naming rule. For example, I would use ManagerKey as a foreign key in the Employee table to point to another record in that table.

1. 表定义通常直接映射到描述一个对象的类，因此它们应该是单数的。为了描述一个对象的集合，我通常在单数名称后附加“Array”或“List”或“Collection”，因为它比使用复数更清楚地表明它不仅是一个集合，而且表明它是一个什么样的集合这是。在那个视图中，我看到的表名不是集合的名称，而是它是集合的对象类型的名称。不编写应用程序代码的 DBA 可能会错过这一点。

2. 我处理的数据经常使用“ID”来进行非密钥识别。为了消除键“ID”和非键“ID”之间的混淆，对于主键名称，我们使用带有表名或缩写的“键”（这就是它，不是吗？）表名。这个前缀（我只为主键保留）使键名唯一，这尤其重要，因为我们使用与数据库列名相同的变量名，并且大多数类都有一个父级，由名称标识父键。这也需要确保它不是保留关键字，只有“Key”才是。为了便于保持关键变量名称的一致性，并提供进行自然连接的程序，外键的名称与它们作为主键的表中使用的名称相同。我不止一次遇到过使用自然连接以这种方式工作得更好的程序。关于最后一点，我承认我使用过的自引用表存在问题。在这种情况下，我会对外键命名规则进行例外处理。例如，我将使用 ManagerKey 作为 Employee 表中的外键来指向该表中的另一条记录。

回答by JYelton

I like convention #2 - in researching this topic, and finding this question before posting my own, I ran into the issue where:

我喜欢约定 #2 - 在研究这个主题，并在发布我自己的问题之前找到这个问题，我遇到了以下问题：

I am selecting * from a table with a large number of columns and joining it to a second table that similarly has a large number of columns. Both tables have an "id" column as the primary key, and that means I have to specifically pick out every column (as far as I know) in order to make those two values unique in the result, i.e.:

我从具有大量列的表中选择 * 并将其连接到同样具有大量列的第二个表。两个表都有一个“id”列作为主键，这意味着我必须专门挑选出每一列（据我所知）以使这两个值在结果中是唯一的，即：

SELECT table1.id AS parent_id, table2.id AS child_id

Though using convention #2 means I will still have some columns in the result with the same name, I can now specify whichid I need (parent or child) and, as Steven Huwig suggested, the USINGstatement simplifies things further.

尽管使用约定 #2 意味着结果中仍会有一些列具有相同的名称，但我现在可以指定我需要哪个id（父或子），并且正如 Steven Huwig 所建议的那样，该USING语句进一步简化了事情。

回答by Ross

I've always used userIdas a PK on one table and userIdon another table as a FK. 'm seriously thinking about using userIdPKand userIdFKas names to identify one from the other. It will help me to identify PK and FK quickly when looking at the tables and it seems like it will clear up code when using PHP/SQL to access data making it easier to understand. Especially when someone else looks at my code.

我一直将userId用作一张表上的 PK，将另一张表上的userId用作 FK。正在认真考虑使用userIdPK和userIdFK作为名称来识别另一个。它将帮助我在查看表格时快速识别 PK 和 FK，并且在使用 PHP/SQL 访问数据时似乎会清除代码，使其更易于理解。尤其是当其他人查看我的代码时。