Making a Column Nullable in SQL

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.