MariaDB Not Null

Summary: in this tutorial, you will learn how to use a not null constraint for a column to ensure values stored in the column are not null.

Introduction to the MariaDB not null constraint

The not null is a domain integrity constraint that ensures values stored in a column are not null.

Here is the syntax of defining a not null constraint:

column_name datatype not null;
Code language: SQL (Structured Query Language) (sql)

If you insert or update null to a column that has a not null constraint, MariaDB rejects the change and issues an error.

The following statement creates a table called courses:

create table courses(
    course_id int auto_increment,
    course_name varchar(100) not null,
    summary varchar(255),
    primary key(course_id)
);
Code language: SQL (Structured Query Language) (sql)

The course_id is the primary key of the courses table, therefore, it doesn’t accept null values because it has an implicit not null constraint.

The course_name has the not null constraint so it also doesn’t accept null values.

This statement attempts to insert a null value into the course_name column:

insert into courses(course_name)
values(null);
Code language: SQL (Structured Query Language) (sql)

MariaDB issued the following error:

SQL Error (1048): Column 'course_name' cannot be null
Code language: SQL (Structured Query Language) (sql)

Adding a not null constraint to an existing column

To add a not null constraint to an existing column, you follow these steps:

• First, update null values to a non-null value if available.
• Second, modify the column to include a not null constraint.

For example, to add a not null constraint to the summary column of the courses table, you use these steps:

First, update null values in the summary column to non-null values:

update courses
set summary = 'N/A'
where summary is null;
Code language: SQL (Structured Query Language) (sql)

Second, modify the summary column to include a not null constraint:

alter table courses
modify summary varchar(255) not null;
Code language: SQL (Structured Query Language) (sql)

Removing a not null constraint

To remove a not null constraint from a column, you use the alter table modify statement:

alter table table_name
modify column_name datatype;
Code language: SQL (Structured Query Language) (sql)

For example, the following statement removes the not null constraint from the summary column of the courses table:

alter table courses
modify summary varchar(255);
Code language: SQL (Structured Query Language) (sql)

To verify whether the not null constraint has been removed, you can use the describe statement:

describe courses;
Code language: SQL (Structured Query Language) (sql)

or use show create table statement:

show create table courses;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the not null constraint for a column to ensure values stored in the column are not null.