Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9

Schema: NULL columns

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

In most RDBMS, the default nullability on any column is NULL, even if NOT NULL is mostly a more reasonable default. Whenever you know your data is not supposed to contain NULL values, then add an explicit NOT NULL constraint. This has the following benefits:

  • Data integrity: One case of incorrect data less to worry about.
  • Documentation: Even if your client application might make sure you'll never get NULL values in a column, it's still better to formally communicate this fact through a constraint.
  • Performance: With NULL being an impossible value, quite a few optimisations can be applied that couldn't be, otherwise.

For example

CREATE TABLE customer (
  -- [...]
  phone   TEXT,          -- Here, the default of being nullable applies (in most RDBMS), but should it?
  address TEXT NULL,     -- The address might optional, you can mark it as such, explicitly, in many RDBMS
  email   TEXT NOT NULL  -- Every customer needs an email, this isn't an optional field
);

This rule obviously doesn't apply when a value is optional, in case of which NULL might be a desirable value.

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo