MySQL NOT NULL Constraint

The NOT NULL constraint in MySQL ensures that a column cannot store NULL values. It is used to enforce data integrity by ensuring that essential data is always present in a table.

1. Why Use NOT NULL?

✔ Prevents Missing Data – Ensures critical fields always have values.
✔ Improves Data Consistency – Avoids NULL-related logic issues in applications.
✔ Optimizes Queries – NULL values can impact performance in some cases.

2. How to Use NOT NULL in MySQL?

The NOT NULL constraint is applied at the column level while creating or modifying a table.

Syntax:

column_name data_type NOT NULL

Example: Creating a Table with NOT NULL Columns

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    age INT
);

✔ username and email must have values.
✔ age is optional and can store NULL.

3. How to Add NOT NULL to an Existing Column?

If a column was initially created without NOT NULL, you can modify it later.

ALTER TABLE users MODIFY username VARCHAR(50) NOT NULL;

⚠ Warning: If the column already contains NULL values, MySQL will reject this change. You must update NULL values first.

UPDATE users SET username = 'default_value' WHERE username IS NULL;

Then apply the NOT NULL constraint.

4. How to Remove NOT NULL from a Column?

To allow NULL values in a column that previously had NOT NULL:

ALTER TABLE users MODIFY username VARCHAR(50) NULL;

✔ Now, username can store NULL values.

5. Handling NOT NULL with DEFAULT Values

To prevent errors when inserting records, you can set a default value for NOT NULL columns.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2) NOT NULL DEFAULT 5000.00
);

✔ If a value is not provided for salary, it defaults to 5000.00.

6. Common Errors & Solutions

Error Message	Cause	Solution
`Column 'column_name' cannot be null`	Trying to insert `NULL` into a `NOT NULL` column.	Provide a non-null value or set a default value.
`Invalid use of NULL value`	Updating a `NOT NULL` column with `NULL`.	Ensure updates provide a valid value.
`ERROR 1138: Invalid use of NULL value`	Trying to modify a column to `NOT NULL` while it contains `NULL` values.	Update existing NULL values before applying NOT NULL.

7. How to Check if a Column has a NOT NULL Constraint?

To check the constraints of a table column:

SHOW COLUMNS FROM users;

✔ This will display whether a column is NULL or NOT NULL.

8. Summary

NOT NULL prevents NULL values in a column.
Use ALTER TABLE to add or remove NOT NULL.
Ensure existing NULL values are handled before applying NOT NULL.
Use DEFAULT values to prevent errors during inserts.

Would you like to see more real-world examples of using NOT NULL constraints? 🚀

Souy Soeng

MySQL NOT NULL Constraint