Why do people recommend not using the name “Id” for an identity column

identitynaming convention

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.

Best Answer

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.

Related Question