{"id":5639,"date":"2017-02-09T20:59:23","date_gmt":"2017-02-10T03:59:23","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=5639"},"modified":"2024-01-29T20:42:44","modified_gmt":"2024-01-30T03:42:44","slug":"mysql-not-null-constraint","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-not-null-constraint\/","title":{"rendered":"MySQL NOT NULL Constraint"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about MySQL NOT NULL<\/code> constraints including defining a NOT NULL<\/code> constraint for a column, adding a NOT NULL<\/code> constraint to an existing column, and removing a NOT NULL<\/code> constraint from a column.<\/p>\n\n\n\n

Introduction to MySQL NOT NULL constraints<\/h2>\n\n\n\n

A NOT NULL<\/code> constraint ensures that values stored in a column are not NULL<\/a>. The syntax for defining a NOT NULL<\/code> constraint is as follows:<\/p>\n\n\n

column_name data_type NOT NULL;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
A column may have only one NOT NULL<\/code> constraint, which enforces the rule that the column must not contain any NULL values. <\/p>\n\n\n\n
For example, the following creates the tasks<\/code> table using the CREATE TABLE<\/code> statement:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> tasks (\n    id<\/span> INT<\/span> AUTO_INCREMENT PRIMARY KEY<\/span>,\n    title VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    start_date DATE<\/span> NOT<\/span> NULL<\/span>,\n    end_date DATE<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In the tasks<\/code> table, we explicitly define the title<\/code> and start_date<\/code> columns with NOT NULL<\/code> constraints. <\/p>\n\n\n\n
The end_date<\/code> column can have NULL values, as when creating a new task, you may not know its completion date<\/p>\n\n\n\n
The following shows the structure of the tasks table:<\/p>\n\n\n
DESC tasks;<\/code><\/span><\/pre>\n\n\n
Output:<\/p>\n\n\n
+------------+--------------+------+-----+---------+----------------+\n| Field      | Type         | Null<\/span> | Key | Default<\/span> | Extra          |\n+------------+--------------+------+-----+---------+----------------+\n| id         | int          | NO   | PRI | NULL<\/span>    | auto_increment |\n| title      | varchar(255<\/span>) | NO   |     | NULL<\/span>    |                |\n| start_date | date         | NO   |     | NULL<\/span>    |                |\n| end_date   | date         | YES  |     | NULL<\/span>    |                |\n+------------+--------------+------+-----+---------+----------------+\n4<\/span> rows in set (0.01<\/span> sec)<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
It’s a good practice to have the NOT NULL<\/code> constraint in every column of a table unless you have a specific reason not to.<\/p>\n\n\n\n
Adding a NOT NULL constraint to an existing column<\/h2>\n\n\n\n
Typically, you add NOT NULL<\/code> constraints to columns when you create the table. However, you may want to add a NOT NULL<\/code> constraint to a column of an existing table. In this case, you use the following steps:<\/p>\n\n\n\n
    \n
  1. First, check the current values of the column if there are any NULL<\/code> values.<\/li>\n\n\n\n
  2. Second, update the NULL<\/code> to non-NULL<\/code>.<\/li>\n\n\n\n
  3. Third, modify the column with a NOT NULL<\/code> constraint.<\/li>\n<\/ol>\n\n\n\n
    Consider the following example.<\/p>\n\n\n\n
    First, insert some rows<\/a> into the tasks<\/code> table:<\/p>\n\n\n
    INSERT<\/span> INTO<\/span> tasks(title ,start_date, end_date)\nVALUES<\/span>('Learn MySQL NOT NULL constraint'<\/span>, '2017-02-01'<\/span>,'2017-02-02'<\/span>),\n      ('Check and update NOT NULL constraint to your database'<\/span>, '2017-02-01'<\/span>,NULL<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    If you want to require users to provide an estimated end date when creating a new task, you can add a NOT NULL constraint to the end_date<\/code> column of the tasks<\/code> table.<\/p>\n\n\n\n
    SELECT<\/span> * FROM<\/span> tasks \nWHERE<\/span> end_date IS<\/span> NULL<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/p>\n\n\n
    +----+-------------------------------------------------------+------------+----------+\n| id | title                                                 | start_date | end_date |\n+----+-------------------------------------------------------+------------+----------+\n|  2<\/span> | Check and<\/span> update NOT NULL<\/span> constraint to your database | 2017<\/span>-02<\/span>-01<\/span> | NULL<\/span>     |\n+----+-------------------------------------------------------+------------+----------+\n1<\/span> row in set (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
    Third, update<\/a> the NULL<\/code> values to non-null values. In this case, you can create a rule that sets to one week after the start date when the end_date is NULL.<\/p>\n\n\n
    UPDATE<\/span> tasks \nSET<\/span> \n    end_date = start_date + 7<\/span>\nWHERE<\/span>\n    end_date IS<\/span> NULL<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    This query verifies the update:<\/p>\n\n\n
    SELECT<\/span> * FROM<\/span> tasks;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/p>\n\n\n
    +----+-------------------------------------------------------+------------+------------+\n| id | title                                                 | start_date | end_date   |\n+----+-------------------------------------------------------+------------+------------+\n|  1<\/span> | Learn MySQL NOT NULL<\/span> constraint                       | 2017<\/span>-02<\/span>-01<\/span> | 2017<\/span>-02<\/span>-02<\/span> |\n|  2<\/span> | Check and<\/span> update NOT NULL<\/span> constraint to your database | 2017<\/span>-02<\/span>-01<\/span> | 2017<\/span>-02<\/span>-08<\/span> |\n+----+-------------------------------------------------------+------------+------------+\n2<\/span> rows in set (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
    ALTER<\/span> TABLE<\/span> table_name\nCHANGE<\/span> \n   old_column_name \n   new_column_name column_definition;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    In this case, the name of the old and new column names are the same except that the column must have a NOT NULL<\/code> constraint:<\/p>\n\n\n
    ALTER<\/span> TABLE<\/span> tasks \nCHANGE<\/span> \n    end_date \n    end_date DATE<\/span> NOT<\/span> NULL<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    DESC tasks;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/p>\n\n\n
    +------------+--------------+------+-----+---------+----------------+\n| Field      | Type         | Null<\/span> | Key | Default<\/span> | Extra          |\n+------------+--------------+------+-----+---------+----------------+\n| id         | int          | NO   | PRI | NULL<\/span>    | auto_increment |\n| title      | varchar(255<\/span>) | NO   |     | NULL<\/span>    |                |\n| start_date | date         | NO   |     | NULL<\/span>    |                |\n| end_date   | date         | NO   |     | NULL<\/span>    |                |\n+------------+--------------+------+-----+---------+----------------+\n4<\/span> rows in set (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
    The output indicates that the NOT NULL<\/code> constraint was added to the end_date<\/code> column successfully.<\/p>\n\n\n\n
    Removing a NOT NULL constraint<\/h2>\n\n\n\n
    To drop a NOT NULL<\/code> constraint for a column, you use the ALTER TABLE..MODIFY<\/code> statement:<\/p>\n\n\n
    ALTER<\/span> TABLE<\/span> table_name\nMODIFY<\/span> column_name column_definition;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Note that the column definition (column_definition<\/code>) must restate the original column definition without the NOT NULL<\/code> constraint.<\/p>\n\n\n\n
    For example, the following statement removes the NOT NULL<\/code> constraint from the end_date<\/code> column in the tasks<\/code> table:<\/p>\n\n\n
    ALTER<\/span> TABLE<\/span> tasks \nMODIFY<\/span> end_date DATE<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Here’s the structure of the tasks<\/code> table:<\/p>\n\n\n
    DESC tasks;<\/code><\/span><\/pre>\n\n\n
    Output:<\/p>\n\n\n
    +------------+--------------+------+-----+---------+----------------+\n| Field      | Type         | Null<\/span> | Key | Default<\/span> | Extra          |\n+------------+--------------+------+-----+---------+----------------+\n| id         | int          | NO   | PRI | NULL<\/span>    | auto_increment |\n| title      | varchar(255<\/span>) | NO   |     | NULL<\/span>    |                |\n| start_date | date         | NO   |     | NULL<\/span>    |                |\n| end_date   | date         | YES  |     | NULL<\/span>    |                |\n+------------+--------------+------+-----+---------+----------------+\n4<\/span> rows in set (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
    Summary<\/h2>\n\n\n\n
      \n
    • Use NOT NULL<\/code> constraint to ensure that a column does not contain any NULL<\/code> values.<\/li>\n\n\n\n
    • Use ALTER TABLE ... CHANGE<\/code> statement to add a NOT NULL<\/code> constraint to an existing column.<\/li>\n\n\n\n
    • Use ALTER TABLE ... MODIFY<\/code> to drop a NOT NULL<\/code> constraint from a column.<\/li>\n<\/ul>\n
      \n\t
      \n\t\t
      Was this tutorial helpful? <\/div>\n\t\t
      \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
      \n\t\t
      \n\t\t\t
      <\/div>\n\t\t\t\n\t\t\t
    Finally, verify the change using the DESCRIBE<\/code><\/a> statement:<\/p>\n\n\n
    Third, add a NOT NULL<\/code> constraint to the end_date<\/code> column using the following ALTER TABLE<\/code><\/a> statement:<\/p>\n\n\n
    The query<\/a> returned one row with NULL<\/code> in the column end_date<\/code>.<\/p>\n\n\n\n
    Second, find rows with NULLs<\/code> in the column end_date<\/code> using the IS NULL<\/a><\/code> operator:<\/p>\n\n\n
Generally, NULL<\/code> values may complicate your queries because you need to use NULL-related functions such as ISNULL()<\/a><\/code>, IFNULL()<\/a><\/code>, and NULLIF()<\/a><\/code> to handle them.<\/p>\n\n\n\n
The id<\/code> column has the PRIMARY KEY<\/code><\/a> constraint, therefore, it implicitly includes a NOT NULL<\/code> constraint.<\/p>\n\n\n\n
In other words, if you attempt to update<\/a> or insert<\/a> a NULL value into a NOT NULL column, MySQL will issue an error.<\/p>\n\n\n\n