Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
8  
BF bunfight here already: programmers.stackexchange.com/q/114728/5905 Several of us (read: me) got sucked into it... –  gbn Apr 17 '12 at 13:52
    
@gbn Thanks, I was actually reading that earlier. It seems that the top opinion is that it isn't really bad practice, and the only answer that even begins to give a good reason for why it might be bad practice is yours, which is a ways down the list. I'm still undecided if the reasons you mention are worth using a longer naming convention though (of course, I'm a programmer, not a dba, so I have to either alias them in code, or type these long Id fields out frequently and remember what letter they start with for autocomplete to kick in) –  Rachel Apr 17 '12 at 13:56
2  
in the highest voted answer, you'll have several id columns using SELECT *. Poor example, but chad's answer also covers the issue about ambiguity too. As it stands, the DB types here will agree with me... As for your reason above, you always have table.tableID. Never table.randomID. –  gbn Apr 17 '12 at 14:01
2  
@Rachel: Did your third-party vendor also turn all their volume levels up to 11? ;) –  nicodemus13 Apr 18 '12 at 9:47
1  
Well, SELECT * is useful when you're doing SELECT * FROM ( SELECT field1, field2, ...) WHERE value = windowedValue. :-) –  SarekOfVulcan Apr 18 '12 at 11:37

10 Answers 10

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.

share|improve this answer
8  
So it's basically a naming convention to protect against errors? I would think using begin transaction and commit transaction would be better practice than using (imo) a more obnoxious naming scheme –  Rachel Apr 17 '12 at 13:51
10  
@Rachel: it's for 1. clarity 2. avoid unnecessary column aliases 3. allow JOIN..USING 4. annoy the PHP monkeys who work in single objects, not sets –  gbn Apr 17 '12 at 13:54
4  
@Rachel If you didn't notice the mistake when writing the query, and just before you executed it, its unlikely you'll notice it before committing. This stuff happens, why make it more likely? –  Andy Apr 17 '12 at 16:28
5  
@Andy I always do a SELECT to find my records before I run the DELETE, and once I run the statement I always verify that the row count is what I expect before committing. –  Rachel Apr 17 '12 at 16:49
5  
I've literally never had this problem. Ever. Seems pretty contrived, compared with the benefits stated by the OP. –  John Buchanan Apr 17 '12 at 23:42

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

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)
share|improve this answer
2  
+1 for both INNER JOIN ... USING and readibility of code. Yes it matter. –  FabienAndre Apr 18 '12 at 11:30

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.

share|improve this answer
2  
making something that can be named consistantly across tables in foreign keys is the best reason –  JamesRyan Apr 18 '12 at 10:28
1  
I'm sure that not using single letter aliases would help (making this error ON customer.id = customerAddress.id pretty obvious) –  João Portela Apr 18 '12 at 13:19
1  
Then you're stuck writing ON Customer.id = CustomerAddress.customer_id, which isn't exactly consistent either. –  db2 Apr 18 '12 at 13:23
    
+1 @JNK made it sound like a useless thing. –  SalmanPK Mar 8 '13 at 1:00

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.

share|improve this answer
1  
+1 for mentioning USING() –  Neil McGuigan Apr 18 '12 at 3:08

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.

share|improve this answer
up vote 10 down vote accepted

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 :)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.