MySQL NOT NULL Constraint

🚫 MySQL NOT NULL Constraint

The NOT NULL constraint in MySQL ensures that a column cannot contain NULL values.

👉 It forces users to always provide a value for the column.

1️⃣ What is NOT NULL Constraint?

  • Prevents NULL (empty) values

  • Column must always have some data

  • Applied at column level

📌 NULL means no value, not zero or empty string.

2️⃣ Why Use NOT NULL?

✔ Ensures mandatory data
✔ Improves data quality
✔ Prevents incomplete records
✔ Essential for critical columns (name, email, ID)

3️⃣ Basic Syntax


4️⃣ Simple Example


Valid Insert ✅


Invalid Insert ❌


 

❌ Error: Column ‘name’ cannot be null

5️⃣ NOT NULL with DEFAULT ⭐

Very common combination.


Insert Without Status


✔ status becomes Active

6️⃣ Adding NOT NULL Using ALTER TABLE


📌 Existing rows must not contain NULL, otherwise this will fail.

7️⃣ Removing NOT NULL Constraint


8️⃣ NOT NULL vs PRIMARY KEY ⭐ (Interview)

Feature NOT NULL PRIMARY KEY
Allows NULL ❌ No ❌ No
Ensures uniqueness ❌ No ✔ Yes
Keys per table Many One
Identity ❌ No ✔ Yes

📌 PRIMARY KEY = NOT NULL + UNIQUE

9️⃣ NOT NULL vs CHECK ❓

Feature NOT NULL CHECK
Prevents NULL ✔ Yes ❌ No
Value condition ❌ No ✔ Yes
Complexity Low High

🔟 Common Mistakes ❌

❌ Confusing NULL with empty string ''
❌ Adding NOT NULL when NULL values already exist
❌ Forgetting DEFAULT for mandatory fields

📌 Interview Questions & MCQs (Very Important)

Q1. What does NOT NULL constraint do?

A) Prevent duplicates
B) Prevent NULL values
C) Speed queries
D) Auto increment

✅ Answer: B

Q2. Can a table have multiple NOT NULL columns?

A) Yes
B) No

✅ Answer: A

Q3. Does NOT NULL allow empty string?

A) Yes
B) No

✅ Answer: A
(Empty string ≠ NULL)

Q4. Which constraint ensures both uniqueness and NOT NULL?

A) UNIQUE
B) CHECK
C) PRIMARY KEY
D) DEFAULT

✅ Answer: C

Q5. How to add NOT NULL to an existing column?

ALTER TABLE table_name MODIFY column datatype NOT NULL;

A) Correct
B) Incorrect

✅ Answer: A

Q6. Which is mandatory for PRIMARY KEY?

A) NULL
B) NOT NULL
C) DEFAULT
D) CHECK

✅ Answer: B

🔥 Real-Life Use Cases

✔ User name
✔ Email address
✔ Phone number
✔ Order date
✔ Status fields

✅ Summary

  • NOT NULL prevents NULL values

  • Ensures mandatory data

  • Can be used on many columns

  • Often combined with DEFAULT

  • PRIMARY KEY includes NOT NULL automatically

  • Very important for SQL exams & interviews

You may also like...