231

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?

19
  • 28
    Well, it is not a description actually and Id means "identity" which is very self explanatory. That is my opinion. Oct 17, 2011 at 16:48
  • 50
    I'm sure there are plenty of shops that use Id as a PK name. I personally use TableId as my naming convention but I wouldn't tell anyone its THE ONE TRUE WAY. It sounds like your teacher is just trying to present her opinion as widely accepted best practice.
    – user29776
    Oct 17, 2011 at 16:58
  • 23
    Definitively the type of bad practice that isn't that bad. The point is to get consistent. If you use id, use it everywhere or don't use it.
    – deadalnix
    Oct 17, 2011 at 17:00
  • 63
    You have a table...it's called People, it's got a column, called Id, what do you think the Id is? A car? A Boat? ...no it's the People Id, that's it. I don't think it's not a bad practice and it's not necessary to name the PK column anything other than Id.
    – jim
    Oct 17, 2011 at 18:17
  • 43
    So the teacher gets in front of the class and tells you this is a bad practice without a single reason? That's a worse practice for a teacher.
    – JeffO
    Oct 19, 2011 at 14:30

19 Answers 19

262

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.

19
  • 1
    @Chad, it was a reference to the fact that in that particular query you used 3 letters for aliases to table names, even when it made no sense (cars -> car. Thank God--you saved my fingers). Don't read too deeply into it.
    – riwalk
    Oct 17, 2011 at 17:32
  • 4
    worse than my alias names, was my mixing of plurality cars and manufacturer. One is plural, the other isn't. If people want to pick at the db, that's the bad practice that should be picked upon.
    – CaffGeek
    Oct 17, 2011 at 17:34
  • 3
    I think it is bad practice. Obviously, as far as bad practice goes it's not terrible.. but it's so easy to avoid, why not do so? Now, I agree, for an intro class, is this the thing to focus on? Probably not....
    – user606723
    Oct 17, 2011 at 19:41
  • 2
    @user606723, actually, I think for an intro class it's an important thing to make a point of. Teaching best practices should be fundamental. It's not until you're experienced that you understand the consequences, and tradeoffs and when you should deviate from best practices.
    – CaffGeek
    Oct 17, 2011 at 19:53
  • 5
    @Chad, Agree to disagree. Trying to teach students "best practices" without allowing them to understand why those are the best practices is a futile effort. And since you can't cover everything, glossing over this point with "you just shouldn't do it, you'll figure out why later" is pretty sane choice from a professor's standpoint. Curious students can post on here or hopefully find this question already answered. (Or ask after class)
    – user606723
    Oct 17, 2011 at 20:01
134

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

23
  • 193
    Not sure this is a good enough explanation to me. There's nothing wrong with saying SELECT * FROM cars c JOIN manufacturer m ON manufacturer.Id = c.ManufacturerId. I have used id for years and never found what you described to be a real problem.
    – riwalk
    Oct 17, 2011 at 16:56
  • 64
    I would say that the bad practice here is to alias tables with name like mfg or mod. manufacturers.id = cars.manufacturer_id is very readable and the error will stick out too.
    – deadalnix
    Oct 17, 2011 at 16:59
  • 7
    @Chad > I already got problems with dumb variable names. Many times. For the reccord, here what I would say to a dev that does this in my team « mfg doesn't mean manufacturer, it means you are to lazy to type manufacturer ».
    – deadalnix
    Oct 17, 2011 at 17:12
  • 10
    @Stargazer712: Doing SELECT * from 2 tables gives 2 x ID columns. ID is now ambiguous: do you reference by ordinal or name? SELECT * is not good practice either. Poor arguments. Fragile code. Chad is correct: defensive coding basically
    – gbn
    Oct 17, 2011 at 18:23
  • 7
    @gbn, again, all ambiguity is gone if you simply do SELECT manufacturer.id FROM .... Every difficulty resulting from id can be overcome very easily, making it simply a matter of taste.
    – riwalk
    Oct 17, 2011 at 19:25
76

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.

6
  • 33
    +1 for "and more tedious to read." - naming conventions shouldn't be thought of as a band-aid for sloppy code, they should be improving readability as a primary concern.
    – ocodo
    Oct 18, 2011 at 1:30
  • 15
    ID is far more tedious to read
    – HLGEM
    Oct 18, 2011 at 20:20
  • 7
    @HLGEM: One can always qualify the column name with the table name. Oct 20, 2011 at 21:10
  • 2
    I would agree except for the more tedious to read. I actually prefer reading more descriptive column names and spending less time figuring out what that column actually is for. Mar 20, 2013 at 20:41
  • 2
    +1, Hate seeing tables with columns like Posts.postID, Posts.postName where simply using post.id and post.name is far prettier.
    – Doug
    May 12, 2017 at 0:14
40

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

16
  • 65
    If you do this, I hate you for making me do extra typing!!!! The table's name is Person, what do you think the Id is going to be? Car? no, it's Person.
    – jim
    Oct 17, 2011 at 18:19
  • 18
    @jim, I don't know about you, but typing 6 extra characters takes me roughly half a second. And considering I rarely ever select from one table, and thus would end up with two columns named 'Id' and will need to include the table/alias name anyhow, there is no savings in the number of characters typed.
    – CaffGeek
    Oct 17, 2011 at 19:13
  • 24
    @Chad I find it superfluous. if I'm doing a join, c.id = m.manufacturerid, is ok with me. These columns are typically "mapped" to a class somehow, and to have a class with Person.PersonId makes me want to vomit...Yes,I am fully aware I have issues.
    – jim
    Oct 17, 2011 at 19:26
  • 22
    I also disagree with this. Why stop at name and id? Why not have every column prefixed with its table name? It seems arbitrary to pick those two names to mandate a prefix. Conceptually, you must have the table in order to have the context of a column anyway. Why not just use that table name to clarify the query: Person.Name, Animal.Name, Part.Name,...
    – Thomas
    Oct 18, 2011 at 17:27
  • 12
    @Bill Leeper, DRY is not always appropriate in database development. In databases what is important is performance and making the database do extra work to fullfill DRY principles (such as using scalar functions or views that call views or queries that return too many columns or using a cursor to add 1000000 records to use an existing proc) is often contraindicated. Do not think that just because something is good in the Object-oriented world that it is appropriate in database design. Your downvote was inappropriate. Using ID is a known SQL antipattern.
    – HLGEM
    Oct 20, 2011 at 21:38
36

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.

5
  • 2
    That's only the case if none of your primary keys is a composite key, which is, unfortuately, far too often the case. One should really only use surrogate keys in particular circumstances. May 17, 2012 at 16:32
  • 7
    Using Id like that you end up with a situation where without thinking developers add primary key id to each and every table they make. One of the foundations of relational databases is the use meaningfull and aggregate primary keys and using id does not help.
    – Pieter B
    Oct 20, 2012 at 14:35
  • This answer just seems like you're saying "I prefer Id" with opinionated arguments. Your argument is you can instantly see which key is the primary key by finding the one called Id. Well, it's the exact same with tableId. I can guarantee you I never get confused which key is the primary key either. I just look for the one that has the table name before the id. Not only that, but what kind of heathen tables are you working with where the first column isn't the primary key? I feel like all your arguments in this answer are purely preferential based and akin to "tableId feels wrong to me".
    – dallin
    Oct 13, 2018 at 20:53
  • 1
    @dallin all the answers are opinionated, otherwise someone would just link to the official standard, and there isn't one!
    – James
    Jun 3, 2019 at 23:44
  • @James I feel like the goal of StackExchange sites is to reduce opinionated answers. That's why questions get closed as being too opinionated. Granted, I think that's kind of why this question got closed - because it elicits opinionated answers, but I feel this specific answer was overly opinionated without any real supporting facts or arguments based on facts.The entire answer can be summarized by saying, "In my opinion, you should use Id, just because that's what I like". That's not a valuable answer.
    – dallin
    Jun 5, 2019 at 2:28
30

From data.stackexchange.com

Id in Posts

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

5
  • 10
    My guess at the FKs, based on the names: PostTypeId -> PostTypes.Id; AcceptedAnswerId -> Answers.Id; OwnerUserId -> Users.Id. Why should a practice that is that easy be considered 'bad'?
    – Sjoerd
    Apr 18, 2012 at 0:11
  • 4
    How exactly does this prove anything about best practices?
    – GBa
    Apr 18, 2012 at 15:52
  • 7
    Whether something is used at stack or not does not prove if it's good or bad practice.
    – Pieter B
    Oct 20, 2012 at 14:39
  • 2
    What it does prove is that this practice in no way prohibits the scalability and usefulness of an application.
    – Cypher
    Feb 13, 2015 at 20:49
  • 1
    Actually SO practice is not perfect. I would use this naming: PostType -> PostType.Id; AcceptedAnswer -> Answer.Id; OwnerUser -> User.Id
    – alpav
    Feb 17, 2015 at 2:10
24

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.

7
  • 5
    Relational database tables are not classes. Oct 18, 2011 at 1:23
  • 1
    They are however data structures and they're analogous to PODO classes. The same naming problems apply.
    – ocodo
    Oct 18, 2011 at 1:28
  • 4
    @Slomojo: No, they're not analogous to simple objects. Object-oriented design and database design are not the same, and are not even related. While they can, in some cases, yield similar (or even the same) design, that does not indicate that they are related. For example, m:m relationships are trivial in classes, but are impossible have between two tables without a third association table. Oct 18, 2011 at 1:55
  • 1
    Quite how this relates to a naming strategy, I don't know. My analogy is (clearly?) only scoped to that extent.
    – ocodo
    Oct 18, 2011 at 2:13
  • 2
    I'm sorry my implied meaning wasn't very clear, I should have said "in this sense they are analogous to classes". Either way, I don't think being overly pedantic about this is particularly constructive. In terms of naming, tables and classes do share a significant amount of similarities. Best practices which develop in a cul-de-sac are fair game for revision, or at the very least are open to discussion. There's plenty within this Q&A that illustrate this effectively, I don't have anything else of note to add.
    – ocodo
    Oct 18, 2011 at 22:36
17

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.

4
  • Unless you are writing a stored procedure when is the last time as an application developer your actually wrote a fully formatted SQL selector? Modern languages all include some sort of ORM feature that manages this relationship for you. The column name is far more important than being able to write clean manual SQL. Oct 18, 2011 at 15:48
  • 4
    @Bill I do all the time, many, many times a day, depends more on your codebase than the language you're developing. And, for diagnostics, if you want to do some good and deep relations you can string together those natural joins and completely avoid looking up field ID's. And, as St. Jerome famously said, "Ignorance of SQL is ignorance of databases". Oct 18, 2011 at 15:57
  • Aside from the fact that natural joins are not universally supported, IMO, natural joins are harder to read. Are there two columns in relationship or only one? Far better to be explicit and avoid natural joins.
    – Thomas
    Oct 18, 2011 at 17:33
  • 1
    @Thomas, I wouldn't put natural joins in code either, but for diagnostics, I've found them pretty useful when the database is modeled so that they actually work. Oct 18, 2011 at 17:40
17

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)
1
  • This is the answer that sticks out most for me. The USING keyword is supported by postgres/mysql/sqlite database, means less typing which some of the other answers list as a reason for using id, and finally in my subjective opinion is more readable. Oct 11, 2016 at 17:25
12

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.

6
  • 9
    too generic for what? the id of a table?
    – jim
    Oct 17, 2011 at 18:24
  • 3
    @Jim of which table? id alone doesn't mean anything, especially in the context of a foreign key to another table. This has nothing to do with classes and everything to do with good basic fundamental relational database design.
    – user7519
    Oct 17, 2011 at 21:32
  • 12
    To be slightly fatuous, the table which it belongs to. table.id is a perfectly acceptable way of referring to an id field. Prefixing the field name with the table name is redundant.
    – ocodo
    Oct 18, 2011 at 1:21
  • 3
    @Slomoj it is no more typing than including the name in the column and more explict when aliasing table names to single or double letter abbreviations in monster joins.
    – user7519
    Oct 18, 2011 at 3:06
  • 6
    Of what nightmare are you referring? Suppose you have a self-referencing structure of employees with a column representing their manager. What do you call the foreign key? You can't call it EmployeeId as that is presumably your PK. The name of the FK does not have to match the PK. It should be named for what it represents to the entity in which it is contained.
    – Thomas
    Oct 18, 2011 at 17:30
7

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.

5
  • +1: These are compelling reasons in my opinion, while the other answers opposing ID don't convince me at all.
    – phoog
    Apr 19, 2012 at 20:31
  • Re: "If you are copying joins to create a complex query" - so your problem is copy&paste. Stop copy&paste and you will see how convenient car.id naming is. For FK joining use car.mfg = mfg.id, car.color=color.id, car.model = model.id - very simple and matches what you would write in LINQ.
    – alpav
    Feb 17, 2015 at 2:06
  • Try writing something with 30 joins and you will see why it is an antipattern.
    – HLGEM
    Feb 18, 2015 at 14:19
  • Aliasing is the first and foremost reason - it is such a headache when doing big complex reports. Natural joins are just a sweet bonus. It's amazing how other answers just ignore this points. I think adding some examples would make the point clearer and jump this answer higher to where it should be.
    – Lulu
    Jul 29, 2018 at 19:01
  • With your solution you still have to alias the FK or PK because they have the same name in both tables. So your proposed solution creates more burden of typing and having to read longer descriptions. In longer queries you also have no idea when looking at table1.primary_key_name_id = table2.primary_key_name_id where the primary key actually resides. Disagree.
    – rball
    Jan 18, 2023 at 17:08
6

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.

8
  • 1
    You'll understand why people add a generic key column to every table when you've had to migrate databases between differing systems, or have had the pleasure of merging databases together.
    – Cypher
    Feb 13, 2015 at 20:56
  • 2
    This is occasionally the case, but in my experience it's pretty uncommon to have a nice unique immutable key. But even in your example you have a meaningless unique number to identify countries, just one that's allocated by ISO not by your database. Jun 22, 2015 at 14:33
  • And now when the country name changes, you've got to update it throughout your entire database to fix. If you had simply used a surrogate key (like..."id"), the update would be trivial. Natural keys are great - when they are completely immutable, and never change. There are actually very few cases where this is true.
    – Gerrat
    Mar 6, 2018 at 20:31
  • 1
    @Gerrat: If a country name changes, the ISO 3166-1 numeric code stays the same, only the ISO 3166-1 alpha-2 and alpha-3 codes change. This happened with Burma / Myanmar, for example. Likewise, if a country changes its territory but keeps its name, the ISO 3166-1 numeric code changes, but the ISO 3166-1 alpha-2 and alpha-3 codes stay. This happened when South Sudan split from Sudan and Eritrea split from Ethiopia. When East and West Germany reunited, they kept the alpha-2 and alpha-3 codes of West Germany but got a new numeric code. When countries completely dissolve or transform (think … Jun 9, 2019 at 15:18
  • … outcome of the Balkan wars of the 90s), they get both new numeric and alpha-2 and alpha-3 codes. Jun 9, 2019 at 15:19
5

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.

2
  • Suppose a Companies table has 2 foreign keys to a Persons table. One represents the company's President; the other represents the company's Treasurer. Would you really call the columns PersonID1 and PersonID2? It would be far more descriptive to call them PresidentID and TreasurerID. I find it much easier to read inner join Person AS Presidents ON Company.PresidentID = Presidents.ID than inner join Person AS Person1 ON Company.PersonID1 = Person1.PersonID
    – phoog
    Apr 19, 2012 at 20:28
  • 1
    No. In your example I would probably have a CompanyOfficer or CompanyPerson table which allows a many-to-many relationship with between Company and Person with additional information about the nature of the relationship. If I were to implement it within the Company table, I would use the column names PresidentPersonID and TreasurerPersonID to preserve the *PersonID part of the name while adding the additional descriptor. inner join Person as Presidents on Company.PresidentPersonID = Presidents.PersonID
    – Malachi
    Apr 25, 2012 at 16:28
4

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.

2

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.

2
  • You would never join two tables by eaches primary key especially an auto-increment id. The sample above would be from tableA a inner join tableB b on a.id = b.table_a_id. Oct 18, 2011 at 15:51
  • Yeah, that's what I meant. Almost lunchtime and need energy. Oct 18, 2011 at 15:54
2

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?

1
  • 1
    this shows why knowledge of formal relational theory is important, the table name represents what a single row is. The table Car represents a Table where each Row represents a single Car. Calling in Cars changes the semantic and shows a complete lack of understanding of formal relational theory basic principals. Rails is a prime example of someone that knew enough to be dangerous.
    – user7519
    May 6, 2015 at 19:44
2

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

2

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.

1
  • That would be the third party tool's fault though. Column naming conventions are no substitute for actual foreign keys, which the tool should introspect.
    – Gerrat
    Jun 11, 2019 at 19:49
1

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.

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