🚫 SQL `NOT NULL` Constraint – Ensure Data Completeness in Your Tables

The NOT NULL constraint in SQL is used to enforce mandatory values in a column. It prevents NULL entries and ensures every row contains valid data.

This is one of the most commonly used data integrity constraints in SQL and is essential for building robust and reliable databases.

📘 What is the SQL `NOT NULL` Constraint?

The NOT NULL constraint ensures that a column cannot have a NULL value. When applied, it requires a value to be provided for that column in every row inserted or updated.

✅ NULL means “unknown” or “missing value” — so NOT NULL means “value must be provided.”

🧾 SQL `NOT NULL` Syntax

1. During Table Creation:

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(100)
);

In this example:

Both first_name and last_name must have a value.
email can be NULL (optional).

2. Adding `NOT NULL` Later with `ALTER TABLE`:

ALTER TABLE employees
MODIFY email VARCHAR(100) NOT NULL;

⚠️ You can only apply NOT NULL to a column if no existing rows have NULL in that column.

✅ Example: Insert Values with NOT NULL Constraint

INSERT INTO employees (employee_id, first_name, last_name)
VALUES (1, 'John', 'Doe'); -- ✅ Successful

INSERT INTO employees (employee_id, first_name)
VALUES (2, 'Jane'); -- ❌ Error: last_name is required

The second query fails because last_name is defined as NOT NULL.

💡 Benefits of Using `NOT NULL`

Ensures data completeness
Helps maintain data quality and reliability
Supports application logic that depends on required fields
Prevents accidental data loss or incomplete entries

🧠 Best Practices for `NOT NULL`

Tip	Reason
Apply to columns that must always have a value	Prevents future data gaps
Avoid on optional fields	Give flexibility where needed
Combine with `DEFAULT` values if applicable	Ensure field always has a meaningful value
Don’t apply to `auto-increment` ID fields manually	They’re already handled by the database engine

⚠️ Common Pitfalls

Forgetting to provide a value for a NOT NULL column → Insert/Update failure
Altering a column with NULLs to NOT NULL → You’ll get an error
Assuming NOT NULL means “not empty string” → ❌ No! Empty string is still a value

-- NULL vs Empty String
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (3, '', 'Smith'); -- ✅ Valid ('' is not NULL)

📝 Summary

NOT NULL ensures that a column must contain a value
It helps enforce mandatory fields in your data model
Can be used during table creation or added later with ALTER TABLE
Provides cleaner, more reliable datasets for querying and analysis

NOT NULL Constraint

🚫 SQL NOT NULL Constraint – Ensure Data Completeness in Your Tables

📘 What is the SQL NOT NULL Constraint?

🧾 SQL NOT NULL Syntax