NOT NULL Constraint

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

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

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

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

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

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