Using ALTER to Drop a Column Only if the Column Exists in SQL

1. Introduction

Dropping a column in SQL can sometimes be necessary when updating the structure of a database. However, attempting to drop a column that doesn’t exist can lead to errors.

In this tutorial, we’ll explore how to use the ALTER TABLE statement to drop a column only if it exists in PostgreSQL, MySQL, and SQL Server. We’ll illustrate this process using examples from the Baeldung University schema.

2. Checking if a Column Exists

Before dropping a column, it’s essential to verify its existence. In essence, we can achieve this by querying the system catalog or information schema, which provides information about columns in tables.

Let’s observe the structure of the table we’ll be working with for this tutorial — the Student table in the University database:

SELECT * FROM Student LIMIT 10;
+------+-----------------+-------------+------------+-----------------+-----------------+------+
| 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 Prabhu |  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 |
| 1101 | Jia Grey        |  1345236267 | 2001-02-05 | 2020-01-15      | 2024-06-15      | 3.98 |
| 1103 | Rose Rit        |  1323612067 | 2001-05-14 | 2020-01-15      | NULL            | 3.57 |
| 1107 | Phellum Luis    |   203678911 | 2001-03-15 | 2020-01-15      | 2024-06-15      | 4.21 |
| 1110 | Albert Decosta  |  2617897011 | 2001-02-21 | 2020-01-15      | 2024-06-15      |    4 |
| 1111 | Vikram Kohli    |  1516578091 | 2001-03-08 | 2020-01-15      | 2024-06-15      | 3.27 |
+------+-----------------+-------------+------------+-----------------+-----------------+------+
10 rows in set (0.00 sec)

We now have insight into what the Student table and its column names look like. Next, let’s explore ways to check if the national_id column exists using queries in PostgreSQL, MySQL, and SQL Server.

2.1. In PostgreSQL

In PostgreSQL, we can query the information_schema.columns view to check if a column exists:

SELECT column_name 
FROM information_schema.columns 
WHERE table_schema = 'public' AND table_name = 'student' AND column_name = 'national_id';

 column_name 
-------------
 national_id
(1 row)

As shown above, the query searches the information_schema.columns view for the national_id column in the Student table within the public schema. In this case, the result shows the column name, national_id, confirming that it exists. Let’s proceed to examine how this is done in MySQL.

2.2. In MySQL

In MySQL, we use a similar approach to check for the existence of a column with a little tweak:

SELECT column_name 
FROM information_schema.columns 
WHERE table_schema = 'University' AND table_name = 'Student' AND column_name = 'national_id';
+-------------+
| COLUMN_NAME |
+-------------+
| national_id |
+-------------+
1 row in set (0.00 sec)

The query operates by searching the information_schema.columns view, which contains metadata about all columns in the database. As shown above, the result shows that the national_id column does indeed exist in the Student table.

2.3. In SQL Server

Similarly, in SQL Server, we’ll query the INFORMATION_SCHEMA.COLUMNS view:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'student' AND COLUMN_NAME = 'national_id';

COLUMN_NAME
-------------
national_id

As shown above, the national_id column exists and is displayed in the output. Conversely, we’ll proceed to examine what the result looks like when the column doesn’t exist.

2.4. Result if the Column Doesn’t Exist

Here, we’ll examine the output displayed when the column we’re trying to query doesn’t exist. So, let’s proceed to check for a column name that doesn’t exist in the Student table within the MySQL database. For example, we’ll check for baeldung_id:

SELECT column_name 
FROM information_schema.columns 
WHERE table_schema = 'University' AND table_name = 'Student' AND column_name = 'national_id';

Empty set (0.00 sec)

The result shows that the column isn’t present by outputting Empty set. Consequently, during automation, such an output could break the application or stop the program from running. So, it’s a good practice to have a condition that handles such occurrences.

3. Dropping a Column Conditionally Using ALTER TABLE

To drop a column only if it exists, we can use stored procedures or procedural language constructs. This approach ensures that the ALTER TABLE statement is executed only when the column is present. So, let’s explore how to do this in PostgreSQL, MySQL, and SQL Server.

3.1. In PostgreSQL

In PostgreSQL, we can use PL/pgSQL to drop the column conditionally:

DO $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'public'
        AND table_name = 'student'
        AND column_name = 'national_id'
    ) THEN
        EXECUTE 'ALTER TABLE Student DROP COLUMN national_id';
    END IF;
END $$;

SELECT * FROM Student LIMIT 3;

  id  |    name     | birth_date | enrollment_date | graduation_date | gpa 
------+-------------+------------+-----------------+-----------------+-----
 1001 | John Liu    | 2001-04-05 | 2020-01-15      | 2024-06-15      |   4
 1003 | Rita Ora    | 2001-01-14 | 2020-01-15      | 2024-06-15      | 4.2
 1007 | Philip Lose | 2001-06-15 | 2020-01-15      | 2024-06-15      | 3.8
(3 rows)

The result above confirms the successful dropping of the national_id column because it existed within the specified table.

However, if we run the query again, the ALTER TABLE statement in the DO block of the code won’t execute since the national_id column no longer exists.

3.2. In MySQL

In MySQL, we can check for the column’s existence within a stored procedure and then execute the ALTER TABLE statement if we find the column:

DELIMITER //

CREATE PROCEDURE DropColumnIfExists()
BEGIN
    DECLARE column_exists INT DEFAULT 0;

    -- Check if the column exists
    SELECT COUNT(*) INTO column_exists 
    FROM information_schema.columns 
    WHERE table_schema = 'University' 
      AND table_name = 'Student' 
      AND column_name = 'national_id';

    -- If the column exists, drop it
    IF column_exists > 0 THEN
        ALTER TABLE Student DROP COLUMN national_id;
    END IF;
END //

DELIMITER ;
CALL DropColumnIfExists();
Query OK, 0 rows affected (0.01 sec)

SELECT * FROM Student;
+------+-----------------+------------+-----------------+-----------------+------+
| id   | name            | birth_date | enrollment_date | graduation_date | gpa  |
+------+-----------------+------------+-----------------+-----------------+------+
| 1001 | John Liu        | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |
| 1003 | Rita Ora        | 2001-01-14 | 2020-01-15      | 2024-06-15      |  4.2 |
| 1007 | Philip Lose     | 2001-06-15 | 2020-01-15      | 2024-06-15      |  3.8 |
| 1010 | Samantha Prabhu | 2001-03-21 | 2020-01-15      | 2024-06-15      |  4.9 |
| 1011 | Vikas Jain      | 2001-07-18 | 2020-01-15      | NULL            |  3.3 |
...

Here, we call the stored procedure, named DropColumnIfExists. Then, the SELECT query confirms that we dropped the national_id column.

3.3. In SQL Server

In SQL Server, we can use a stored procedure to achieve the same result as the other databases. Similarly, we create the stored procedure and then execute it. If the condition is met, the procedure triggers dropping the specified column:

CREATE PROCEDURE DropColumnIfExist
AS
BEGIN
    IF EXISTS (
        SELECT 1
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = 'dbo'
        AND TABLE_NAME = 'Student'
        AND COLUMN_NAME = 'national_id'
    )
    BEGIN
        EXEC('ALTER TABLE dbo.Student DROP COLUMN national_id');
    END
END;


EXEC DropColumnIfExist;

Commands completed successfully.
Completion time: 2024-08-07T00:33:18.8347394+01:00

SELECT * FROM Student;
+------+-----------------+------------+-----------------+-----------------+------+
| id   | name            | birth_date | enrollment_date | graduation_date | gpa  |
+------+-----------------+------------+-----------------+-----------------+------+
| 1001 | John Liu        | 2001-04-05 | 2020-01-15      | 2024-06-15      |    4 |
| 1003 | Rita Ora        | 2001-01-14 | 2020-01-15      | 2024-06-15      |  4.2 |
| 1007 | Philip Lose     | 2001-06-15 | 2020-01-15      | 2024-06-15      |  3.8 |
| 1010 | Samantha Prabhu | 2001-03-21 | 2020-01-15      | 2024-06-15      |  4.9 |
| 1011 | Vikas Jain      | 2001-07-18 | 2020-01-15      | NULL            |  3.3 |
...

The result confirms that we dropped the column.

4. Conclusion

In this article, we explored different methods for safely and conditionally dropping a column from a table in PostgreSQL, MySQL, and SQL Server. In essence, this approach helps avoid errors and ensures smooth database operations, catering to different scenarios and use cases.