NOT NULL and DEFAULT Constraints0%

NOT NULL and DEFAULT Constraints

Beginner12 min read•Updated: 2026-09-12

NOT NULL and DEFAULT Constraints: Preventing Missing Data

In database design, missing or unrecorded information is represented by NULL. However, allowing NULL everywhere leads to complex, bug-prone queries where every filter must check IS NOT NULL. To build reliable databases, you must know when to strictly forbid missing data using NOT NULL and when to supply automatic fallbacks using DEFAULT.

1. The `NOT NULL` Constraint

The NOT NULL constraint enforces that a column must always be assigned a valid value during an INSERT or UPDATE statement. Attempting to insert a NULL value into a NOT NULL column triggers an error.

1CREATE TABLE users (
2    user_id INT PRIMARY KEY AUTO_INCREMENT,
3    username VARCHAR(50) NOT NULL, -- Mandatory field
4    email VARCHAR(100) NOT NULL,    -- Mandatory field
5    middle_name VARCHAR(50)         -- Optional field (Defaults to NULL allowed)
6);

What is `NULL` Exactly?

NULL is not equal to zero (0).
NULL is not equal to an empty string ("").
NULL represents unknown, unrecorded, or missing data.
In SQL three-valued logic, NULL = NULL evaluates to UNKNOWN, not TRUE!

2. The `DEFAULT` Constraint

The DEFAULT constraint supplies a predetermined fallback value if an INSERT statement does not explicitly provide a value for that column:

1CREATE TABLE product_inventory (
  product_id INT PRIMARY KEY AUTO_INCREMENT,
  product_name VARCHAR(150) NOT NULL,
  quantity_in_stock INT NOT NULL DEFAULT 0,
  is_discontinued BOOLEAN NOT NULL DEFAULT FALSE,
  minimum_order_quantity INT NOT NULL DEFAULT 1,
  reorder_level INT NOT NULL DEFAULT 10
8);

Testing Defaults During Insertion:

1-- Notice we only supply product_name:
2INSERT INTO product_inventory (product_name) 
3VALUES ('Wireless Mechanical Keyboard');
4 
5-- Check the resulting row:
6SELECT * FROM product_inventory;

Output:

Visual Architecture Blueprint

+------------+--------------------------------+-------------------+-----------------+------------------------+---------------+
| product_id | product_name                   | quantity_in_stock | is_discontinued | minimum_order_quantity | reorder_level |
+------------+--------------------------------+-------------------+-----------------+------------------------+---------------+
|          1 | Wireless Mechanical Keyboard   |                 0 |               0 |                      1 |            10 |
+------------+--------------------------------+-------------------+-----------------+------------------------+---------------+

3. Dynamic Default Expressions in MySQL 8.0+

Traditionally, DEFAULT only accepted literal constants (e.g., DEFAULT 0, DEFAULT 'Active'). In modern MySQL 8.0+, you can use dynamic expressions enclosed in parentheses!

1CREATE TABLE audit_records (
  record_id INT PRIMARY KEY AUTO_INCREMENT,
  user_action VARCHAR(100) NOT NULL,
  
  -- Dynamic Default: Current timestamp
  logged_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  
  -- Dynamic Default: Random UUID v4 generated on insert
  tracking_uuid VARCHAR(36) NOT NULL DEFAULT (UUID())
10);

4. Modifying Constraints on Existing Tables

1-- Add NOT NULL constraint to an existing column:
2ALTER TABLE users
3MODIFY COLUMN middle_name VARCHAR(50) NOT NULL;
4 
5-- Add a default value to an existing column:
6ALTER TABLE users
7ALTER COLUMN is_active SET DEFAULT TRUE;
8 
9-- Drop a default value:
10ALTER TABLE users
11ALTER COLUMN is_active DROP DEFAULT;

5. Best Practices & Common Pitfalls

Default to NOT NULL Everywhere Possible: Unless a column genuinely represents optional or unknown information (e.g., shipped_date for an order that hasn't shipped yet), mark columns as NOT NULL. This simplifies query logic and enables the optimizer to utilize indexes more efficiently.
Do Not Confuse Empty String with NULL: An empty string "" is a known string of zero length; NULL is an unknown value. Storing "" in a NOT NULL column succeeds, but may violate your business logic!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

In SQL three-valued logic, what is the result of the comparison expression NULL = NULL?

Question 2

What happens if an INSERT statement does not provide a value for a column configured with DEFAULT 'Active'?

Question 3

Which SQL statement alters an existing column named status to set its default value to 'Pending'?

Question 4

Why does marking columns as NOT NULL improve database performance?

Question 5

In MySQL 8.0+, how must dynamic function expressions (like UUID()) be written in a DEFAULT clause?

Next Lesson

UNIQUE Constraint & Candidate Keys

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Foreign Key & Referential Integrity	UNIQUE Constraint & Candidate Keys

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

UNIQUE Constraint & Candidate Keys

NOT NULL and DEFAULT Constraints

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

NOT NULL and DEFAULT Constraints: Preventing Missing Data

1. The `NOT NULL` Constraint

1CREATE TABLE users (
2    user_id INT PRIMARY KEY AUTO_INCREMENT,
3    username VARCHAR(50) NOT NULL, -- Mandatory field
4    email VARCHAR(100) NOT NULL,    -- Mandatory field
5    middle_name VARCHAR(50)         -- Optional field (Defaults to NULL allowed)
6);

What is `NULL` Exactly?

NULL is not equal to zero (0).
NULL is not equal to an empty string ("").
NULL represents unknown, unrecorded, or missing data.
In SQL three-valued logic, NULL = NULL evaluates to UNKNOWN, not TRUE!

2. The `DEFAULT` Constraint

The DEFAULT constraint supplies a predetermined fallback value if an INSERT statement does not explicitly provide a value for that column:

1CREATE TABLE product_inventory (
  product_id INT PRIMARY KEY AUTO_INCREMENT,
  product_name VARCHAR(150) NOT NULL,
  quantity_in_stock INT NOT NULL DEFAULT 0,
  is_discontinued BOOLEAN NOT NULL DEFAULT FALSE,
  minimum_order_quantity INT NOT NULL DEFAULT 1,
  reorder_level INT NOT NULL DEFAULT 10
8);

Testing Defaults During Insertion:

1-- Notice we only supply product_name:
2INSERT INTO product_inventory (product_name) 
3VALUES ('Wireless Mechanical Keyboard');
4 
5-- Check the resulting row:
6SELECT * FROM product_inventory;

Output:

Visual Architecture Blueprint

+------------+--------------------------------+-------------------+-----------------+------------------------+---------------+
| product_id | product_name                   | quantity_in_stock | is_discontinued | minimum_order_quantity | reorder_level |
+------------+--------------------------------+-------------------+-----------------+------------------------+---------------+
|          1 | Wireless Mechanical Keyboard   |                 0 |               0 |                      1 |            10 |
+------------+--------------------------------+-------------------+-----------------+------------------------+---------------+

3. Dynamic Default Expressions in MySQL 8.0+

Traditionally, DEFAULT only accepted literal constants (e.g., DEFAULT 0, DEFAULT 'Active'). In modern MySQL 8.0+, you can use dynamic expressions enclosed in parentheses!

1CREATE TABLE audit_records (
  record_id INT PRIMARY KEY AUTO_INCREMENT,
  user_action VARCHAR(100) NOT NULL,
  
  -- Dynamic Default: Current timestamp
  logged_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  
  -- Dynamic Default: Random UUID v4 generated on insert
  tracking_uuid VARCHAR(36) NOT NULL DEFAULT (UUID())
10);

4. Modifying Constraints on Existing Tables

1-- Add NOT NULL constraint to an existing column:
2ALTER TABLE users
3MODIFY COLUMN middle_name VARCHAR(50) NOT NULL;
4 
5-- Add a default value to an existing column:
6ALTER TABLE users
7ALTER COLUMN is_active SET DEFAULT TRUE;
8 
9-- Drop a default value:
10ALTER TABLE users
11ALTER COLUMN is_active DROP DEFAULT;

5. Best Practices & Common Pitfalls

Default to NOT NULL Everywhere Possible: Unless a column genuinely represents optional or unknown information (e.g., shipped_date for an order that hasn't shipped yet), mark columns as NOT NULL. This simplifies query logic and enables the optimizer to utilize indexes more efficiently.
Do Not Confuse Empty String with NULL: An empty string "" is a known string of zero length; NULL is an unknown value. Storing "" in a NOT NULL column succeeds, but may violate your business logic!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

In SQL three-valued logic, what is the result of the comparison expression NULL = NULL?

Question 2

What happens if an INSERT statement does not provide a value for a column configured with DEFAULT 'Active'?

Question 3

Which SQL statement alters an existing column named status to set its default value to 'Pending'?

Question 4

Why does marking columns as NOT NULL improve database performance?

Question 5

In MySQL 8.0+, how must dynamic function expressions (like UUID()) be written in a DEFAULT clause?

Next Lesson

UNIQUE Constraint & Candidate Keys

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Foreign Key & Referential Integrity	UNIQUE Constraint & Candidate Keys

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

NOT NULL and DEFAULT Constraints: Preventing Missing Data

1. The NOT NULL Constraint

What is NULL Exactly?

2. The DEFAULT Constraint

Testing Defaults During Insertion:

3. Dynamic Default Expressions in MySQL 8.0+

4. Modifying Constraints on Existing Tables

5. Best Practices & Common Pitfalls

Multiple Choice Questions

UNIQUE Constraint & Candidate Keys

Related Lessons

Practice Quiz

Loading Page Content...

NOT NULL and DEFAULT Constraints: Preventing Missing Data

1. The NOT NULL Constraint

What is NULL Exactly?

2. The DEFAULT Constraint

Testing Defaults During Insertion:

3. Dynamic Default Expressions in MySQL 8.0+

4. Modifying Constraints on Existing Tables

5. Best Practices & Common Pitfalls

Multiple Choice Questions

UNIQUE Constraint & Candidate Keys

Related Lessons

Practice Quiz

1. The `NOT NULL` Constraint

What is `NULL` Exactly?

2. The `DEFAULT` Constraint

1. The `NOT NULL` Constraint

What is `NULL` Exactly?

2. The `DEFAULT` Constraint