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 title
.
You also have a table full of article categories–id
int primary key, varchar name
.
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 (
article_id bigint,
category_id bigint,
primary key (article_id, category_id)
) engine=InnoDB;
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?
t
, and asset 120 at timet + 60
. If you can see both of those IDs (100 and 120) in unobfuscated form, you now know the total number of assets which exist, as well as roughly the rate at which they're created. This is information leakage. This is not purely hypothetical.