Yes, we're now running our Black Friday Sale. All Courses are 33% off until 1st December, 2026 at 17:00 Romania time:
Making a Column Nullable in SQL
Last updated: March 18, 2025
1. Introduction
When working with relational databases, we may encounter situations where a column was originally defined as NOT NULL. However, as requirements evolve, we might need to modify it to allow NULL values.
In this tutorial, let’s explore how to make a column nullable in SQL across PostgreSQL, SQL Server, and MySQL.
We’ll use the Student table from the Baeldung University database to demonstrate queries. The national_id column is currently NOT NULL, and we’ll modify it to allow NULL values.
2. Understanding Nullable and NOT NULL Constraints
In SQL, a column can be either nullable, allowing NULL values, or defined with the NOT NULL constraint, requiring a value in every row. While the NOT NULL constraint ensures data integrity by preventing missing values, NULL is often allowed in columns to represent unavailable or optional data in business scenarios. If we insert a NULL value into a NOT NULL column, the database rejects the operation and returns an error.
We can use the ALTER TABLE command to remove the NOT NULL constraint from an existing column and allow NULL values to be inserted.
3. PostgreSQL
In PostgreSQL, we can use the ALTER TABLE command to make a column nullable:
ALTER TABLE student
ALTER COLUMN national_id
DROP NOT NULL;
This removes the NOT NULL constraint from the national_id column, allowing it to store NULL values.
4. MySQL
Let’s look at the query in MySQL to achieve the same:
ALTER TABLE Student
MODIFY COLUMN national_id BIGINT NULL;
Unlike PostgreSQL, MySQL requires the MODIFY command along with the data type and constraints. Here, we specify NULL to remove the previous NOT NULL constraint.
5. SQL Server
Now, let’s look at the query in SQL Server:
ALTER TABLE Student
ALTER COLUMN national_id BIGINT NULL;
This is similar to the MySQL query, but SQL Server uses ALTER COLUMN instead of MODIFY. Like in MySQL, we must specify the data type, unlike in PostgreSQL.
6. Conclusion
In this article, we examined how to modify a column to allow NULL values in three popular relational databases: PostgreSQL, MySQL, and SQL Server. Although the fundamental concept is the same, using the ALTER TABLE command to remove the NOT NULL constraint, the syntax varies slightly across each database.
The code backing this article is available on GitHub. Once you're logged in as a Baeldung Pro Member, start learning and coding on the project.