Antipattern: One Size Fits All
Let's see some caveats of using database keys traditionally.
Books, articles, and programming frameworks have established a cultural convention that every database table must have a primary key column with the following characteristics:
-
The primary key’s column name is
id. -
Its data type is a 32-bit or 64-bit integer.
-
Unique values are generated automatically.
The presence of a column named id in every table is so common that this has become synonymous with a primary key. Programmers learning SQL get the false idea that a primary key always means a column defined in this manner.
CREATE TABLE Bugs (id SERIAL PRIMARY KEY,description VARCHAR(1000),-- . . .);
Adding an id column to every table causes several effects that make its use seem arbitrary.
Making a redundant key
We may sometimes see an id column defined as the primary key simply for the sake of tradition, even when another column in the same table could be used as the natural primary key. The other column may even be defined with a UNIQUE ...