6.9. Design Advice for NULLs

It is a good idea to declare all your base tables with NOT NULL constraints on all columns whenever possible. NULLs confuse people who do not know SQL, and NULLs are expensive. NULLs are usually implemented with an extra bit somewhere in the row where the column appears, rather than in the column itself. They adversely affect storage requirements, indexing, and searching.

NULLs are not permitted in PRIMARY KEY columns. Think about what a PRIMARY KEY that was NULL (or partially NULL) would mean. A NULL in a key means that the data model does not know what makes the entities in that table unique from each other. That in turn says that DBMS cannot decide whether the PRIMARY KEY does or does not duplicate a key that ...

Get Joe Celko's SQL for Smarties, 3rd Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.