Why is naming a table's Primary Key column "Id" considered bad practice?

Question

My t-sql teacher told us that naming our PK column "Id" is considered bad practice without any further explanations.

Why is naming a table PK column "Id" is considered bad practice?

Answer 1

I'm going to come out and say it: It's not really a bad practice (and even if it is, its not that bad).

You could make the argument (as Chad pointed out) that it can mask errors like in the following query:

SELECT * 
    FROM cars car
    JOIN manufacturer mfg
        ON mfg.Id = car.ManufacturerId
    JOIN models mod
        ON mod.Id = car.ModelId
    JOIN colors col
        ON mfg.Id = car.ColorId

but this can easily be mitigated by not using tiny aliases for your table names:

SELECT * 
    FROM cars
    JOIN manufacturer
        ON manufacturer.Id = cars.ManufacturerId
    JOIN models
        ON models.Id = cars.ModelId
    JOIN colors
        ON manufacturer.Id = cars.ColorId

The practice of ALWAYS using 3 letter abbreviations seems much worse to me than using the column name id. (Case in point: who would actually abbreviate the table name cars with the abbreviation car? What end does that serve?)

The point is: be consistent. If your company uses Id and you commonly make the error above, then get in the habit of using full table names. If your company bans the Id column, take it in stride and use whatever naming convention they prefer.

Focus on learning things that are ACTUALLY bad practices (such as multiple nested correlated sub queries) rather than mulling over issues like this. The issue of naming your columns "ID" is closer to being a matter of taste than it is to being a bad practice.

---

A NOTE TO EDITORS : The error in this query is intentional and is being used to make a point. Please read the full answer before editing.

Answer 2

Because when you have a table with a foreign key you can't name that foreign key "Id". You have table name it TableId

And then your join looks like

SELECT * FROM cars c JOIN manufacturer m ON m.Id = c.ManufacturerId

And ideally, your condition should have the same field name on each sides

SELECT * FROM cars c JOIN manufacturer m ON m.ManufacturerId = c.ManufacturerId

So while it seems redundant to name the Id as ManufacturerId, it makes it less likely that you have errors in your join conditions as mistakes become obvious.

This seems simple, but when you join several tables, it gets more likely you'll make a mistake, find the one below...

SELECT * 
    FROM cars car 
    JOIN manufacturer mfg
        ON mfg.Id = car.ManufacturerId
    JOIN models mod
        ON mod.Id = car.ModelId
    JOIN colors col
        ON mfg.Id = car.ColorId

Whereas with proper naming, the error sticks out...

SELECT * 
    FROM cars car 
    JOIN manufacturer mfg
        ON mfg.ManufacturerId = car.ManufacturerId
    JOIN models mod
        ON mod.ModelId = car.ModelId
    JOIN colors col
        ON mfg.ManufacturerId = car.ColorId

Another reason naming them Id is "bad" is that when you are querying for information from several tables you will need to rename the Id columns so you can distinguish them.

SELECT   manufacturer.Id as 'ManufacturerId'
        ,cars.Id as 'CarId'
        --etc
    FROM cars 
    JOIN manufacturer
        ON manufacturer.Id = cars.Id

With accurate names this is less of an issue

Answer 3

Ruby's ActiveRecord library and Groovy's GORM use "id" for the surrogate key by default. I like this practice. Duplicating the table name in each column name is redundant, tedious to write, and more tedious to read.

Answer 4

Common or key column names like "Name" or "Id" should be prefixed with the TableName.

It removes ambiguity, easier to search for, means far less column aliases when both "Id" values are needed.

A lesser used or audit column or non-key (say LastUpdatedDateTime) doesn't matter

Answer 5

This thread is dead, but I would like to add that IMO not using Id is a bad practice. The Id column is special; it is the primary key. Any table can have any number of foreign keys, but it can have only one key that is primary. In a database where all primary keys are called Id, as soon as you look at the table you know exactly which column is the primary key.

Believe me, for months now I've spent all day every day working in lots of big databases (Salesforce) and the best thing I can say about the schemas is that every table has a primary key called Id. I can assure you I absolutely never get confused about joining a primary key to a foreign key because the PK is called Id. Another thing that people haven't mentioned is that tables can have long silly names like Table_ThatDoesGood_stuff__c; that name is bad enough because the architect had a hangover the morning he thought up that table , but now you are telling me that it's bad practice not to call the primary key Table_ThatDoesGood_stuff__cId (remembering that SQL column names aren't in general case sensitive).

To be honest, the problems with with most people who teach computer programming are that they haven't written a line of production code in years, if ever, and they have no idea what a working software engineer actually does. Wait until you start working and then make up your own mind what you think is a a good idea or not.

Answer 6

From data.stackexchange.com

BOOM, question answered.
Now go tell your teacher that SO practice bad database design.

Answer 7

I don't consider it bad practice. Consistency is king, as usual.

I think it's all about context. In the context of the table on its own, "id" just means exactly what you expect, a label to help uniquely identify it against others that might otherwise be (or appear) identical.

In the context of joins, it's your responsibility to construct the joins in such a way as to make it readable to you and your team. Just as it is possible to make things look difficult with poor phrasing or naming, it is equally possible to construct a meaningful query with effective use of aliases and even comments.

In the same way a Java class called 'Foo' doesn't have its properties prefixed by 'Foo', don't feel obliged to prefix your table IDs with table names. It is usually clear in context what the ID being referred to is.

Answer 8

It makes it hard (and confusing) to perform a natural join on the table, therefore yeah, it's bad if not very bad.

Natural Join is an ancient artifact of SQL Lore (i.e. relational algebra) you may have seen one of these: ⋈ in a database book perhaps. What I mean is Natrual Join is not a new fangled SQL idea, even though it seemed to take forever for DBMS's to have implemented it, therefore it's not a new fangled idea for you to implement it, it might even be unreasonable for you to ignore its existence nowadays.

Well, if you name all your primary key's ID, then you lose the ease and simplicity of the natural join. select * from dudes natural join cars will need to be written select * from dudes inner join cars where cars.dudeid = dudes.id or select * from dudes inner join cars where dudes.carid = cars.id. If you are able to do a natural join, you get to ignore what the relation actually is, which, I believe, is pretty awesome.

Answer 9

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 10

Why not just ask your teacher?

Think about this, when all your tables PK columns are named ID it makes using them as foreign keys a nightmare.

Column names need to be semantically significant. ID is to generic.

Answer 11

ID is bad for the following reasons:

If you do a lot of reporting queries you always have to alias the columns if you want to see both. So it becomes a waste of time when you could name it properly to begin with. These complex queries are hard enough (I write queries that can be hundreds of lines long) without the added burden of doing unnecessary work.

It is subject to causing code errors. If you use a database that allows the use of the natural join (not that I think you should ever use that but when features are available somebody will use them), you will join on the wrong thing if you get a developer that uses it.

If you are copying joins to create a complex query, it is easy to forget to change the alias to the one you want and get an incorrect join. If each id is named after the table it is in, then you will usually get a syntax error. It is also easier to spot if the join ina complex query is incorrect if the pPK name and the FK name match.

Answer 12

The practice of using Id as primary key field leads to the practice where id gets added to every table. A lot of tables already have unique information that uniquely identifies a record. Use THAT as primary key and not an id field you add to each and every table. That's one of the foundations of relational databases.

And that's why using id is bad practice: id is often not information just an autoincrease.

consider the following tables:

PK id | Countryid   | Countryname
    1 |         840 | United States
    2 |         528 | the Netherlands

What's wrong with this table is that it enables the user to add another line: United States, with countrycode 840. It just broke relational integrity. Ofcourse you can enforce uniqueness on individual columns, or you could just use a primary key that's already available:

PK Countryid   | Countryname
           840 | United States
           528 | the Netherlands

That way you use the information you already have as primary key, which is at the heart of relational database design.

Answer 13

There are some answers that approach what I would consider the most important reason for not using "id" as the column name for the primary key in a table: namely consistency and reduced ambiguity.

However, for me the key benefit is realized by the maintenance programmer, in particular one who was not involved with the original development. If you used the name "PersonID" for the ID in the Person table and consistently used that name as a foreign key, it is trivial to write a query against the schema to find out what tables have PersonID without having to infer that "PersonID" is the name used when it is a foreign key. Remember, right or wrong, foreign key relationships are not always enforced in all projects.

There is an edge case where one table may need to have two foreign keys to the same table, but in such cases I would put the original key name as the suffix name for the column, so a wildcard match, %PersonID, could easily find those instances as well.

Yes, much of this could be accomplished by a standard of having "id" and knowing to always use it as "tableNameID", but that requires both knowing that the practice is in place and depending on the original developers to follow through with a less intuitive standard practice.

While some people have pointed out that it does require some extra key strokes to write out the longer column names, I would posit that writing the code is only a small fraction of the active life of the program. If saving developer keystrokes was the goal, comments should never be written.

As someone who has spent many years maintaining large projects with hundreds of tables, I would strongly prefer consistent names for a key across tables.

Answer 14

I always use 'id' as the primary column name for every table simply because it's the convention of the frameworks I use (Ruby on Rails, CakePHP), so I don't have to override it all the time.

That won't beat academic reasons for me.

Answer 15

I don't think it's a bad practice if it's used properly. It's common to have an auto-incrementing ID field called "ID" that you never have to touch, and use a friendlier identifier for the application. It can be a little cumbersome to write code like from tableA a inner join tableB b on a.id = b.a_id but that code can be tucked away.

As a personal preference I tend to prefix the Id with the name of the entity, but I don't see a real issue with just using Id if it's handled entirely by the database.

Answer 16

ID is common enough, that I don't think it would confuse anyone. You're always going to want to know the table. Putting fields names in production code without including a table/alias is a bad practice. If you're overly concerned about being able to quickly type ad hoc queries, you're on your own.

Just hope no one develops a sql database where ID is a reserved word.

CREATE TABLE CAR (ID);

Takes care of the field name, primary key, and auto increments by 1 starting with 1 all in one nice little 2 character package. Oh, and I would have called it CARS but if we're going to save on key-strokes and who really thinks a table called CAR is going to only have one?

Answer 17

This question has been beaten over and over again, but I thought that I too would add my opinion.

1. I use id to mean that that is the identifier for each table, so when I join to a table and I need the primary key I automatically join to the primary key.

2. The id field is an autoincrement, unsigned (meaning that I never have to set its value and it cannot be negative)

3. For foreign keys, I use tablenameid (again a matter of style), but the primary key I join to is the id field of the table, so the consistency means I can always check queries easily

4. id is short and sweet too

5. Additional convention - use lower case for all table and column names, so no issues to be found due to case

Answer 18

Another thing to consider is that if the primary key name is different from the foreign key name, then it is not possible to use certain third party tools.

For example, you would be unable to load your schema into a tool like Visio and have it produce accurate ERD's.

Answer 19

I find people here cover pretty much every aspect but I want to add that "id" is not and should not be read as "identifier" it's more of an "index" and surely it does not state or describe the row's identity. (I may have used wrong wording here, please correct me if I did)

It's more or less how people read the table data and how they write their code. I personally and most likely this is the most popular way I see more frequently is that coders write the full reference as table.id, even if they don't need to do union's or/and joins. For example:

SELECT cars.color, cars.model FROM cars WHERE cars.id = <some_var>

That way you can translate it to English as "Give me color and model of that car that is numbered as ." and not as "Give me color and model of that car that is identified as number ." The ID does not represent the car in any way, it's only car's index, a serial number if you will. Just like when you want to take the third element from an array.

So to sum up what I wanted to add is that it's just a matter of preference and the described way of reading SQL is the most popular.

Though, there are some cases where this is not used, such as (a far more rare example) when the ID is a string that is really describing. For example id = "RedFordMustang1970" or something similar. I really hope I could explain this at least to get the idea.