How To Alter Column From NULL to NOT NULL

Sometimes you may need to change a nullable column with NULL values into one without NULL values. 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.

1. Update Table to Remove Null Values.

The first step is to remove null values from our column. 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 |
+------+--------+------------+

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

2. Alter Table and Modify Column

Next, we will change amount column from null to not null, using ALTER TABLE statement.

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.

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