🚫 SQL NOT NULL – Enforce Required Fields in Your Database

🧲 Introduction – What is SQL NOT NULL?

The NOT NULL constraint in SQL is used to prevent a column from storing NULL values. It enforces data integrity by ensuring that a value must be provided when a record is inserted or updated.

🎯 In this guide, you’ll learn:

• How to apply the NOT NULL constraint
• Default behavior and interactions with other constraints
• When and why to use NOT NULL in database design

---

✅ 1. Basic NOT NULL Usage

CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(100) NOT NULL,
  created_at DATE NOT NULL
);

✅ Ensures that email and created_at must have values when a new user is added.

---

🔄 2. Adding NOT NULL to Existing Column

ALTER TABLE customers
MODIFY phone VARCHAR(15) NOT NULL;  -- MySQL

-- PostgreSQL
ALTER TABLE customers
ALTER COLUMN phone SET NOT NULL;

✅ Converts a nullable column to NOT NULL (make sure existing rows comply).

---

🧱 3. NOT NULL with DEFAULT

CREATE TABLE products (
  id INT PRIMARY KEY,
  stock INT NOT NULL DEFAULT 0
);

✅ Ensures all new rows have a valid stock value (even if not provided explicitly).

---

⚠️ 4. Behavior Notes

• NULL means “unknown” or “missing”, not zero or empty string
• NOT NULL is checked on INSERT and UPDATE
• Violating it results in an error:

-- Error:
INSERT INTO users (id) VALUES (1);  -- Missing required NOT NULL column

---

📘 Best Practices

✅ Recommended	❌ Avoid This
Use NOT NULL on all required fields	Allowing NULLs in critical identifiers
Combine with DEFAULT when needed	Assuming NOT NULL fills values automatically
Add only if you’re sure the column is mandatory	Over-constraining optional data

---

📌 Summary – Recap & Next Steps

The NOT NULL constraint is one of the simplest yet most important tools for ensuring data integrity in SQL databases. It ensures that required fields always receive a value.

🔍 Key Takeaways:

• Prevents missing data in required columns
• Works on INSERT and UPDATE
• Often paired with DEFAULT, PRIMARY KEY, and CHECK

⚙️ Real-World Relevance:
Used in production tables, validation logic, audit systems, and backend validation enforcement.

➡️ Next: Learn UNIQUE, CHECK, DEFAULT, or explore composite constraint design.

---

❓ FAQ – SQL NOT NULL

❓ What does NOT NULL mean in SQL?

✅ It means the column must contain a value—no NULL allowed.

❓ Can I add NOT NULL to an existing column?

✅ Yes, but only if all current rows have a value.

❓ Can NOT NULL be combined with DEFAULT?

✅ Absolutely. It’s common to enforce a default value if no value is given.

❓ What happens if I violate NOT NULL?

❌ An error is thrown. The row won’t be inserted or updated.

---

Share Now :