It is not good practice to superfluous designs. I.e. - it is not good practice to always have an auto increment int primary key when one is not needed.
Let's see an example where one is not needed.
You have a table for articles–this has an int primary key
id, and a varchar column named
You also have a table full of article categories–
id int primary key, varchar
One row in the Articles table has an
id of 5, and a
title "How to cook goose with butter". You want to link that article with the following rows in your Categories table: "Fowl" (id: 20), "Goose" (id: 12), "Cooking" (id: 2), "Butter" (id: 9).
Now, you have 2 tables: articles and categories. How do you create the relationship between the two?
You could have a table with 3 columns:
id (primary key), article_id (foreign key), category_id (foreign key). But now you have something like:
| id | a_id | c_id |
| 1 | 5 | 20 |
| 2 | 5 | 12 |
| 3 | 5 | 2 |
A better solution is to have a primary key that is made up of 2 columns.
| a_id | c_id |
| 5 | 20 |
| 5 | 12 |
| 5 | 2 |
This can be accomplished by doing:
create table articles_categories (
primary key (article_id, category_id)
Another reason not to use an auto increment integer is if you are using UUIDs for your primary key.
UUIDs are by their definition unique, which accomplishes the same thing that using unique integers does. They also have their own added benefits (and cons) over integers. For instance, with a UUID, you know that the unique string you're referring to points to a particular data record; this is useful in cases where you do not have 1 central database, or where applications have the ability to create data records offline (then upload them to the database at a later date).
In the end, you need to not think about primary keys as a thing. You need to think of them as the function they perform. Why do you need primary keys? To be able to uniquely identify specific sets of data from a table using a field that will not be changed in the future. Do you need a particular column called
id to do this, or can you base this unique identification off of other (immutable) data?