How To Set Null Value In Mysql

Summary: in this tutorial, you lot volition learn how to define a Not NULL constraint for a column, add a NOT NULL constraint to an existing column, and remove a Not Aught constraint from a column.

Introduction to the MySQL `NOT Naught` constraint

The Non Goose egg constraint is a column constraint that ensures values stored in a column are not Aught.

The syntax of defining a NOT Cypher constraint is as follows:

column_name data_type Not Cypher;

Lawmaking language: SQL (Structured Query Linguistic communication) ( sql )

A cavalcade may contain only ane NOT Zip constraint which specifies a dominion that the column must not contain any Nil value. In other words, if you lot update or insert Nothing into a NOT NULL cavalcade, MySQL will issue an mistake.

The post-obit CREATE TABLE statement creates the tasks table:

CREATE TABLE tasks ( id INT AUTO_INCREMENT PRIMARY Cardinal, title VARCHAR(255) NOT NULL, start_date Appointment NOT NULL, end_date Appointment );

Code linguistic communication: SQL (Structured Query Linguistic communication) ( sql )

In the tasks table, we explicitly ascertain the title and start_date columns with NOT NULL constraints. The id column has the PRIMARY Key constraint, therefore, it implicitly includes a Non NULL constraint.

The end_date column can have NULL values, assuming that when you create a new task, you may not know when the task can exist completed.

It'due south a good practice to have the Non Zip constraint in every cavalcade of a table unless yous take a good reason non to do and so.

Generally, the NULL value makes your queries more complicated because yous accept to apply functions such equally ISNULL(), IFNULL(), and NULLIF() for handling Zilch.

Add together a `NOT Zero` constraint to an existing cavalcade

Typically, you add Not NULL constraints to columns when you create the table. Sometimes, yous want to add together a NOT NULL constraint to a Naught-able column of an existing table. In this case, you use the post-obit steps:

Bank check the electric current values of the column if there is any Goose egg.
Update the NULL to non-NULL if NULLs exist.
Modify the column with a Not Zilch constraint.

Consider the following instance.

The post-obit statement inserts some rows into the tasks table for the demonstration.

                                          INSERT                INTO                tasks(title ,start_date, end_date)                VALUES('Learn MySQL Not NULL constraint',                '2017-02-01','2017-02-02'),       ('Check and update Not NULL constraint to your database',                '2017-02-01',Cypher);                          
                          Code language:              SQL (Structured Query Linguistic communication)              (              sql              )

Suppose that you desire to force users to give an estimated end date when creating a new task. To implement this dominion, you add a NOT NULL constraint to the end_date column of the tasks table.

Offset, use the IS Zilch operator to find rows with NULLs in the column end_date :

                                          SELECT                *                FROM                tasks                WHERE                end_date                IS                NULL;                          
                          Lawmaking language:              SQL (Structured Query Language)              (              sql              )

The query returned one row with NULL in the column end_date.

2d, update the Aught values to non-null values. In this case, y'all can make up a rule that if the end_date is Zero, the end engagement is 1 week later the start date.

UPDATE tasks SET end_date = start_date + seven WHERE end_date IS Zippo;

Code language: SQL (Structured Query Language) ( sql )

This query verifies the update:

                                          SELECT                *                FROM                tasks;                          
                          Code linguistic communication:              SQL (Structured Query Language)              (              sql              )

Third, add a Non NULL constraint to the end_date column using the post-obit ALTER Tabular array argument:

Change TABLE table_name CHANGE old_column_name new_column_name column_definition;

Code language: SQL (Structured Query Language) ( sql )

In this case, the name of the old and new column names are the same except that the cavalcade must have a NOT Nothing constraint:

ALTER TABLE tasks CHANGE end_date end_date DATE NOT Null;

Code language: SQL (Structured Query Language) ( sql )

Let's verify the change by using the Describe statement:

DESCRIBE tasks;

Code language: SQL (Structured Query Language) ( sql )

As you see, the NOT Null constraint was added to the end_date column successfully.

Drop a `Not NULL` constraint

To drop a Non NULL constraint for a column, you use the ALTER TABLE..Change statement:

ALTER TABLE table_name MODIFY column_name column_definition;

Code language: SQL (Structured Query Linguistic communication) ( sql )

Note that the column definition (column_definition) must recapitulate the original column definition without the NOT NULLconstraint.

For instance, the following statement removes the NOT Nil constraint from the end_date column in the tasks tabular array:

ALTER Tabular array tasks Alter end_date end_date DATE Non Cypher;

Code language: SQL (Structured Query Language) ( sql )

To ensure that the statement actually removed the NOT Aught constraint, you can use the Bear witness CREATE TABLE command to view the full column definition:

MySQL NOT NULL constraint - removal example

Note that the DESCRIBE statement besides does the fob:

In this tutorial, you have learned how to ascertain a Not NULL constraint for a column, add a NOT NULL constraint to a column, and remove a Not Naught constraint from a cavalcade.

Was this tutorial helpful?