In my databases, I tend to get into the habit of having an auto-incrementing integer primary key with the name id for every table I make so that I have a unique lookup for any particular row.
Is this considered a bad idea? Are there any drawbacks to doing it this way? Sometimes I'll have multiple indices like id, profile_id, subscriptions where id is the unique identifier, profile_id links to the foreign id of a Profile table, etc.
Or are there scenarios where you don't want to add such a field?
Best Answer
It's never a bad idea to have a guaranteed unique row identifier. I guess I shouldn't say never – but let's go with the overwhelming majority of the time it's a good idea.
Theoretical potential downsides include an extra index to maintain and extra storage space used. That's never been enough of a reason to me to not use one.