NOT NULL and DEFAULT constraints in MySQL - Step-by-Step Execution

Concept Flow - NOT NULL and DEFAULT constraints

Create Table

↓

Define Column

↓

Apply NOT NULL?

Yes→Reject NULL inserts

No↓

Apply DEFAULT?

Yes→Use default if no value given

No↓

Insert Row

↓

Check Constraints

↓

Accept or Reject Row

When creating a table, columns can be set NOT NULL to reject missing values, and DEFAULT to supply a value if none is given during insert.

Execution Sample

MySQL

CREATE TABLE users (
  id INT NOT NULL,
  name VARCHAR(20) NOT NULL DEFAULT 'guest'
);

INSERT INTO users (id) VALUES (1);

Creates a table with NOT NULL and DEFAULT constraints, then inserts a row with only id, name uses default.

Execution Table

Step	Action	Column	Value Provided	Constraint Check	Resulting Value	Row Inserted?
1	Start Insert	id	1	NOT NULL check passes	1	Pending
2	Start Insert	name	NULL (not provided)	NOT NULL check passes (value missing but DEFAULT exists)	Check DEFAULT	Pending
3	Apply DEFAULT	name	NULL	DEFAULT 'guest' applied	'guest'	Pending
4	Final Check	all columns	id=1, name='guest'	All constraints satisfied	id=1, name='guest'	Yes

💡 Insert succeeds because name uses DEFAULT 'guest' and id is provided and NOT NULL.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
id	NULL	1	1	1	1
name	NULL	NULL	NULL	'guest'	'guest'

Key Moments - 2 Insights

Why does the insert not fail when 'name' is not provided even though it is NOT NULL?

Because the DEFAULT constraint provides 'guest' as the value for 'name' when no value is given, as shown in execution_table row 3.

What happens if we try to insert NULL explicitly into a NOT NULL column without DEFAULT?

The insert fails immediately at the NOT NULL check, rejecting the row. This is implied by the constraint check in execution_table row 2.

Visual Quiz - 3 Questions

Look at the execution_table at step 3, what value is assigned to 'name'?

A Empty string

B NULL

C 'guest'

D No value assigned

💡 Hint

Check the 'Resulting Value' column in execution_table row 3.

At which step does the NOT NULL constraint get checked for 'name'?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the 'Constraint Check' column in execution_table row 2.

If the DEFAULT constraint was removed from 'name', what would happen when inserting without 'name'?

A Insert fails due to NOT NULL violation

B Insert succeeds with NULL in 'name'

C Insert succeeds with empty string in 'name'

D Insert succeeds with 'guest' anyway

💡 Hint

Refer to key_moments explanation about NOT NULL without DEFAULT.

Concept Snapshot

NOT NULL prevents missing values in a column.
DEFAULT sets a value if none is given during insert.
If NOT NULL and DEFAULT both exist, DEFAULT fills missing values.
Inserts fail if NOT NULL column has no value and no DEFAULT.
Use these constraints to ensure data integrity.

Full Transcript

This visual execution shows how MySQL handles NOT NULL and DEFAULT constraints during an insert. When inserting a row, each column is checked. If a NOT NULL column is missing a value, the database rejects the insert unless a DEFAULT value is defined. In the example, the 'name' column is NOT NULL with a DEFAULT of 'guest'. When inserting only 'id', the database applies the default 'guest' to 'name' and accepts the row. The execution table traces each step: checking provided values, applying constraints, and final acceptance. This helps beginners see how constraints protect data and how defaults help avoid insert errors.