O'Reilly logo

SQL Antipatterns by Bill Karwin

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Antipattern: One Size Fits All

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.

ID-Required/anti/id-ubiquitous.sql
 
CREATE​ ​TABLE​ Bugs (
 
id SERIAL ​PRIMARY​ ​KEY​,
 
description ​VARCHAR​(1000),
 
-- . . .
 
);

Adding an id column to every table causes several effects ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required