00:00

Help Us Improve

We're sorry this tutorial wasn't helpful. Please tell us what we can do better.

MySQL NOT NULL

Last updated: 5 min read

In SQL, NULL means "no value" or "unknown". Some columns, such as a person's name or an order's date, must always have a value. The NOT NULL constraint tells MySQL to reject any row that leaves such a column empty.

1. What NULL Means

NULL, empty string and zero are differentThree boxes: NULL means no value or unknown; an empty string is a known value that has no characters; zero is a known number. NOT NULL forbids only NULL; empty strings and zero are still allowed.NULLunknown / missingblocked by NOT NULL''empty string: known, no charactersallowed0zero: a known numberallowed
Figure 1: NOT NULL rejects only NULL. An empty string or 0 is still a value.
  • NULL is not equal to anything, not even another NULL: NULL = NULL is NULL, not true.
  • Test for it with IS NULL and IS NOT NULL.
  • Every column allows NULL unless you say NOT NULL (primary key columns are NOT NULL automatically).
query.sql 
SELECT NULL = NULL AS equals, NULL IS NULL AS is_null, '' IS NULL AS empty_is_null, 0 IS NULL AS zero_is_null;

Result: 1 row

equalsis_nullempty_is_nullzero_is_null
NULL100

2. Declaring NOT NULL Columns

declare.sql 
CREATE TABLE registrations (
  reg_id     INT AUTO_INCREMENT PRIMARY KEY,
  attendee   VARCHAR(50) NOT NULL,
  email      VARCHAR(80) NOT NULL,
  company    VARCHAR(50),               -- optional
  event_date DATE NOT NULL
);
DESCRIBE registrations;

Result: 5 rows

FieldTypeNullKey
reg_idintNOPRI
attendeevarchar(50)NO
emailvarchar(80)NO
companyvarchar(50)YES
event_datedateNO

3. What Happens When a Value Is Missing

Explicit NULL:

query.sql 
INSERT INTO registrations (attendee, email, event_date) VALUES (NULL, 'x@mail.example', '2026-04-10');
Output 
ERROR 1048 (23000): Column 'attendee' cannot be null

Column left out with no default:

query.sql 
INSERT INTO registrations (attendee, event_date) VALUES ('Kriti', '2026-04-10');
Output 
ERROR 1364 (HY000): Field 'email' doesn't have a default value

Updating an existing value to NULL:

update-null.sql 
INSERT INTO registrations (attendee, email, event_date) VALUES ('Kriti', 'kriti@mail.example', '2026-04-10');
UPDATE registrations SET email = NULL WHERE attendee = 'Kriti';
Output 
Query OK, 1 row affected
ERROR 1048 (23000): Column 'email' cannot be null

The optional company column accepts NULL without complaint:

optional.sql 
INSERT INTO registrations (attendee, email, event_date) VALUES ('Lokesh', 'lokesh@mail.example', '2026-04-10');
SELECT attendee, company FROM registrations;

Result: 2 rows

attendeecompany
KritiNULL
LokeshNULL

4. NOT NULL Allows Empty Strings

An empty string is a value, so it passes NOT NULL:

empty.sql 
INSERT INTO registrations (attendee, email, event_date) VALUES ('', '', '2026-04-11');
SELECT reg_id, attendee = '' AS empty_name FROM registrations WHERE reg_id = 3;

Result: 1 row

reg_idempty_name
31

To also forbid blanks, add a CHECK constraint:

not-blank.sql 
DELETE FROM registrations WHERE attendee = '';
ALTER TABLE registrations ADD CONSTRAINT chk_attendee_not_blank CHECK (TRIM(attendee) <> '');
INSERT INTO registrations (attendee, email, event_date) VALUES ('   ', 'a@mail.example', '2026-04-11');
Output 
Query OK, 1 row affected
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0
ERROR 3819 (HY000): Check constraint 'chk_attendee_not_blank' is violated.

5. NOT NULL with DEFAULT

Pair NOT NULL with a DEFAULT so inserts can skip the column and still get a valid value:

with-default.sql 
ALTER TABLE registrations ADD COLUMN ticket_type VARCHAR(10) NOT NULL DEFAULT 'standard';
INSERT INTO registrations (attendee, email, event_date) VALUES ('Mansi', 'mansi@mail.example', '2026-04-10');
SELECT attendee, ticket_type FROM registrations;

Result: 3 rows

attendeeticket_type
Kritistandard
Lokeshstandard
Mansistandard
Explicitly inserting NULL into a NOT NULL column with a default is still an error; the default is used only when the column is left out (or given as DEFAULT).

6. Adding or Removing NOT NULL on an Existing Column

Use MODIFY with the full column definition. Existing NULLs must be fixed first:

query.sql 
ALTER TABLE registrations MODIFY company VARCHAR(50) NOT NULL;
Output 
ERROR 1138 (22004): Invalid use of NULL value
add-not-null.sql 
UPDATE registrations SET company = 'Independent' WHERE company IS NULL;
ALTER TABLE registrations MODIFY company VARCHAR(50) NOT NULL DEFAULT 'Independent';
SHOW COLUMNS FROM registrations LIKE 'company';

Result: 1 row

FieldTypeNullKeyDefaultExtra
companyvarchar(50)NOIndependent

To allow NULL again:

remove-not-null.sql 
ALTER TABLE registrations MODIFY company VARCHAR(50) NULL;
SHOW COLUMNS FROM registrations LIKE 'company';

Result: 1 row

FieldTypeNullKeyDefaultExtra
companyvarchar(50)YESNULL

7. Strict Mode and Multi-Row Inserts

MySQL 8+ runs in strict mode by default (STRICT_TRANS_TABLES), which is why missing values cause errors. In the old non-strict mode, MySQL would instead store the type's implicit default ('' or 0) with a warning, silently hiding bugs. Keep strict mode on.

query.sql 
SELECT @@sql_mode LIKE '%STRICT_TRANS_TABLES%' AS strict_mode_on;

Result: 1 row

strict_mode_on
1

8. Which Columns Should Be NOT NULL?

  • Anything required by the business: names, dates, amounts, status.
  • Foreign keys for mandatory relationships (an order must have a customer).
  • Flags and counters: use NOT NULL DEFAULT 0 / FALSE so you never deal with three-valued logic.
  • Leave NULL for genuinely optional or unknown data: middle name, cancellation date, last login.

9. Practice Exercises

Exercise 1: List every NOT NULL column in the registrations table.
solution.sql 
SELECT column_name FROM information_schema.columns
WHERE table_schema = 'events' AND table_name = 'registrations' AND is_nullable = 'NO'
ORDER BY ordinal_position;

Result: 5 rows

COLUMN_NAME
reg_id
attendee
email
event_date
ticket_type
Exercise 2: Add a required city column defaulting to 'Online'.
solution.sql 
ALTER TABLE registrations ADD COLUMN city VARCHAR(30) NOT NULL DEFAULT 'Online';
SELECT attendee, city FROM registrations;

Result: 3 rows

attendeecity
KritiOnline
LokeshOnline
MansiOnline

10. Summary

  • NOT NULL makes a column mandatory; NULL means missing/unknown.
  • Inserts or updates that would store NULL fail with error 1048 or 1364.
  • Empty strings and 0 still pass; add a CHECK to forbid blanks.
  • Combine NOT NULL with DEFAULT for easy inserts.
  • Change it later with ALTER TABLE … MODIFY, after fixing existing NULLs.