NOT NULL Constraint

In this page:

Understanding NOT NULL
Adding NOT NULL to Existing Tables
Handling Error Scenarios
Removing NOT NULL
Best Practices

Understanding NOT NULL

By default, any column can hold the special NULL value meaning 'no data at all' — adding the NOT NULL constraint forbids that, forcing every row to supply a real value for that column.

Example: Understanding NOT NULL

sql

CREATE TABLE users (username VARCHAR(50) NOT NULL);

Adding NOT NULL to Existing Tables

You can retrofit NOT NULL onto an already-existing column with ALTER TABLE ... MODIFY, though the operation will fail if any existing rows currently have NULL in that column. This makes retrofitting a constraint a two-step process in practice: first clean up any existing NULLs, then apply the MODIFY statement.

Example: Adding NOT NULL to Existing Tables

sql

ALTER TABLE users MODIFY username VARCHAR(50) NOT NULL;

Handling Error Scenarios

Attempting to INSERT a row without a value for a NOT NULL column (and with no DEFAULT defined) raises an immediate error, stopping bad data before it ever reaches storage. This fail-fast behavior is exactly the point of the constraint, catching a data integrity problem at insert time instead of much later.

Example: Handling Error Scenarios

sql

CREATE TABLE users (username VARCHAR(50) NOT NULL);
INSERT INTO users (username) VALUES (NULL); -- raises an error

Removing NOT NULL

Removing a NOT NULL constraint is a matter of modifying the column definition again without the keyword — after that, future inserts are free to leave the column empty. This is a reversible change, so if a business requirement changes, the column can always go back to requiring a value again.

Example: Removing NOT NULL

sql

ALTER TABLE users MODIFY username VARCHAR(50);

Best Practices

NOT NULL is considered best practice on any column where a missing value would be meaningless or dangerous — usernames, passwords, and primary keys are the classic examples. Applying NOT NULL too liberally, on truly optional fields, just forces awkward placeholder values instead of a clean, honest empty value.

Example: Best Practices

sql

CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  password VARCHAR(255) NOT NULL
);

🔒

Chapter Quiz — Complete all 8 topics to unlock

0/8 topics done

Complete these topics first:

← Previous Lesson MySQL Constraints Overview Next Lesson → UNIQUE Constraint