• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Not Null

← Previous Next →

The MySQL NOT NULL constraint is used to ensure that a column cannot store NULL values. In other words, every record must have a valid value for that column.

Using NOT NULL is essential for data integrity, especially for columns that are critical to your database, such as IDs, names, or key references. This ensures that no important data is accidentally left empty.

What Is NOT NULL?

In MySQL, a NULL value represents unknown or missing data. While some columns may allow NULL values, important columns should use the NOT NULL constraint to prevent missing data.

Key Points:

  • Ensures columns always have a value.

  • Can be applied at column creation or table modification.

  • Works with all data types.

  • Often combined with PRIMARY KEY or UNIQUE constraints.

Syntax for NOT NULL

When creating a table:

CREATE TABLE table_name (
    column_name datatype NOT NULL,
    ...
);

When modifying an existing table:

ALTER TABLE table_name
MODIFY column_name datatype NOT NULL;

Explanation:

  • column_name: Name of the column.

  • datatype: The data type of the column, such as INT or VARCHAR.

  • NOT NULL: Ensures no NULL values are allowed.

Creating a Table with NOT NULL Columns

Example: Create a students table where student_name cannot be NULL:

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(50) NOT NULL,
    age INT,
    city VARCHAR(50)
);

Explanation:

  • student_id is the primary key and automatically not null.

  • student_name must always have a value.

  • age and city are optional and can contain NULLs.

Adding NOT NULL to an Existing Column

If a column already exists but allows NULLs, you can enforce NOT NULL using ALTER TABLE:

ALTER TABLE students
MODIFY city VARCHAR(50) NOT NULL;

Explanation:

  • Updates the city column to disallow NULLs.

  • All existing rows must have a value in the column; otherwise, MySQL will throw an error.

NOT NULL and Default Values

It is often useful to combine NOT NULL with a DEFAULT value:

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    credits INT NOT NULL DEFAULT 3
);

Explanation:

  • credits must always have a value.

  • If no value is provided during insertion, it defaults to 3.

NOT NULL with Primary Key

Every PRIMARY KEY automatically implies NOT NULL:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50) NOT NULL
);

Explanation:

  • student_id is automatically NOT NULL because it is a primary key.

  • Adding NOT NULL explicitly is optional but acceptable for clarity.

Inserting Data with NOT NULL Columns

Example: Insert a row into the students table:

INSERT INTO students (student_name, age, city)
VALUES ('Ananya', 18, 'Delhi');

If you try to insert without a value for student_name:

INSERT INTO students (age, city)
VALUES (19, 'Mumbai');

Result: MySQL will throw an error because student_name is NOT NULL.

Practical Use Cases

  1. IDs and Primary Keys: Ensure every record has a unique identifier.

  2. Critical Fields: Names, emails, and essential reference data should never be empty.

  3. Financial Data: Prices, quantities, and amounts should always have valid values.

  4. Reference Tables: Foreign key columns often use NOT NULL to maintain integrity.

  5. Forms and Applications: Ensure users always provide required data before inserting records.

Best Practices

  1. Apply NOT NULL Early: Set important columns as NOT NULL during table creation.

  2. Combine with DEFAULT: Prevent errors by providing default values for NOT NULL columns.

  3. Validate Data: Ensure application-level validations align with database constraints.

  4. Avoid NULL in Critical Columns: Use NOT NULL to enforce mandatory data.

  5. Review Existing Tables: Modify old tables if critical columns currently allow NULLs.

Common Mistakes to Avoid

  1. Inserting NULL Values: Will cause errors for NOT NULL columns.

  2. Ignoring Defaults: NOT NULL without a default can lead to insert failures if no value is provided.

  3. Applying to Optional Columns: Avoid forcing NOT NULL on fields that may legitimately have missing data.

  4. Overlooking Existing Data: Altering a column to NOT NULL when NULLs exist will fail.

  5. Misunderstanding NULL vs Empty String: A blank string is different from NULL in MySQL.

Summary of the Tutorial

The MySQL NOT NULL constraint is crucial for ensuring essential data is never missing.

  • Prevents NULL values in critical columns.

  • Often used with PRIMARY KEY, FOREIGN KEY, and DEFAULT.

  • Helps maintain data integrity and consistency.

  • Should be applied thoughtfully to columns where missing data is unacceptable.

Using NOT NULL correctly ensures that your database remains reliable and consistent, reducing the chances of data errors and inconsistencies.

Practice Questions

  1. Create a table students with student_id as PRIMARY KEY and student_name as NOT NULL.

  2. Modify the city column in the students table to make it NOT NULL.

  3. Create a table courses with course_name as NOT NULL and credits as NOT NULL DEFAULT 3.

  4. Insert a new row into the students table without providing a value for a NOT NULL column and observe the result.

  5. Create a table employees with employee_id as PRIMARY KEY, employee_name as NOT NULL, and salary as NOT NULL.

  6. Add a NOT NULL constraint to an existing column email in the employees table.

  7. Create a table orders with order_id as AUTO_INCREMENT PRIMARY KEY and order_date as NOT NULL.

  8. Try inserting a row into the courses table without specifying credits and check the default behavior.

  9. Modify the age column in the students table to be NOT NULL and test with existing data.

  10. Create a table departments with department_id as PRIMARY KEY and department_name as NOT NULL.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top