The constraints overview just listed NOT NULL as the simplest of the six, a column that can never store NULL. It's also the only one of the six with no table-level form, it only ever attaches to exactly one column, so there's nothing composite about it to cover here beyond the core idea.
Learning Objectives
- Declare a NOT NULL column, and explain exactly what it rejects.
- Distinguish NOT NULL from DEFAULT, two rules that are easy to confuse but solve different problems.
- Add NOT NULL to an already-existing column across every database in this course.
Declaring NOT NULL
Every INSERT into departments must supply a real value for name, or the statement fails outright. Leave name out, or explicitly pass NULL for it, and the database rejects the row before it's ever stored. employees.first_name and last_name are NOT NULL for the same reason, an employee record with no name isn't a usable record.
CREATE TABLE departments ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL );
NOT NULL vs DEFAULT: Different Problems
| NOT NULL | DEFAULT | |
|---|---|---|
| What it does | Rejects the INSERT if no value is given | Supplies a value automatically if none is given |
| Can they combine? | Yes, a column is often both | Yes, same answer |
| department_id example | Not applied, a new hire may not have a department assigned yet | Not applied here either, there's no sensible default department |
| hire_date example | Could apply, every employee needs one | Often paired with DEFAULT CURRENT_DATE, covered in Module 9 |
NOT NULL alone just rejects a missing value, it forces the caller to supply one. DEFAULT alone fills in a value automatically but still allows the column itself to be NULL afterward unless NOT NULL is also present. A column marked NOT NULL DEFAULT 0 can never end up NULL, every INSERT either supplies a value or silently gets 0.
Adding NOT NULL to an Existing Column
| Database | Make an Existing Column NOT NULL |
|---|---|
| MySQL / MariaDB | ALTER TABLE employees MODIFY COLUMN last_name VARCHAR(50) NOT NULL; |
| PostgreSQL | ALTER TABLE employees ALTER COLUMN last_name SET NOT NULL; |
| SQL Server | ALTER TABLE employees ALTER COLUMN last_name VARCHAR(50) NOT NULL; |
| Oracle | ALTER TABLE employees MODIFY last_name NOT NULL; |
| SQLite | Not supported directly, recreate the table with the constraint instead |
This fails immediately if the column already has even one NULL value in it, the constraint can't be satisfied by existing data, so the ALTER TABLE statement itself is rejected. Clean up any existing NULLs first, typically with an UPDATE, before adding the constraint.
Try It Yourself
Write a statement that makes projects.name NOT NULL on an existing table, on PostgreSQL.
ALTER TABLE projects ALTER COLUMN name SET NOT NULL;
Frequently Asked Questions
Does NOT NULL accept an empty string or 0 as a valid value?
Yes, an empty string '' and the number 0 are both real, non-NULL values, NOT NULL only rejects the actual absence of a value. A column could be NOT NULL and still contain '' for every row, that's a data-quality concern, not something NOT NULL alone prevents.
Can a PRIMARY KEY column be anything other than NOT NULL?
No, PRIMARY KEY always implies NOT NULL automatically, declaring PRIMARY KEY on a column makes it NOT NULL whether or not the keyword is written explicitly.
Common Beginner Mistakes
Adding NOT NULL to a column that already has NULL rows, without fixing them first
The ALTER TABLE statement fails immediately, the existing NULL values already violate the constraint being added. Run an UPDATE to replace the NULLs with real values (or a sensible default) before adding NOT NULL.
Assuming NOT NULL prevents duplicate or invalid-looking values
It only checks for the presence of a value, not its content or uniqueness. A NOT NULL column can still hold duplicate values, or technically-valid-but-wrong ones, CHECK and UNIQUE exist for those, covered later in this module.
Best Practices
- Mark a column NOT NULL whenever the business logic genuinely requires a value, not as a default habit on every column.
- Clean up existing NULL values with an UPDATE before adding NOT NULL to a populated table.
- Pair NOT NULL with DEFAULT when a sensible fallback value exists, so existing rows get a real value automatically instead of blocking the migration.
Interview Questions
What's the difference between a column being NOT NULL and a column having a DEFAULT value?
NOT NULL rejects an INSERT that doesn't supply a value for that column. DEFAULT supplies a value automatically when none is given, it doesn't reject anything on its own. A column can have either, both, or neither, salary DECIMAL(10,2) NOT NULL DEFAULT 0 uses both together: every row gets a real number, and an INSERT that omits salary silently gets 0 instead of failing.
Why would ALTER TABLE employees MODIFY COLUMN middle_name VARCHAR(50) NOT NULL fail on MySQL, even with correct syntax?
If any existing row already has NULL in middle_name, the constraint can't be satisfied retroactively, the statement is rejected. The fix: UPDATE employees SET middle_name = '' WHERE middle_name IS NULL; (or a more meaningful placeholder) before running the ALTER TABLE.
Summary
NOT NULL requires a column to always hold a real value, rejecting any INSERT or UPDATE that leaves it empty. It's distinct from DEFAULT, which fills in a value rather than rejecting the absence of one, though the two are often combined. Adding it to an already-populated table requires every existing row to already satisfy it.
What's Next?
UNIQUE is next, a different kind of restriction: not whether a value exists, but whether it's allowed to repeat.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.