MySQL NOT NULL Constraint

  1. Overview
  2. MYSQL
  3. MySQL Database
  4. MySQL NOT NULL Constraint

The NOT NULL constraint in MySQL ensures that a column cannot contain NULL values. This is important to ensure that every record has a valid value for that column, which can help maintain the integrity and consistency of the data.

By default, MySQL allows columns to have NULL values unless explicitly defined with the NOT NULL constraint. When a column is defined as NOT NULL, it becomes mandatory to provide a value for that column during INSERT and UPDATE operations. If no value is provided, MySQL will return an error.

Applying the NOT NULL Constraint

1. Adding the NOT NULL Constraint on Table Creation

When creating a table, you can specify the NOT NULL constraint directly for one or more columns to prevent them from having NULL values.

Example: Create a Table with NOT NULL Constraint

CREATE TABLE Persons (
    ID INT NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255) NOT NULL,
    Age INT
);

In this example:

  • The ID, LastName, and FirstName columns are defined with the NOT NULL constraint.
  • The Age column does not have the NOT NULL constraint, so it can accept NULL values.

2. Adding the NOT NULL Constraint After Table Creation

If you need to add the NOT NULL constraint to an existing table, you can use the ALTER TABLE statement. This is useful when you realize that a column should not accept NULL values after the table has been created.

Example: Adding NOT NULL Constraint to an Existing Column

ALTER TABLE Persons
MODIFY Age INT NOT NULL;

In this example:

  • The Age column, which was previously allowed to contain NULL values, is now modified to enforce the NOT NULL constraint.

Important Notes:

  • When a column has the NOT NULL constraint, you must insert a value for that column in every row.
  • If you try to insert a record without providing a value for a NOT NULL column, MySQL will throw an error.
  • You cannot add a NOT NULL constraint to a column that already contains NULL values unless you first update the existing NULL values with valid data.

Summary of Using NOT NULL:

  • On Table Creation: The NOT NULL constraint ensures that the column cannot contain NULL values at the time the table is created.
  • On Table Modification: You can modify an existing column to enforce the NOT NULL constraint using ALTER TABLE.
  • Error Handling: If you attempt to insert or update a NOT NULL column without a value, an error will occur.

The NOT NULL constraint is a fundamental tool in database design to enforce data integrity and ensure that essential fields always have meaningful values.

Was this article helpful?