mysql add not null constraint

How To Add NOT NULL Constraint in MySQL

If you need to set a MySQL column to not accept null values, then you can add NOT NULL constraint in MySQL. You can add NOT NULL constraint when you create table table using CREATE TABLE statement, or add NOT NULL constraint in existing table using ALTER TABLE statement. Here’s how to add NOT NULL constraint in MySQL.

 

How To Add NOT NULL Constraint in MySQL

Here are the steps to add NOT NULL constraint for column in MySQL, add NOT NULL constraint to existing column and remove NOT NULL constraint from a column.

Here’s the syntax to define NOT NULL constraint in MySQL when you create new table.

column_name data_type NOT NULL;

In the above statement, you need to specify NOT NULL after mentioning the column_name and its data_type

Here’s an example to add NOT NULL constraint in MySQL.

mysql> create table product_sales(
     id int,
     amount int NOT NULL,
     order_date date
     );

mysql> describe product_sales;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| id         | int(11) | YES  |     | NULL    |       |
| amount     | int(11) | NO   |     | NULL    |       |
| order_date | date    | YES  |     | NULL    |       |
+------------+---------+------+-----+---------+-------+

mysql> insert into product_sales(id, order_date)
     values(1,'2020-08-01');
ERROR 1364 (HY000): Field 'amount' doesn't have a default value

In the above CREATE TABLE query, we have added a NOT NULL constraint for amount column. When you insert a NULL value in this column, MySQL will give an error.

Bonus Read : How to Add Default Constraint in MySQL

 

ALTER TABLE Add NOT NULL constraint in MySQL

You can also add NOT NULL constraint to existing column in MySQL using ALTER TABLE … CHANGE statement. Here’s the syntax to add not null constraint in existing table in MySQL.

ALTER TABLE table_name
CHANGE 
   old_column_name 
   new_column_name column_definition;

In the above query, we mention the same column name for old_column_name as well as new_column_name. The new_column_name must be followed by its column_definition of data type and NOT NULL keyword.

Here’s an example of SQL query to add NOT NULL constraint to existing column in MySQL.

mysql> ALTER TABLE product_sales
     CHANGE
         order_date
         order_date DATE NOT NULL;

mysql> describe product_sales;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| id         | int(11) | YES  |     | NULL    |       |
| amount     | int(11) | NO   |     | NULL    |       |
| order_date | date    | NO   |     | NULL    |       |
+------------+---------+------+-----+---------+-------+

In the above query, we have added NOT NULL constraint to existing column order_date

Bonus Read : MySQL Select Top N Rows Per Group

 

How to Drop NOT NULL constraint

You can also drop NOT NULL constraint using ALTER TABLE … MODIFY statement. Here’s the syntax to remove NOT NULL constraint in MySQL.

ALTER TABLE table_name
MODIFY column_name column_definition;

In the above query, you need to specify the column name and definition for which you want to remove NOT NULL constraint.

Here’s an example to remove NOT NULL constraint for order_date column.

mysql> ALTER TABLE product_sales
       MODIFY order_date DATE;

mysql> describe product_sales;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| id         | int(11) | YES  |     | NULL    |       |
| amount     | int(11) | NO   |     | NULL    |       |
| order_date | date    | YES  |     | NULL    |       |
+------------+---------+------+-----+---------+-------+

Bonus Read : How to Get Last Week Data in MySQL

 

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

mm

About Ubiq

Ubiq is a powerful dashboard & reporting platform. Build dashboards, charts & reports for your business in minutes. Try it for free!