MySQL NOT NULL

Ensuring required data in your database tables

🔒 What is NOT NULL?

The NOT NULL constraint ensures that a column cannot have NULL values. It forces users to provide data for that column, making it mandatory for every record. 


-- Creating a table with NOT NULL constraint
CREATE TABLE users (
    id INT NOT NULL,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

Output:

Table 'users' created successfully with NOT NULL constraints.

Key NOT NULL Concepts

✅

Required Fields

Makes columns mandatory 

name VARCHAR(50) NOT NULL
🚫

Prevents NULL

Blocks empty values 

age INT NOT NULL
🛡️

Data Integrity

Ensures complete records 

email VARCHAR(100) NOT NULL
⚠️

Error Prevention

Catches missing data early 

phone VARCHAR(15) NOT NULL

🔹 Creating Table with NOT NULL

When creating a table, specify NOT NULL for columns that must always have values. This prevents incomplete data from being inserted and maintains database quality by ensuring critical information is never missing. 

CREATE TABLE employees (
    employee_id INT NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10,2) NOT NULL
);

Output:

✓ Table created with NOT NULL constraints on employee_id, first_name, last_name, and salary.

🔹 Inserting Data with NOT NULL

When inserting data into a table with NOT NULL constraints, you must provide values for all required columns. If you try to insert NULL or omit a NOT NULL column, MySQL will reject the operation with an error. 

-- Valid insert (all NOT NULL columns have values)
INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (1, 'John', 'Doe', 50000.00);

-- Invalid insert (missing NOT NULL column)
INSERT INTO employees (employee_id, first_name)
VALUES (2, 'Jane');
-- Error: Column 'last_name' cannot be null

Output:

✓ First insert successful

✗ Second insert failed: Column 'last_name' cannot be null

🔹 Adding NOT NULL to Existing Column

You can add NOT NULL constraint to an existing column using ALTER TABLE. However, ensure the column has no NULL values before applying the constraint, otherwise MySQL will reject the modification with an error message. 

-- First, update any NULL values
UPDATE employees 
SET department = 'Unassigned' 
WHERE department IS NULL;

-- Then add NOT NULL constraint
ALTER TABLE employees 
MODIFY COLUMN department VARCHAR(50) NOT NULL;

Output:

✓ Column 'department' modified to NOT NULL successfully

🔹 Removing NOT NULL Constraint

To allow NULL values in a column that currently has NOT NULL constraint, use ALTER TABLE with MODIFY. This makes the column optional, allowing records to be inserted without providing a value for that field. 

-- Remove NOT NULL constraint
ALTER TABLE employees 
MODIFY COLUMN department VARCHAR(50) NULL;

Output:

✓ Column 'department' now allows NULL values

🔹 NOT NULL with DEFAULT Values

Combining NOT NULL with DEFAULT provides a fallback value when no data is supplied. This ensures the column always has a value while giving flexibility during insertion, making data entry easier without compromising data integrity. 

CREATE TABLE products (
    product_id INT NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    status VARCHAR(20) NOT NULL DEFAULT 'active'
);

Output:

✓ Table created with NOT NULL and DEFAULT constraints

🧠 Test Your Knowledge

What does the NOT NULL constraint do?