NOT NULL: a required value
By default a column accepts NULL. NOT NULL says the value is required. An insert without it fails (in strict mode).
query.sql
SQL
CREATE TABLE members (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(100) NOT NULL,
phone VARCHAR(20) NULL,
joined_on DATE NOT NULL DEFAULT (CURRENT_DATE),
plan VARCHAR(10) NOT NULL DEFAULT 'free',
credits INT NOT NULL DEFAULT 0
);
INSERT INTO members (email) VALUES ('asha@example.com');
INSERT INTO members (email, phone, plan) VALUES ('ben@example.com', NULL, 'pro');
SELECT id, email, phone, joined_on IS NOT NULL AS has_date, plan, credits FROM members;
Output
OUTPUT
+----+------------------+-------+----------+------+---------+
| id | email | phone | has_date | plan | credits |
+----+------------------+-------+----------+------+---------+
| 1 | asha@example.com | NULL | 1 | free | 0 |
| 2 | ben@example.com | NULL | 1 | pro | 0 |
+----+------------------+-------+----------+------+---------+
2 rows in setemail was required. phone is optional and stays NULL. The rest got their defaults.
query.sql
SQL
INSERT INTO members (phone) VALUES ('98450');
Output
OUTPUT
ERROR 1364 (HY000): Field 'email' doesn't have a default value
query.sql
SQL
INSERT INTO members (email, plan) VALUES ('chitra@example.com', NULL);
Output
OUTPUT
ERROR 1048 (23000): Column 'plan' cannot be nullDecide NULL or NOT NULL for each column
Make it NOT NULL | Allow NULL |
|---|---|
| The value always exists (email, created_at) | “Unknown” or “not applicable” is a real state (middle name, end date of an ongoing contract) |
| It has a sensible default (0, ‘free’) | The absence carries meaning |
NOT NULL columns are simpler: no three-valued logic (NULL), no surprises in NOT IN and in aggregates. Make columns NOT NULL unless you need NULL.
DEFAULT values
A default can be:
- A literal:
DEFAULT 0,DEFAULT 'free',DEFAULT NULL. CURRENT_TIMESTAMPfor date-time columns:created_at DATETIME DEFAULT CURRENT_TIMESTAMP.- An expression in brackets (MySQL 8.0.13 and later):
DEFAULT (CURRENT_DATE),DEFAULT (UUID_TO_BIN(UUID())),DEFAULT (JSON_ARRAY()).
query.sql
SQL
CREATE TABLE tokens (
id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID())),
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
expires_at DATETIME NOT NULL DEFAULT (CURRENT_TIMESTAMP + INTERVAL 1 DAY),
meta JSON NOT NULL DEFAULT (JSON_OBJECT())
);
INSERT INTO tokens () VALUES ();
SELECT LENGTH(id) AS id_bytes, expires_at > created_at AS expires_later, meta FROM tokens;
Output
OUTPUT
+----------+---------------+------+
| id_bytes | expires_later | meta |
+----------+---------------+------+
| 16 | 1 | {} |
+----------+---------------+------+
1 row in setINSERT INTO tokens () VALUES (); inserts a row using every default.
The DEFAULT keyword
Ask for the default explicitly in INSERT and UPDATE:
query.sql
SQL
UPDATE members SET plan = 'pro' WHERE email = 'asha@example.com';
UPDATE members SET plan = DEFAULT WHERE email = 'asha@example.com';
SELECT email, plan FROM members WHERE email = 'asha@example.com';
Output
OUTPUT
+------------------+------+
| email | plan |
+------------------+------+
| asha@example.com | free |
+------------------+------+
1 row in setNOT NULL without a default
A NOT NULL column with no default must be given a value on insert. In strict mode, leaving it out is an error. In a non-strict mode, MySQL silently inserts 0 or '', which hides bugs (SQL Mode).
Adding NOT NULL to an existing column
query.sql
SQL
CREATE TABLE contacts (id INT PRIMARY KEY, email VARCHAR(50) NULL);
INSERT INTO contacts VALUES (1, 'a@example.com'), (2, NULL);
ALTER TABLE contacts MODIFY email VARCHAR(50) NOT NULL;
Output
OUTPUT
ERROR 1138 (22004): Invalid use of NULL valueThe table has a NULL, so the change fails. Fix the data first (update the NULLs to a real value, or delete the rows), and then add the constraint:
query.sql
SQL
UPDATE contacts SET email = 'unknown@example.com' WHERE email IS NULL;
ALTER TABLE contacts MODIFY email VARCHAR(50) NOT NULL;
SHOW COLUMNS FROM contacts;
Output
OUTPUT
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| email | varchar(50) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in setThe empty string is not NULL
NOT NULL accepts an empty string: '' is a value. If “empty” is not valid, add a CHECK:
query.sql
SQL
CREATE TABLE usernames (name VARCHAR(20) NOT NULL, CONSTRAINT chk_name_not_blank CHECK (CHAR_LENGTH(TRIM(name)) > 0));
INSERT INTO usernames VALUES ('asha');
INSERT INTO usernames VALUES (' ');
Output
OUTPUT
ERROR 3819 (HY000): Check constraint 'chk_name_not_blank' is violated.Defaults and application code
- Do not duplicate defaults in the application and the database with different values.
- A default of
0or''for “unknown” is often wrong. UseNULLif unknown is a state. - For timestamps, use
DEFAULT CURRENT_TIMESTAMPinstead of letting the application send its own clock, so that all rows use the database’s time.
lightbulb
The three-line rule
Required →
NOT NULL. Sensible starting value → DEFAULT. Genuinely unknown → NULL.