SQL 数据库表中 ID 列的命名

Naming of ID columns in database tables

提问by Arry

I was wondering peoples opinions on the naming of ID columns in database tables.

我想知道人们对数据库表中 ID 列命名的看法。

If I have a table called Invoices with a primary key of an identity column I would call that column InvoiceID so that I would not conflict with other tables and it's obvious what it is.

如果我有一个名为 Invoices 的表，其中有一个标识列的主键，我会将该列称为 InvoiceID，这样我就不会与其他表发生冲突，并且很明显它是什么。

Where I am workind current they have called all ID columns ID.

我现在工作的地方，他们已经调用了所有 ID 列 ID。

So they would do the following:

所以他们会做以下事情：

Select  
    i.ID 
,   il.ID 
From
    Invoices i
    Left Join InvoiceLines il
        on i.ID = il.InvoiceID

Now, I see a few problems here:
1. You would need to alias the columns on the select
2. ID = InvoiceID does not fit in my brain
3. If you did not alias the tables and referred to InvoiceID is it obvious what table it is on?

现在，我在这里看到了一些问题：
1. 您需要为 select 上的列设置别名
2. ID = InvoiceID 不适合我的大脑
3. 如果您没有为表设置别名并引用 InvoiceID 很明显是什么表它开着？

What are other peoples thoughts on the topic?

其他人对这个话题有什么看法？

采纳答案by HLGEM

ID is a SQL Antipattern. See http://www.amazon.com/s/ref=nb_sb_ss_i_1_5?url=search-alias%3Dstripbooks&field-keywords=sql+antipatterns&sprefix=sql+a

ID 是 SQL 反模式。参见http://www.amazon.com/s/ref=nb_sb_ss_i_1_5?url=search-alias%3Dstripbooks&field-keywords=sql+antipatterns&sprefix=sql+a

If you have many tables with ID as the id you are making reporting that much more difficult. It obscures meaning and makes complex queries harder to read as well as requiring you to use aliases to differentiate on the report itself.

如果您有许多以 ID 作为 ID 的表，那么您的报告就会变得更加困难。它模糊了含义并使复杂的查询更难阅读，并要求您使用别名来区分报告本身。

Further if someone is foolish enough to use a natural join in a database where they are available, you will join to the wrong records.

此外，如果有人愚蠢到在可用的数据库中使用自然连接，您将连接到错误的记录。

If you would like to use the USING syntax that some dbs allow, you cannot if you use ID.

如果您想使用某些数据库允许的 USING 语法，则不能使用 ID。

If you use ID you can easily end up with a mistaken join if you happen to be copying the join syntax (don't tell me that no one ever does this!)and forget to change the alias in the join condition.

如果您使用 ID，如果您碰巧复制了连接语法（不要告诉我从来没有人这样做过！）并且忘记更改连接条件中的别名，则很容易以错误的连接结束。

So you now have

所以你现在有

select t1.field1, t2.field2, t3.field3
from table1 t1 
join table2 t2 on t1.id = t2.table1id
join table3 t3 on t1.id = t3.table2id

when you meant

当你的意思是

select t1.field1, t2.field2, t3.field3 
from table1 t1 
join table2 t2 on t1.id = t2.table1id
join table3 t3 on t2.id = t3.table2id

If you use tablenameID as the id field, this kind of accidental mistake is far less likely to happen and much easier to find.

如果使用 tablenameID 作为 id 字段，这种意外的错误发生的可能性要小得多，也更容易发现。

回答by kemiller2002

I always prefered ID to TableName + ID for the id column and then TableName + ID for a foreign key. That way all tables have a the same name for the id field and there isn't a redundant description. This seems simpler to me because all the tables have the same primary key field name.

对于 id 列，我总是更喜欢 ID 而不是 TableName + ID，然后是外键的 TableName + ID。这样，所有表的 id 字段都具有相同的名称，并且没有多余的描述。这对我来说似乎更简单，因为所有表都具有相同的主键字段名称。

As far as joining tables and not knowing which Id field belongs to which table, in my opinion the query should be written to handle this situation. Where I work, we always prefece the fields we use in a statement with the table/table alias.

至于连接表并且不知道哪个 Id 字段属于哪个表，我认为应该编写查询来处理这种情况。在我工作的地方，我们总是优先使用我们在带有表/表别名的语句中使用的字段。

回答by Echostorm

Theres been a nerd fight about this very thing in my company of late. The advent of LINQ has made the redundant tablename+IDpattern even more obviously silly in my eyes. I think most reasonable people will say that if you're hand writing your SQL in such a manner as that you have to specify table names to differentiate FKsthen it's not only a savings on typing, but it adds clarity to your SQL to use just the ID in that you can clearly see which is the PKand which is the FK.

最近在我的公司里就这件事发生了一场书呆子大战。LINQ 的出现，让多余的表名+ID模式在我看来更加明显的愚蠢。我认为大多数合理的人会说，如果您以必须指定表名来区分FK的方式手动编写 SQL，那么这不仅可以节省打字时间，而且还可以让您的 SQL 更加清晰，只需使用ID中，你可以清楚地看到哪个是PK，哪个是FK。

E.g.

例如

FROM Employees e LEFT JOIN Customers c ON e.ID = c.EmployeeID

FROM 员工 e LEFT JOIN 客户 c ON e.ID = c.EmployeeID

tells me not only that the two are linked, but which is the PKand which is the FK. Whereas in the old style you're forced to either look or hope that they were named well.

不仅告诉我两者是联系在一起的，而且告诉我哪个是PK，哪个是FK。而在旧风格中，您被迫要么看起来要么希望它们的名字很好。

回答by Jason Cohen

We use InvoiceID, not ID. It makes queries more readable -- when you see IDalone it could mean anything, especially when you alias the table to i.

我们使用InvoiceID，而不是ID。它使查询更具可读性——当您ID单独查看时，它可能意味着任何事情，尤其是当您将表别名为i.

回答by pawnrob

I agree with Keven and a few other people here that the PK for a table should simply be Id and foreign keys list the OtherTable + Id.

我同意 Keven 和这里的其他一些人的意见，表的 PK 应该只是 Id，外键列出 OtherTable + Id。

However I wish to add one reason which recently gave more weight to this arguement.

然而，我想补充一个最近更加重视这一论点的原因。

In my current position we are employing the entity framework using POCO generation. Using the standard naming convention of Id the the PK allows for inheritance of a base poco class with validation and such for tables which share a set of common column names. Using the Tablename + Id as the PK for each of these tables destroys the ability to use a base class for these.

在我目前的职位上，我们正在使用使用 POCO 生成的实体框架。使用 Id 的标准命名约定，PK 允许继承带有验证的基本 poco 类，例如共享一组公共列名的表。使用 Tablename + Id 作为这些表中的每一个的 PK 破坏了为这些表使用基类的能力。

Just some food for thought.

只是一些思考的食物。

回答by bjdodo

My preference is also ID for primary key and TableNameID for foreign key. I also like to have a column "name" in most tables where I hold the user readable identifier (i.e. name :-)) of the entry. This structure offers great flexibility in the application itself, I can handle tables in mass, in the same way. This is a verypowerful thing. Usually an OO software is built on top of the database, but the OO toolset cannot be applied because the db itself does not allow it. Having the columns id and name is still not very good, but it is a step.

我的偏好也是主键的 ID 和外键的 TableNameID。我还喜欢在大多数表中都有一个“名称”列，其中包含条目的用户可读标识符（即名称 :-)）。这种结构为应用程序本身提供了极大的灵活性，我可以以相同的方式处理大量表格。这是一个非常强大的东西。通常OO软件是建立在数据库之上的，但是OO工具集无法应用，因为数据库本身不允许。拥有列 id 和 name 仍然不是很好，但它是一个步骤。

Select
i.ID , il.ID From Invoices i Left Join InvoiceLines il on i.ID = il.InvoiceID

Select
i.ID , il.ID From Invoices i Left Join InvoiceLines il on i.ID = il.InvoiceID

Why cant I do this?

为什么我不能这样做？

Select  
    Invoices.ID 
,   InvoiceLines.ID 
From
    Invoices
    Left Join InvoiceLines
        on Invoices.ID = InvoiceLines.InvoiceID

In my opinion this is very much readable and simple. Naming variables as i and il is a poor choice in general.

在我看来，这是非常可读和简单的。将变量命名为 i 和 il 通常是一个糟糕的选择。

回答by Nir

It's not really important, you are likely to run into simalar problems in all naming conventions.

这并不重要，您可能会在所有命名约定中遇到类似的问题。

But it is important to be consistent so you don't have to look at the table definitions every time you write a query.

但是保持一致很重要，这样您就不必在每次编写查询时查看表定义。

回答by Eric Kassan

I just started working in a place that uses only "ID" (in the core tables, referenced by TableNameID in foreign keys), and have already found TWO production problems directly caused by it.

我刚开始在一个只使用“ID”的地方工作（在核心表中，外键中由TableNameID引用），并且已经发现了两个直接由它引起的生产问题。

In one case the query used "... where ID in (SELECT ID FROM OtherTable ..." instead of "... where ID in (SELECT TransID FROM OtherTable ...".

在一种情况下，查询使用“... where ID in (SELECT ID FROM OtherTable ...”而不是“... where ID in (SELECT TransID FROM OtherTable ...”)。

Can anyone honestly say that wouldn't have been much easier to spot if full, consistent names were used where the wrong statement would have read "... where TransID in (SELECT OtherTableID from OtherTable ..."? I don't think so.

任何人都可以坦率地说，如果在错误的语句中使用完整、一致的名称，会不会更容易发现“... where TransID in (SELECT OtherTableID from OtherTable ...”？我不认为所以。

The other issue occurs when refactoring code. If you use a temp table whereas previously the query went off a core table then the old code reads "... dbo.MyFunction(t.ID) ..." and if that is not changed but "t" now refers to a temp table instead of the core table, you don't even get an error - just erroneous results.

重构代码时会出现另一个问题。如果您使用临时表，而以前查询离开核心表，则旧代码读取“... dbo.MyFunction(t.ID) ...”，如果未更改但“t”现在指的是临时表而不是核心表，您甚至不会收到错误 - 只是错误的结果。

If generating unnecessary errors is a goal (maybe some people don't have enough work?), then this kind of naming convention is great. Otherwise consistent naming is the way to go.

如果目标是产生不必要的错误（也许有些人没有足够的工作？），那么这种命名约定很棒。否则一致的命名是要走的路。

回答by percebus

I personallyprefer (as it has been stated above) the Table.IDfor the PKand TableIDfor the FK. Even (please don't shoot me) Microsoft Access recommends this.

我个人比较喜欢（因为已经如上所述）的Table.ID的PK和表格ID为FK。甚至（请不要射击我）Microsoft Access 也建议这样做。

HOWEVER, I ALSO know for a fact that some generating tools favor the TableID for PK because they tend to link all column name that contain 'ID'in the word, INCLUDING ID!!!

但是，我也知道一些生成工具偏爱 PK 的 TableID，因为它们倾向于链接单词中包含“ID”的所有列名，包括 ID！

Even the query designer does this on Microsoft SQL Server (and for each query you create, you end up ripping off all the unnecessary newly created relationships on all tables on column ID)

甚至查询设计器也在 Microsoft SQL Server 上执行此操作（对于您创建的每个查询，您最终都会删除列 ID 上所有表上所有不必要的新创建的关系）

THUS as Much as my internal OCD hates it, I roll with the TableIDconvention. Let's remember that it's called a Data BASE, as it will be the base for hopefully many many many applications to come. And all technologies Should benefit of a well normalized with clear description Schema.

因此，尽管我的内部强迫症讨厌它，但我还是坚持使用TableID约定。让我们记住它被称为 Data BASE，因为它有望成为许多应用程序的基础。并且所有技术都应该受益于规范化并具有清晰描述的 Schema。

It goes without saying that I DO draw my line when people start using TableName, TableDescription and such. In My opinion, conventions should do the following:

毋庸置疑，当人们开始使用 TableName、TableDescription 等时，我确实会划清界限。在我看来，约定应该做到以下几点：

• Table name: Pluralized. Ex. Employees

• Table alias: Full table Name, singularized. Ex.

  SELECT Employee.*, eMail.Address
  FROM Employees AS Employee LEFT JOIN eMails as eMail on Employee.eMailID = eMail.eMailID -- I would sure like it to just have the eMail.ID here.... but oh well
  

• 表名：复数。前任。雇员

• 表别名：全表名，单数。前任。

  SELECT Employee.*, eMail.Address
  FROM Employees AS Employee LEFT JOIN eMails as eMail on Employee.eMailID = eMail.eMailID -- I would sure like it to just have the eMail.ID here.... but oh well
  

[Update]

[更新]

Also, there are some valid posts in this thread about duplicated columns due of the "kind of relationship" or role. Example, if a Store has an EmployeeID, that tells me squat. So I sometimes do something like Store.EmployeeID_Manager. Sure it's a bit larger but at leas people won't go crazy trying to find table ManagerID, or what EmployeeIDis doing there. When querying is WHERE I would simplify it as: SELECT EmployeeID_Manager as ManagerID FROM Store

此外，由于“某种关系”或角色，该线程中有一些关于重复列的有效帖子。例如，如果 Store 有一个EmployeeID，这告诉我蹲下。所以我有时会做类似Store.EmployeeID_Manager 的事情。当然它有点大，但至少人们不会疯狂地寻找表 ManagerID，或者EmployeeID在那里做什么。当查询是 WHERE 时，我会将其简化为：SELECT EmployeeID_Manager as ManagerID FROM Store

回答by Michael Brown

For the sake of simplicity most people name the column on the table ID. If it has a foreign key reference on another table, then they explicity call it InvoiceID (to use your example) in the case of joins, you are aliasing the table anyway so the explicit inv.ID is still simpler than inv.InvoiceID

为简单起见，大多数人在表 ID 上命名列。如果它在另一个表上有外键引用，那么在连接的情况下，他们明确称其为 InvoiceID（以使用您的示例），无论如何您都在为表设置别名，因此显式 inv.ID 仍然比 inv.InvoiceID 简单