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

Concept Flow - NOT NULL constraint behavior

Start Insert/Update

↓

Check Column Value

↓

Is Value NULL?

No→Allow Operation

Yes↓

Reject Operation with Error

↓

End

When inserting or updating, the database checks if a NOT NULL column has a NULL value. If yes, it rejects the operation with an error; otherwise, it allows it.

Execution Sample

SQL

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

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

This code tries to insert a row with a NULL value in the 'name' column, which has a NOT NULL constraint.

Execution Table

Step	Action	Column Checked	Value	Result	Notes
1	Start INSERT	id	1	OK	Value is not NULL, passes NOT NULL check
2	Check 'name' column	name	NULL	Error	NULL value violates NOT NULL constraint
3	Reject INSERT	-	-	Failed	Insert operation aborted due to NOT NULL violation

💡 Insert fails because 'name' column value is NULL but has NOT NULL constraint

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
id	undefined	1	1	1
name	undefined	NULL	NULL	NULL
Insert Operation Status	pending	pending	error	failed

Key Moments - 2 Insights

Why does the insert fail even though only one column has NULL?

The NOT NULL constraint applies to each column individually. In the execution_table row 2, the 'name' column is checked and found NULL, which violates its NOT NULL constraint, causing the entire insert to fail.

Can other columns without NOT NULL constraints accept NULL values?

Yes. Only columns defined with NOT NULL reject NULL values. Columns without this constraint can accept NULL. This is shown in the execution_table where 'id' with NOT NULL passes with value 1, but 'name' fails with NULL.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result when checking the 'id' column?

A Error due to NULL value

B OK, value is not NULL

C Skipped check

D Warning but allowed

💡 Hint

Refer to execution_table row 1 under 'Result' column

At which step does the insert operation get rejected?

A Step 1

B Step 2

C Step 3

D After all steps

💡 Hint

Look at execution_table row 3 under 'Result' and 'Notes'

If the 'name' column did not have NOT NULL constraint, what would happen at step 2?

A Insert would succeed

B Insert would still fail

C Error would change to warning

D Database would ignore the column

💡 Hint

Consider the role of NOT NULL constraint in execution_table row 2

Concept Snapshot

NOT NULL constraint ensures a column cannot have NULL values.
When inserting or updating, if a NOT NULL column receives NULL, the operation fails.
Each NOT NULL column is checked individually.
Columns without NOT NULL allow NULL values.
Violations cause immediate rejection with an error.

Full Transcript

The NOT NULL constraint in SQL prevents columns from having NULL values. When you insert or update data, the database checks each NOT NULL column to ensure the value is not NULL. If it finds a NULL value in such a column, it rejects the operation with an error. For example, inserting a row with NULL in a NOT NULL column causes the insert to fail. Columns without NOT NULL constraints can accept NULL values without errors. This behavior helps keep important data always present in the database.