SQL NOT NULL Constraint
By default any column may hold NULL. A NOT NULL constraint says “this column must always have a value”. It is the simplest and most useful constraint: names, dates and amounts that are required by the business should never be missing, and the database can guarantee that.
- Declare required columns with
NOT NULL - Recognise NOT NULL errors on INSERT and UPDATE
- Add NOT NULL to an existing column safely
- Combine NOT NULL with DEFAULT
Define NOT NULL columns
CREATE TABLE patients ( patient_id INT PRIMARY KEY, full_name VARCHAR(60) NOT NULL, phone VARCHAR(15), admitted DATE NOT NULL);DESCRIBE patients;| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| patient_id | int | NO | PRI | NULL | |
| full_name | varchar(60) | NO | NULL | ||
| phone | varchar(15) | YES | NULL | ||
| admitted | date | NO | NULL |
full_nameandadmittedareNOT NULL; theNullcolumn ofDESCRIBEshowsNO.phoneis optional (YES).- A primary key is automatically
NOT NULL.
Inserting without a required value
Leaving a NOT NULL column out:
CREATE TABLE patients (patient_id INT PRIMARY KEY, full_name VARCHAR(60) NOT NULL, phone VARCHAR(15), admitted DATE NOT NULL);INSERT INTO patients (patient_id, full_name) VALUES (1, 'Ravi Shah');ERROR 1364 (HY000): Field 'admitted' doesn't have a default value
Giving NULL explicitly:
CREATE TABLE patients (patient_id INT PRIMARY KEY, full_name VARCHAR(60) NOT NULL, phone VARCHAR(15), admitted DATE NOT NULL);INSERT INTO patients VALUES (1, NULL, '98450 11111', '2026-01-10');ERROR 1048 (23000): Column 'full_name' cannot be null
- Error 1364: the column was not given and has no default.
- Error 1048: an explicit NULL was given for a NOT NULL column.
- An
UPDATE ... SET full_name = NULLfails with error 1048 in the same way.
An empty string '' is not NULL, so NOT NULL does not stop it. Add a CHECK (full_name <> '') if empty text must be rejected too.
NOT NULL with DEFAULT
Combine them when a value is required but has an obvious standard:
CREATE TABLE tickets ( ticket_id INT PRIMARY KEY, status VARCHAR(10) NOT NULL DEFAULT 'open');INSERT INTO tickets (ticket_id) VALUES (1);SELECT * FROM tickets;| ticket_id | status |
|---|---|
| 1 | open |
Add NOT NULL to an existing column
MODIFY redefines the column. It fails if existing rows contain NULL:
ALTER TABLE customers MODIFY email VARCHAR(60) NOT NULL;ERROR 1138 (22004): Invalid use of NULL value
Fix the data first, then add the constraint:
UPDATE customers SET email = 'unknown@example.com' WHERE email IS NULL;ALTER TABLE customers MODIFY email VARCHAR(60) NOT NULL;SELECT customer_id, email FROM customers WHERE customer_id = 4;| customer_id | |
|---|---|
| 4 | unknown@example.com |
- Customer 4 has no email; the
UPDATEfills in a placeholder. - Now no row is NULL, so
MODIFY ... NOT NULLsucceeds. MODIFYmust repeat the full column type (VARCHAR(60)), or the type would change too.
Remove the constraint again by modifying the column without NOT NULL:
ALTER TABLE customers MODIFY email VARCHAR(60) NULL;Quick quiz
1. Does NOT NULL reject an empty string ”?
Use a CHECK constraint to reject empty text.
2. Why does ALTER TABLE ... MODIFY email ... NOT NULL fail?
Existing data must satisfy the new constraint.
NOT NULL FAQ
What does NOT NULL mean in SQL?
The column must always contain a value; inserts or updates that would leave it NULL are rejected.
How do I add a NOT NULL constraint to an existing column in MySQL?
Update existing NULLs to a value, then run ALTER TABLE t MODIFY col TYPE NOT NULL; repeating the column’s full type.
Is a primary key NOT NULL automatically?
Yes. Every primary key column is implicitly NOT NULL.