NOT NULL constraint in SQL - Step-by-Step Execution

Concept Flow - NOT NULL constraint

Define Table with NOT NULL

↓

Insert Row

↓

Check Column Value

↓

Error: Reject

When inserting data, the database checks if columns with NOT NULL have values. If a NULL is found, insertion is rejected.

Execution Sample

SQL

CREATE TABLE users (
  id INT NOT NULL,
  name VARCHAR(50) NOT NULL
);

INSERT INTO users (id, name) VALUES (1, 'Alice');
INSERT INTO users (id, name) VALUES (2, NULL);

Creates a table with NOT NULL columns and tries to insert one valid row and one invalid row with NULL.

Execution Table

Step	Action	Column Checked	Value	Result	Notes
1	Create table	id, name	N/A	Success	Table created with NOT NULL constraints
2	Insert row	id	1	Valid	Value is NOT NULL
2	Insert row	name	'Alice'	Valid	Value is NOT NULL
2	Insert row	All columns	Valid	Row inserted	Row with id=1 and name='Alice' added
3	Insert row	id	2	Valid	Value is NOT NULL
3	Insert row	name	NULL	Invalid	NOT NULL constraint violated
3	Insert row	All columns	Invalid	Error	Insertion rejected due to NULL in NOT NULL column

💡 Insertion stops at step 3 because 'name' column value is NULL which violates NOT NULL constraint

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
users table rows	empty	[{id:1, name:'Alice'}]	[{id:1, name:'Alice'}]	[{id:1, name:'Alice'}]

Key Moments - 2 Insights

Why does the second insert fail even though the 'id' is valid?

Because the 'name' column has a NULL value which violates the NOT NULL constraint as shown in execution_table step 3.

Can a NOT NULL column accept an empty string or zero?

Yes, NOT NULL means the value cannot be NULL, but empty strings or zero are valid values as long as they are not NULL.

Visual Quiz - 3 Questions

Look at the execution_table at step 2, what is the value of 'name' column?

A 'Alice'

B NULL

C Empty string

D 0

💡 Hint

Check execution_table row with Step 2 and Column Checked 'name'

At which step does the NOT NULL constraint cause an error?

A Step 1

B Step 2

C Step 3

D No error occurs

💡 Hint

Look at execution_table rows where Result is 'Invalid' or 'Error'

If the second insert had name='Bob' instead of NULL, what would happen?

A Insertion would fail due to NOT NULL

B Insertion would succeed

C Table would be dropped

D Only 'id' would be inserted

💡 Hint

Refer to execution_table step 2 where both columns have valid NOT NULL values

Concept Snapshot

NOT NULL constraint ensures a column cannot have NULL values.
When inserting or updating, NULL in NOT NULL columns causes error.
Valid values include empty strings or zero, but not NULL.
Use NOT NULL to enforce required data in columns.

Full Transcript

The NOT NULL constraint in SQL prevents columns from having NULL values. When a table is created with NOT NULL columns, any attempt to insert or update a row with NULL in those columns will be rejected by the database. For example, if a users table has 'id' and 'name' columns both NOT NULL, inserting a row with a NULL name will cause an error and the row will not be added. However, values like empty strings or zero are allowed as they are not NULL. This constraint helps ensure important data is always present in the database.