MySQL NOT NULL

Last Updated: 28 May, 2023

In MySQL, a NOT NULL is a column-level constraint. It is used to apply a restriction on the columns to not accept NULL values. If we try to update or insert a NULL value into a NOT NULL column, MySQL will generate an error message.

• A table can have any number of columns with NOT NULL constraints.
• We apply NOT NULL constrains with those columns whose values are very important.
• We can add NOT NULL constrains with new or existing columns both.

Add NOT NULL constraint when creating a table

To apply the NOT NULL constraint to a column when creating a table, we can use the following syntax:

Syntax:

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

Example:

  CREATE TABLE student (
  id INT NOT NULL,
  name VARCHAR(50) NOT NULL,
  roll_no int,
  );

Add NOT NULL constraint in the existing column

To add a NOT NULL constraint to a column in a MySQL table, we can use the ALTER TABLE statement, followed by the MODIFY COLUMN command, and specify the NOT NULL keyword:

Syntax:

ALTER TABLE table_name MODIFY COLUMN column_name data_type NOT NULL;

Example:

ALTER TABLE student MODIFY COLUMN last_name VARCHAR(50) NOT NULL;

Once the NOT NULL constraint has been applied to a column, any attempts to insert a NULL value into that column will result in an error. It's important to note that NOT NULL constraints can only be added to columns that do not already contain NULL values. If we want to add a NOT NULL constraint to a column that already contains NULL values, we must first update the table to replace the NULL values with a valid value, and then apply the NOT NULL constraint.

Drop a NOT NULL constraint

To drop a NOT NULL constraint for a column, we use the ALTER TABLE statement.

Syntax:

ALTER TABLE table_name MODIFY COLUMN column_name data_type

Example:

ALTER TABLE student MODIFY COLUMN last_name VARCHAR(50);