Altering a Column From NULL to NOT NULL in SQL

1. Overview

When we create a table column in SQL, it can accept NULL values by default. To disallow NULL values in a table, we need to use the NOT NULL constraint explicitly. This constraint ensures each row must have a valid (non-null) value for that column. We can add the NOT NULL constraint during table creation or by altering an existing column.

In this tutorial, we’ll explain how to update a column from NULL to NOT NULL in SQL. For this purpose, we’ll use the Student table of the University database.

2. Updating Table to Remove NULL Values

In SQL, altering a column to NOT NULL means that a column can’t contain NULL values. We can do this by running the ALTER TABLE statement. However, before altering a column to NOT NULL, first, we should replace the NULL values with some default values according to the column’s data type.

Let’s check out the content of an already existing Student table using the SELECT statement:

SELECT * FROM Student;

In the gpa column, several fields contain NULL values:

+-----------+----------------+--------------+--------------+-----------------+------------ ----+----------+
| id        | name           | national_id  | birth_date   | enrollment_date | graduation_date | gpa      |
+-----------+----------------+--------------+--------------+-----------------+-----------------+----------+
| 1001      | John Liu       | 123345566    | 2001-04-05   | 2020-01-15      | 2024-06-15      | 4        |
| 1003      | Rita Ora       | 132345166    | 2001-01-14   | 2020-01-15      | 2024-06-15      | 4.2      |
| 1007      | Philip Lose    | 321345566    | 2001-06-15   | 2020-01-15      | 2024-06-15      | 3.8      |
| 1010      | Samantha Prabu | 3217165566   | 2001-03-21   | 2020-01-15      | 2024-06-15      | 4.9      |
| 1011      | Vikas Jain     | 321345662    | 2001-07-18   | 2020-01-15      | [null]          | 3.3      |
| 1610      | Ritu Raj       | 3203455662   | 2002-05-15   | 2020-01-15      | 2025-06-15      | [null]   |
| 1607      | Peter Liu      | 3203455662   | 2002-06-21   | 2020-01-15      | 2025-06-15      | [null]   |
+-----------+----------------+--------------+---------------+-----------------+----------------+----------+
Total rows: 25 of 25 Query Complete 00:00:00.364

We can use the IS NULL operator in the WHERE clause to find all the NULL values in the gpa column and update them with 0.0:

UPDATE Student SET gpa = 0.0
WHERE gpa IS NULL;

The output confirms that we successfully updated four records in the gpa column:

UPDATE 4
Query returned successfully in 80 msec

Now we can easily change the gpa column to NOT NULL.

3. Changing Constraints

In PostgreSQL, we can run the ALTER TABLE statement with the ALTER COLUMN clause to update a column to NOT NULL:

ALTER TABLE Student
ALTER COLUMN gpa SET NOT NULL;

The output verifies that the gpa column of the Student table is altered:

ALTER TABLE
Query returned successfully in 39 msec

Conversely, in SQL Server, when altering a column to NOT NULL, we need to specify the data type of the column along with the ALTER COLUMN clause:

ALTER TABLE Student 
ALTER COLUMN gpa REAL NOT NULL;

In MySQL, the syntax for altering a column to NOT NULL differs from PostgreSQL and SQL Server. It involves using the ALTER TABLE statement with the MODIFY clause and specifying the data type of the column to be altered:

ALTER TABLE Student 
MODIFY gpa REAL NOT NULL;

Let’s try to insert a NULL value in the gpa column to verify the altered nullability:

INSERT INTO Student (id, name, national_id, birth_date, enrollment_date, graduation_date, gpa)
VALUES (1050, 'Mesam Mujtaba', 123456789, '1992-06-20', '2020-01-01', '2024-01-01', NULL);

The output ensures that trying to add a NULL value to the gpa column results in a NULL value violates the NOT-NULL constraint error:

ERROR: Failing row contains (1050, Mesam Mujtaba, 123456789, 1992-06-20, 2020-01-01, 2024-01-01, null).null value in column "gpa" of relation "student" violates not-null constraint
ERROR: null value in column "gpa" of relation "student" violates not-null constraint
SQL State: 23502
Detail: Failing row contains (1050, Mesam Mujtaba, 123456789, 1992-06-20, 2020-01-01, 2024-01-01, null).

Thus, existing NULL values must be updated before altering a column to NOT NULL, regardless of the database system we’re using. Also, attempting to insert a NULL value into a NOT NULL column results in an error.

4. Conclusion