Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
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
NULLvalues in a column, it's still better to formally communicate this fact through a constraint. - Performance: With
NULLbeing 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!