Search⌘ K
AI Features
Home
Courses
SQL Antipatterns: Avoiding the Pitfalls of Database Programming

Antipattern: One Size Fits All

Explore why the common practice of naming every table's primary key as id leads to redundancy, possible duplicate records, and less clear queries. Understand how natural and compound keys can better reflect real-world data structures and improve database clarity while avoiding unnecessary columns.

We'll cover the following...

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.

MySQL
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 ...