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.

Practice

1.

What does the NOT NULL constraint do in a database table?

easy

A. It automatically generates unique values for a column.

B. It allows a column to have duplicate values.

C. It ensures a column cannot have empty or missing values.

D. It deletes rows with null values.

Solution

1. Step 1: Understand the purpose of NOT NULL

   The NOT NULL constraint prevents a column from having NULL (empty) values, ensuring data is always present.

2. Step 2: Compare options with the definition

   Only It ensures a column cannot have empty or missing values. correctly describes this behavior; others describe different constraints or actions.

3. Final Answer:

   It ensures a column cannot have empty or missing values. -> Option C

4. Quick Check:

   NOT NULL means no empty values allowed [OK]

Hint: NOT NULL means no empty values allowed in a column [OK]

Common Mistakes:

• Confusing NOT NULL with UNIQUE constraint
• Thinking NOT NULL deletes rows
• Assuming NOT NULL auto-generates values

2.

Which of the following is the correct syntax to add a NOT NULL constraint to a column named email when creating a table?

CREATE TABLE users (
  id INT PRIMARY KEY,
  email ???
);

easy

A. NULL VARCHAR(255)

B. NOT NULL VARCHAR(255)

C. VARCHAR(255) NULL

D. VARCHAR(255) NOT NULL

Solution

1. Step 1: Recall correct order of column definition

   The data type comes first, then constraints like NOT NULL.

2. Step 2: Check each option's order

   VARCHAR(255) NOT NULL correctly places VARCHAR(255) before NOT NULL; others have wrong order or use NULL instead.

3. Final Answer:

   VARCHAR(255) NOT NULL -> Option D

4. Quick Check:

   Data type first, then NOT NULL [OK]

Hint: Data type comes before NOT NULL in column definition [OK]

Common Mistakes:

• Writing NOT NULL before data type
• Using NULL instead of NOT NULL
• Omitting data type

3.

Given the table employees with columns id INT PRIMARY KEY and name VARCHAR(100) NOT NULL, what happens when you run this query?

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

medium

A. The query fails with an error due to NOT NULL constraint.

B. The name column defaults to an empty string.

C. The row is inserted with a NULL name.

D. The row is inserted but name is ignored.

Solution

1. Step 1: Understand NOT NULL behavior on insert

   NOT NULL means the column cannot accept NULL values during insert or update.

2. Step 2: Analyze the insert statement

   The query tries to insert NULL into the name column, violating the NOT NULL constraint, causing an error.

3. Final Answer:

   The query fails with an error due to NOT NULL constraint. -> Option A

4. Quick Check:

   NOT NULL rejects NULL inserts [OK]

Hint: Inserting NULL into NOT NULL column causes error [OK]

Common Mistakes:

• Assuming NULL is converted to empty string
• Thinking the row inserts ignoring NULL
• Confusing NOT NULL with default values

4.

Consider this table creation statement:

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(50) NOT NULL,
  price DECIMAL(5,2)
);

Which of the following statements will cause an error?

medium

A. INSERT INTO products (product_id, product_name, price) VALUES (1, 'Pen', 1.50);

B. INSERT INTO products (product_id, price) VALUES (2, 2.00);

C. INSERT INTO products (product_id, product_name) VALUES (3, 'Notebook');

D. INSERT INTO products (product_id, product_name, price) VALUES (4, 'Eraser', NULL);

Solution

1. Step 1: Identify columns with NOT NULL constraint

   Only product_name has NOT NULL, so it must have a value on insert.

2. Step 2: Check each insert statement

   INSERT INTO products (product_id, price) VALUES (2, 2.00); omits product_name, so it tries to insert NULL, causing an error. Others provide product_name.

3. Final Answer:

   INSERT missing NOT NULL column product_name causes error. -> Option B

4. Quick Check:

   NOT NULL columns must be included in insert [OK]

Hint: Always provide values for NOT NULL columns on insert [OK]

Common Mistakes:

• Ignoring NOT NULL columns in insert
• Assuming NULL allowed if column omitted
• Confusing NULL and empty string

5.

You have a table orders with columns order_id INT PRIMARY KEY, customer_id INT NOT NULL, and order_date DATE NOT NULL. You want to add a new column status that must always have a value and defaults to 'pending'. Which is the correct way to add this column?

hard

A. ALTER TABLE orders ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'pending';

B. ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending';

C. ALTER TABLE orders ADD COLUMN status VARCHAR(20) NOT NULL;

D. ALTER TABLE orders ADD COLUMN status VARCHAR(20);

Solution

1. Step 1: Understand NOT NULL with default value

   Adding a NOT NULL column requires a default value to avoid errors on existing rows.

2. Step 2: Analyze each ALTER statement

   ALTER TABLE orders ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'pending'; adds status with NOT NULL and default 'pending', so existing rows get this value. Others miss NOT NULL or default.

3. Final Answer:

   ALTER with NOT NULL and DEFAULT 'pending' is correct. -> Option A

4. Quick Check:

   NOT NULL columns need default when added to existing table [OK]

Hint: Add NOT NULL column with DEFAULT to avoid errors [OK]

Common Mistakes:

• Adding NOT NULL column without default
• Assuming default alone enforces NOT NULL
• Omitting NOT NULL when required