The simplest data integrity constraint requires that a column contain a non-NULL value. The ANSI/ISO standard and most commercial SQL products support this constraint by allowing you to declare that a column is NOT NULL when the table containing the column is first created. The NOT NULL constraint is specified as part of the CREATE TABLE statement, described in Chapter 13.
When a column is declared NOT NULL, the DBMS enforces the constraint by ensuring the following:
- Every INSERT statement that adds a new row or rows to the table must specify a non-NULL data value for the column. An attempt to insert a row containing a NULL value (either explicitly or implicitly) results in an error.
- Every UPDATE statement that updates the column must assign it a non-NULL data value. Again, an attempt to update the column to a NULL value results in an error.
One disadvantage of the NOT NULL constraint is that it must usually be specified when a table is first created. Typically, you cannot go back to a previously created table and disallow NULL values for a column. Usually, this disadvantage is not serious because it’s obvious when the table is first created which columns should allow NULLs and which should not. There is also a potential logical problem with adding the NOT NULL constraint to an existing table. If one or more rows of that table already contain NULL values, then what should the DBMS do with those rows? They represent valid real-world objects, but they now violate the (new) required data constraint.
The inability to add a NOT NULL constraint to an existing table is also partly a result of the way most DBMS brands implement NULL values internally. Usually a DBMS reserves an extra byte in every stored row of data for each column that permits NULL values. The extra byte serves as a null indicator for the column and is set to some specified value to indicate a NULL value. When a column is defined as NOT NULL, the indicator byte is not present, saving disk storage space. Dynamically adding and removing NOT NULL constraints would thus require on-the-fly reconfiguration of the stored rows on the disk, which is not practical in a large database.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.