SQL NOT NULL
NOT NULL says "this column must have a value on every row". Insert or update with NULL and the database raises an error.
At create time
SQL
CREATE TABLE customers ( id INT PRIMARY KEY, name VARCHAR(80) NOT NULL, email VARCHAR(120) NOT NULL );
Adding NOT NULL to an existing column
SQL
-- MySQL ALTER TABLE customers MODIFY email VARCHAR(120) NOT NULL; -- PostgreSQL / SQL Server ALTER TABLE customers ALTER COLUMN email SET NOT NULL;
If any existing row has NULL in that column, the alter fails. Backfill first:
SQL
UPDATE customers SET email = 'unknown@example.com' WHERE email IS NULL;
Drop NOT NULL
SQL
ALTER TABLE customers ALTER COLUMN email DROP NOT NULL;
Combine with DEFAULT
If you need the column always populated but the caller often won't pass it, pair with a default:
SQL
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
Tip: Default columns to
NOT NULL. Make NULL the exception, not the rule. Fewer NULLs means fewer "why didn't this WHERE match?" bugs.Example
Exercise
Disallow NULL in the name column.
name VARCHAR(80)
NULL
Three letters; the negation keyword.
Discussion
Loading…