Why do people recommend not using the name "Id" for an identity column?

Question

I was taught not to use the name Id for the identity column of my tables, but lately I've just been using it anyways because it's simple, short, and very descriptive about what the data actually is.

I've seen people suggest prefixing Id with the table name, but this just seems to make more work for the person writing the SQL queries (or the programmer if you're using an ORM like Entity Framework), particularly on longer table names such as CustomerProductId or AgencyGroupAssignementId

One third-party vendor we hired to create something for us actually named all their identity columns Ident just to avoid using Id. At first I thought they did that because Id was a keyword, but when I looked into it, I found that Id isn't a keyword in SQL Server 2005, which is what we are using.

So why do people recommend not using the name Id for an identity column?

Edit: To clarify, I am not asking which naming convention to use, or for arguments to use one naming convention over the other. I just want to know why it's recommended to not use Id for the identity column name.

I'm a single programmer, not a dba, and to me the database is just a place to store my data. Since I usually build small apps, and typically use an ORM for data access, a common field name for the identity field is much easier to work with. I want to know what I am missing out on by doing this, and if there are any really good reasons for me not to do this.

Answer 1

The table name prefix has very good reasons.

Consider:

TableA (id int identity, stringdata varchar(max))

TableB (id int identity, stringdata varchar(max))

We want to DELETE from TableA records that exist in both tables. Easy enough, we will just do an INNER JOIN:

DELETE a
FROM 
  TableA A
INNER JOIN 
  TableB B
    ON b.id = B.id

....and we just wiped out all of TableA. We inadvertently compared B's ID to itself - every record matched, and every record got deleted.

If the fields had been named TableAId and TableBId this would be impossible (Invalid field name TableAid in TableB).

Personally I have no issue with using the name id in a table, but it's really a better practice to preface it with the table name (or entity name, if TableA were people then PeopleId would work fine too) to avoid accidentally comparing to the wrong field and blowing something up.

This also makes it very obvious where fields come from in long queries with lots of JOINs.

Answer 2

Mostly it's to keep foreign keys from becoming a tremendous pain. Let's say you have two tables: Customer and CustomerAddress. The primary key for both is a column named id, which is an identity (int) column.

Now you need to have the customer ID referenced from CustomerAddress. You can't name the column id, obviously, so you go with customer_id.

This leads to a couple of issues. First, you have to consistently remember when to call the column "id", and when to call it "customer_id". And if you mess this up, it leads to the second problem. If you've got a large query with a dozen or so joins, and it's not returning any data, have fun playing Where's Waldo and hunting down this typo:

ON c.id = ca.id

Whoops, should have been ON c.id = ca.customer_id. Or better yet, name your identity columns descriptively, so it can be ON c.customer_id = ca.customer_id. Then if you accidentally use the wrong table alias somewhere, customer_id won't be a column in that table, and you'll get a nice compilation error, rather than empty results and subsequent code squinting.

Granted, there are cases where this doesn't help, such as if you need multiple foreign key relationships from one table to another single table, but naming all the primary keys "id" doesn't help any there either.

Answer 3

Here's a summary of all the answers about the advantages obtained from the convention to not use a common name for all primary keys:

• Less mistakes, since the identity fields are not named the same

  You cannot mistakenly write a query that joins on B.Id = B.Id instead of A.Id = B.Id, because the identity fields will never be named the exact same.

• Clearer column names.

  If you look at a column named CustomerId, you immediately know what data is in that column. If the column name was something generic like Id, then you need to know the table name as well to know what data the column contains.

• Avoids unnecessary column aliases

  You can now write SELECT CustomerId, ProductId from a query that joins Customers with Products, instead of SELECT Customer.Id as CustomerId, Products.Id as ProductId

• Allows the JOIN..USING syntax

  You can join tables with the syntax Customer JOIN Products USING (CustomerId), instead of Customer JOIN Products ON Customer.Id = Products.Id

• The key is easier to find in searches

  If you are looking for a customer's identity field in a large solution, searching for CustomerId is far more useful than searching for Id

If you can think of any other advantages this naming convention has, let me know and I'll add it to the list.

Whether you choose to use unique or identical column names for identity fields is up to you, but regardless of what you choose, please be consistent :)

Answer 4

To copy my answer from the linked question:

There is a situation where sticking "ID" on every table isn't the best idea: the USING keyword, if it's supported. We use it often in MySQL.

For example, if you have fooTable with column fooTableId and barTable with foreign key fooTableId, then your queries can be constructed as such:

SELECT fooTableId, fooField1, barField2 FROM fooTable INNER JOIN barTable USING (fooTableId)

It not only saves typing, but is much more readable compared to the alternative:

SELECT fooTable.Id, fooField1, barField2 FROM fooTable INNER JOIN barTable ON (fooTable.Id = barTable.foTableId)

Answer 5

After normalizing a database schema to limit redundancy, tables are divided in smaller tables with established relations (one to one, one to many, many to many). In the process single fields in the original table can appear in multiple normalized tables.

For instance a database for a blog could look like this in its unnormalized form, assuming a unique constraint on the Author_Nickname.

| Author_Nickname | Author_Email | Post_Title | Post_Body |
+-----------------+--------------+------------+-----------+
| dave            | dave@x.com   | Blah       | Bla bla   |
| dave            | dave@x.com   | Stuff      | I like    |
| sophie          | s@oph.ie     | Lorem      | Ipsum     |

Normalizing it would yield two tables:

Author:

| Author_Nickname | Author_Email |
+-----------------+--------------+
| dave            | dave@x.com   |
| sophie          | s@oph.ie     |

Post

| Author_Nickname | Post_Title | Post_Body |
+-----------------+------------+-----------+
| dave            | Blah       | Bla bla   |
| dave            | Stuff      | I like    |
| sophie          | Lorem      | Ipsum     |

Here Author_Nickname would be a primary key for the author table, and a foreign key in the post table. Even if Author_Nickname appears in two table, it still corresponds to a single unit of information, ie. each column name corresponds to a single field.

In many cases there can't be a unique constraint on the original fields, so a numeric artificial field is used as primary key instead. This doesn't change the fact that each column name still represents a single field. In traditional database design, individual column names correspond to single fields even if they aren't keys. (eg, one would use part.partname and client.clientname rather than part.name and client.name). This is the reason for the existance of the INNER JOIN ... USING <key> and the NATURAL JOIN syntaxes.

However, nowadays, and with ORM layers readily available in many languages, databases are often designed as a persistance layer for OO languages, in which it is natural that variables that have the same role in different classes are called the same (part.name and client.name, not part.partname and client.clientname). In such a context, I tend to use 'ID' for my primary keys.

Answer 6

One third-party vendor we hired to create something for us actually named all their identity columns Ident just to avoid using Id.

Using "Ident" instead of "Id" doesn't really solve anything if "Ident" ends-up being used on all of their tables.

There's a good article on SQL coding conventions on the Drupal site that indicates a good practice for this situation:

It is a good practice to prefix table names with the module name to prevent possible namespace conflicts.

From this standpoint, CustomerProductId and AgencyGroupAssignmentId make sense to use. Yes, it's quite verbose. You could shorten it, but the biggest point to be concerned with then is whether or not the developer who follows you will understand what you meant. Ids prefaced with verbose table names shouldn't leave ambiguity as to what they are. And (to me) that's more important than saving a few keystrokes.

Answer 7

I name my columns CustomerID instead of ID, so whenever I type

FROM dbo.Customers AS c JOIN dbo.CustomerOrders AS o

SQL Prompt immediately suggests the following

ON c.CustomerID = o.CustomerID 

It saves me a few keystrokes. Yet I think naming conventions are very subjective and as such I don't have a strong opinion one way or another.

Answer 8

It's the same reason why you wouldn't name all of your varchar fields something like "UserText" and "UserText1", or why you wouldn't use "UserDate" and "UserDate1".

Typicaly if you have an identity field in a table it's your primary key. How would you build a child table with a foreign key to a parent table if the primary key in both tables was id?

Not everyone agrees with this methodolgoy, but in my databases I assign a unique abbreviation to each table. The PK for that table would be named PK_[abbrv]ID. IF that's used as an FK anywhere then I would use FK_[abbrv]ID. Now I have zero guess work on figuring out what the table relationships are.

Answer 9

Basically for the same reason you don't typically name parameters parameter1, parameter2...it's accurate, but not descriptive. If you see TableId, then you can probably safely assume that it's used to hold a pk for Table, regardless of context.

As for whoever used Ident, he entirely misses the point, given a choice between Ident and Id use Id. Ident is even more confusing than Id.

Out of context, Id can be assumed to be the primary key to some table (not extremely useful unless the id is a guid), but Ident doesn't even tell you (or at least me) that. I would eventually figure out that Ident is short for identity (one way or another), but the time I spent figuring that out would be wasted.

Answer 10

Use a prefix so that the same name can be used in both primary key and foreign key contexts, so that you can perform natural join/join ... using.