How To Alter Column From NULL to NOT NULL

Last updated on August 15th, 2024 at 09:31 am

All database systems allow you to store NULL as well as non NULL values. Sometimes you may need to change a nullable column with NULL values into one without NULL values. This may be because many filters and conditions do not work properly with NULL values. It may be also because you do not want to allow NULL values in certain database columns due to changes in business rules. In this article, we will look at how to alter column from NULL to NOT NULL values. You can use these steps to change column from NULL to NOT NULL in MySQL, PostgreSQL and SQL Server.

How To Alter Column From Null to Not Null

Here are the steps to alter column from NULL to NOT NULL. Let’s say you have a table sales(id, amount, order_date).

mysql> create table sales(id int, amount int,order_date date);

mysql> insert into sales(id, amount)
       values(1, 100),(2,300),(3,45);

mysql> insert into sales(id, order_date)
       values(4,'2020-11-01');

mysql> select * from sales;
+------+--------+------------+
| id   | amount | order_date |
+------+--------+------------+
|    1 |    100 | NULL       |
|    2 |    300 | NULL       |
|    3 |     45 | NULL       |
|    4 |   NULL | 2020-11-01 |
+------+--------+------------+

1. Update Table to Remove Null Values

Before you run ALTER TABLE statement to convert a nullable column into not nullable one, you need to change all its NULL values to non-null ones. Otherwise, you will get an error. This is because you can change column to be NOT NULL only when already non of its values are NULL. Therefore, the first step is to remove null values from our column using UPDATE statement.

As you can see, the above table contains null values in order_date and amount columns.

Also read : How to Fix Incorrect String Value in MySQL

Let us say you want to change amount column from null to not null. So first we will remove null values from this column using UPDATE statement.

mysql> update sales set amount=0 
       where amount is null;

mysql> select * from sales;
+------+--------+------------+
| id   | amount | order_date |
+------+--------+------------+
|    1 |    100 | NULL       |
|    2 |    300 | NULL       |
|    3 |     45 | NULL       |
|    4 |      0 | 2020-11-01 |
+------+--------+------------+

Also read : How to Change Column Size in MySQL

Similarly, if you want to change order_date column from null to not null, first update null values to not null values, as shown below.

mysql> update sales set order_date='0000-00-00' 
       where order_date is null;

mysql> select * from sales;
+------+--------+------------+
| id   | amount | order_date |
+------+--------+------------+
|    1 |    100 | 0000-00-00 |
|    2 |    300 | 0000-00-00 |
|    3 |     45 | 0000-00-00 |
|    4 |      0 | 2020-11-01 |
+------+--------+------------+

Also read : Top 5 Data Modeling tools for SQL Server

Depending on the existing data type of your column (number or date), you need to accordingly set it to non-null value. In our examples, we have set int column to 0 and date column to ‘0000-00-00’. If your column is a text or string, then you need to set an appropriate non-null string.

2. Alter Table and Modify Column

Next, we will change amount column from null to not null, using ALTER TABLE statement. You can run this statement only when none of the values of a column are null. Here is the syntax for it.

ALTER TABLE table_name ALTER COLUMN col_name data_type NOT NULL;

Replace table_name, col_name and data_type with table name, column name and data type respectively.

Here’s the SQL query to change amount column from NULL to NOT NULL.

For MySQL
---------
ALTER TABLE sales
MODIFY COLUMN amount int NOT NULL;

For SQL Server/PostgreSQL
-------------------------
ALTER TABLE sales
ALTER COLUMN amount int NOT NULL;

Similarly, here are the SQL queries to change order_date column from NULL to NOT NULL

For MySQL
---------
ALTER TABLE sales
MODIFY COLUMN order_date date NOT NULL;

For SQL Server/PostgreSQL
-------------------------
ALTER TABLE sales
ALTER COLUMN order_date date NOT NULL;

Also read : How to List databases and tables in PostgreSQL

We verify the above change by running the describe table command in MySQL.

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

You will see that columns amount and order_date contain NO value for NULL column indicating that they are not permitted to store NULL values.

Conclusion

In this article, we have learnt how to change a column from NULL to NOT NULL. Often, many database administrators need to change nullable columns to NOT null. It is important to remember that there are two steps involved instead of a single command for it. Also, when you update the present null values of the column to non-null value, you need to choose the appropriate value(0/’0000-00-00′) depending on its data type (int/date). Only after this step, you can use ALTER TABLE statement to convert the column to NOT NULL type.

Ubiq makes it easy to visualize data, and monitor them in real-time dashboards. Try Ubiq for free.