Let’s learn what happens when you delete a column in SQL and how to do it in the most popular DBMS technologies.
Tools used in this tutorial
DbVisualizer - top rated database management tool and SQL client.
Deleting a column in SQL is one of the most common operations when dealing with a database. For this reason, knowing how to properly delete a column in SQL is critical. Without the right procedures and precautions, you could run into data integrity and data loss issues.
In this guide, you will dig into the process of deleting columns in SQL databases, seeing the essential concepts and syntax. We will provide you with all the information you need to perform column deletion operations smoothly and effectively.
Let’s dive in!
What Does it Mean to Delete a Column in SQL?
In SQL, deleting a column refers to the process of permanently removing a specific column from a table. All data associated with the column will be removed from the disk. Similarly, the column’s metadata will be removed from the table's schema. When you delete a column, you essentially eliminate its existence within the table structure. The column will be no longer available for queries and its data will be lost.
The SQL command to delete an existing column from a table is DROP COLUMN. This is part of the SQL DDL (Data Definition Language), which contains statements to define, modify, or delete the structure of database objects like tables, indexes, and constraints. Since DROP COLUMN involves modifying the structure of an existing table, it must be used in an ALTER TABLE query.
Here is the generic SQL syntax to delete a column:
1 ALTER TABLE <table_name>
2 DROP COLUMN <column_name>;
Replace table_name with the name of the table you want to remove the column_name column from. Later in this article, you will see how to use this query in the most popular relational databases, such as MySQL, PostgreSQL, Microsoft SQL Server, and Oracle Database.
Consequences of Deleting a Column in SQL
Here are the key aspects to consider when removing a column from a table in SQL:
Data removal: All data currently stored in the column to be deleted, in each row of the table, will be permanently deleted. This includes all values, records, or information previously stored in that column.
Metadata removal: The column's metadata, such as its name, data type, default values, constraints, and indexes will be removed from the table's schema definition. This modification impacts the structure of the table, changing the number of its columns.
Query impact: Any queries, statements, or SQL procedures that reference the deleted column must be updated accordingly. In detail, you need to exclude the column from the query to avoid errors or unexpected results.
Data integrity: If the column is part of foreign keys or other constraints, deleting it involves data integrity considerations. In some DBMSs, you may need to remove these constraints or manage them accordingly before you can delete the column.
Performance improvements: Deleting a column in SQL can lead to improved query performance, especially if the column is not frequently used or contains redundant information. This also results in reduced storage.
Keep in mind that deleting a column is an operation that cannot be undone. This means that you must be careful with it, especially in production environments. Before proceeding with the irreversible operation, it is critical to plan properly, back up the database, and consider the potential impact on existing applications and queries.
How to Delete a Column in SQL
In this section, you will see how to delete one or more columns in the most popular relation databases.
Deleting a column in MySQL
The syntax to delete a single column in MySQL is:
1 ALTER TABLE <table_name>
2 DROP [COLUMN] <column_name>;
Note that the COLUMN keyword is optional.
For example, you can remove the email column from the users table with:
1 ALTER TABLE users
2 DROP COLUMN email;
The syntax to delete multiple columns with a single query in MySQL is:
1 ALTER TABLE <table_name>
2 DROP [COLUMN] <column1>,
3 DROP [COLUMN] <column2>,
4 ...,
5 DROP [COLUMN] <columnN>;
Again, COLUMN is optional.
So, the query below removes the phone and address columns from the contacts table:
1 ALTER TABLE contacts
2 DROP phone,
3 DROP address;
Deleting a column in PostgreSQL
The syntax to delete a single column in PostgreSQL is:
1 ALTER TABLE <table_name>
2 DROP [COLUMN] <column_name>;
The COLUMN keyword is optional.
For example, you can remove the address column from the users table with:
1 ALTER TABLE users
2 DROP COLUMN address;
The syntax to delete more than one column with a single PostgreSQL query is:
1 ALTER TABLE <table_name>
2 DROP [COLUMN] <column1>,
3 DROP [COLUMN] <column2>,
4 ...,
5 DROP [COLUMN] <columnN>;
Again, COLUMN is optional.
So, the following query deletes the price and quantity columns from the orders table:
1 ALTER TABLE orders
2 DROP COLUMN price,
3 DROP COLUMN quantity;
Deleting a column in Microsoft SQL Server
The syntax to delete a single column in Microsoft SQL Server is:
1 ALTER TABLE <table_name>
2 DROP COLUMN <column_name>;
For example, you can drop the release_date column from the games table with:
1 ALTER TABLE games
2 DROP COLUMN release_date;
The syntax to delete multiple columns with a single T-SQL query is:
1 ALTER TABLE <table_name>
2 DROP COLUMN <column1>, <column2>, ..., <columnN>;
Thus, this query removes the time and efforts columns from the projects table:
1 ALTER TABLE projects
2 DROP COLUMN time, effort;
Deleting a column in Oracle Database
The syntax to delete a single column in Oracle Database is:
1 ALTER TABLE <table_name>
2 DROP COLUMN <column_name>;
For example, you can remove the price column from the products table with:
1 ALTER TABLE products
2 DROP COLUMN price;
The syntax to delete multiple columns with a single T-SQL query is:
1 ALTER TABLE <table_name>
2 DROP (<column1>, <column2>, ..., <columnN>);
Then, the query below deletes the address and age from the users table:
1 ALTER TABLE projects
2 DROP (address, age);
Note that Oracle also allows you to logically delete a column with the SET UNUSED command.
Column Removal in SQL: Complete Example
When deleting a column in SQL, it is critical to use the right tool. You cannot just run a DROP COLUMN statement from the command line lightheartedly. The reason is that you need to understand what happened as a result of the query, and the best way to do this is with a database client. This tool allows you to visually explore tables and their data, helping you monitor your databases and fix issues as they arise. That is exactly what a top-notch database client like DbVisualizer offers!
Download DbVisualizer for free and follow the wizard to set up a database connection. Suppose you have an account MySQL database. Navigate to the users table and open the “Columns” dropdown.
Right-click on “Columns” and select the “Open in New Tab” option:
The "Columns" tab in DbVisualizer.
Here, you can visually explore all columns a table consists of.
Now, open the SQL commander and launch the query below to remove the is_active column:
1 ALTER TABLE users
2 DROP COLUMN is_active;
Launching the DROP COLUMN operation in DbVisualizer.
Right-click on “Columns” and select “Refresh Objects Tree:”
This will refresh the column list, and you should notice that is_active is now gone:
The "is_active" column is no longer in the list.
Et voilà! You just deleted a column and see the results of the operation with a few simple steps!
Conclusion
In this article, you saw what it means to delete a column in SQL, what happens when you do it, and how to do it. Thanks to what you learned here, you now know how to handle single and multiple column deletion in MySQL, PostgreSQL, Microsoft SQL Server, and Oracle Database.
Since deleting columns in SQL alters the table schema, monitoring the consequences in a database client makes everything easier. This is where DbVisualizer comes in! In addition to supporting visual querying and data exploration in dozens of databases, this tool also offers advanced query optimization capabilities, and ER schema exploration. Download DbVisualizer for free now!
FAQ
What is the difference between dropping a column and deleting a column in SQL?
In SQL, dropping a column and deleting a column mean the same things. The two expressions refer to the process of permanently removing a column from a table, including its data and metadata.
What is the difference between deleting a column logically and deleting a column physically?
The distinction between logical and physical deletion in a relational database lies in how data removal is handled. Logical deletion involves marking the data as inactive without physically removing the column. On the other hand, physical deletion involves permanently removing the data from the database, making it unrecoverable.
Is it possible to delete multiple columns in a single SQL statement?
In most SQL dialects, you can remove more than one column with a single ALTER TABLE query by using a special syntax. In such a scenario, you have to specify all the columns you want to delete in a list or use the DROP COLUMN command several times.
What happens when you try to delete a column that does not exist?
When attempting to delete a column that does not exist in the table, the database will raise an error. For example, MySQL returns the following error message:
Can't DROP ''; check that column/key exists
Thus, ensure that the columns you want to delete exist in the table before launching the command.
What are the best practices to consider when deleting a column from a production database?
Best practices help minimize data loss and disruption when removing a column from a production database. These include:
Generating database backups.
Evaluating relationships between tables, such as foreign keys or constraints.
Notifying stakeholders and affected teams before proceeding.
Performing comprehensive testing in a non-production environment before applying changes to the live database.
Devising a rollback plan in case of unexpected issues.
About the author
Antonello Zanini is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.