intro
Let’s see everything you need to know about the ALTER TABLE DROP COLUMN query to remove a column from a table in SQL.
Database schemas evolve to accommodate new business needs and data representation requirements. As a result, some columns in a table may become obsolete and need to be removed to save space. This is where the SQL ALTER TABLE DROP COLUMN statement comes in!
In this guide, you'll learn what the DROP COLUMN ALTER TABLE statement is and how to use it in MySQL, PostgreSQL, SQL Server, and Oracle.
Let’s dive in!
What Is the ALTER TABLE DROP COLUMN Statement?
The SQL ALTER TABLE DROP COLUMN statement is used to remove columns from an existing table in a database. This operation modifies the table structure by permanently deleting the specified columns.
In some databases, the DROP COLUMN ALTER TABLE command only affects the table metadata. In others, it also immediately removes the data stored in the column to reclaim disk space. Learn more about the process of deleting a column in SQL.
SQL ALTER TABLE DROP COLUMN Syntax and Usage
This is the syntax of SQL ALTER TABLE DROP COLUMN query according to the ANSI/ISO standard:
1
ALTER TABLE table_name
2
DROP [COLUMN] column_name [CASCADE | RESTRICT];
Where:
At the same time, all major SQL dialects come with some variations and extensions of that syntax. Time to explore how to use the ALTER TABLE DROP COLUMN action in:
MySQL
Below is the syntax of the MySQL ALTER TABLE query for dropping a column:
1
ALTER TABLE table_name
2
DROP [COLUMN] column_name;
As you can see, MySQL does not support the CASCADE and RESTRICT options.
Example:
1
ALTER TABLE employees
2
DROP age;
The above query removes the age column from the employees table.
MySQL also supports an extension to the standard syntax to drop multiple columns in a single ALTER TABLE statement:
1
ALTER TABLE table_name
2
DROP [COLUMN] column_1, [...], DROP [COLUMN] column_n;
Example:
1
ALTER TABLE employees
2
DROP middle_name, DROP salary;
This deletes the middle_name and salary columns from the employees table.
Notes:
PostgreSQL
The ALTER TABLE syntax for dropping columns in PostgreSQL is:
1
ALTER TABLE table_name
2
DROP [COLUMN] [IF EXISTS] column_name [RESTRICT | CASCADE];
IF EXISTS is an optional Postgres extension. If it is specified and the column does not exist, no error is thrown.
Example:
1
ALTER TABLE users
2
DROP COLUMN IF EXISTS points;
This DROP COLUMN ALTER TABLE query removes the points column from the users table, if the column exists.
The syntax for dropping multiple columns in PostgreSQL is:
1
ALTER TABLE table_name
2
DROP [COLUMN] [IF EXISTS] column_1 [RESTRICT | CASCADE],
3
[...],
4
DROP [COLUMN] [IF EXISTS] column_n [RESTRICT | CASCADE];
Example:
1
ALTER TABLE users
2
DROP COLUMN address,
3
DROP COLUMN IF EXISTS age;
The above query deletes the address column and removes the age column—if it exists—from the users table.
Notes:
SQL Server
This is how to use ALTER TABLE DROP COLUMN in SQL Server:
1
ALTER TABLE table_name
2
DROP COLUMN [IF EXISTS] column_name;
In this case, COLUMN is required and there is no support for the RESTRICT and CASCADE options.
Example:
1
ALTER TABLE Products
2
DROP COLUMN Description;
This removes the Description column from the Products table.
Compared to MySQL and PostgreSQL, the syntax for removing multiple columns with a single ALTER TABLE query is a bit different in SQL Server:
1
ALTER TABLE table_name
2
DROP COLUMN [IF EXISTS] column_1, [...], column_n;
Example:
1
ALTER TABLE Products
2
DROP COLUMN Discount, Category;
The SQL Server query drops the Discount and Category columns from Products.
Notes:
Oracle
Here is the ALTER TABLE DROP COLUMN syntax in Oracle:
1
ALTER TABLE table_name
2
DROP [COLUMN] column_name;
Example:
1
ALTER TABLE events
2
DROP COLUMN date;
This command removes the date column from the events table.
Oracle allows you to drop multiple columns in a single ALTER TABLE statement by using the following syntax:
1
ALTER TABLE table_name
2
DROP (column_1, column_2, [...], column_n);
Example:
1
ALTER TABLE events
2
DROP (description, type);
This drops the description and type columns from the events table.
Notes:
Drop a Column in a Table Using a Database Client
As you just learned, each major database technology has its variations of the standard syntax for dropping a column. Remembering the specific DROP COLUMN ALTER TABLE syntax across different databases can be challenging, and mistakes in writing the query are common.
How can you avoid that issue? With DbVisualizer, a database client that allows you to visually remove columns from a table. Regardless of the underlying database, the procedure remains the same and involves only a few clicks!
To drop a column in DbVisualizer:
Connect to your database server, expand the "Databases" dropdown menu on the left, select the specific database to explore, and expand the "Tables" dropdown: In this case, the database is called
games.
Note the list of databases and tables
Right-click on the table to remove the column in, and select the "Alter table…" option. In this example, the selected table is
users.
Selecting the Alter Table option
In the "Alter Table" dialog, select the column you want to remove, and click the "—" button on the right to mark it as to be dropped. The columns to drop are
created_byandcreated_at.
Note the queries in the SQL Preview section
In particular, as you can see, it will execute the following queries:
1
ALTER TABLE
2
`games`.`users` DROP COLUMN `created_by`;
3
ALTER TABLE
4
`games`.`users` DROP COLUMN `created_at`;
Et voilà! You just learned a more intuitive way to remove columns in SQL.
Conclusion
In this guide, you learned how to use ALTER TABLE to drop columns in SQL. You explored what this statement offers and how to use it in MySQL, PostgreSQL, SQL Server, and Oracle.
As discussed here, each SQL dialect has its own extensions and syntax variations for the DROP COLUMN ALTER TABLE command. The easiest way to delete a column in SQL is through a full-featured database client that allows you to do it visually with just a few clicks. This is exactly what DbVisualizer offers!
As a powerful database client, DbVisualizer supports many DBMS technologies, can generate ERD-like schemas with a single click, and also provides advanced query optimization features. Try DbVisualizer for free today!
FAQ
Is it possible to drop multiple columns with a single DROP COLUMN ALTER TABLE query?
The official SQL ANSI/ISO standard does not specify a way to drop multiple columns in a single ALTER TABLE query. However, most SQL dialects support this feature natively with their own custom syntax.
What are the permissions required to use ALTER TABLE DROP COLUMN in SQL Server?
To use ALTER TABLE DROP COLUMN in SQL Server, you need the ALTER permission on the table, which enables you to make changes to the table structure.
What is the opposite of DROP COLUMN in ALTER TABLE?
The SQL ALTER TABLE DROP COLUMN statement removes a column from a table. That means the opposite operation is the ALTER TABLE ADD COLUMN statement, which adds a column to a table. Discover more in our guide on the ALTER TABLE ADD COLUMN statement.
What are the most common conditions why you cannot drop a column in SQL?
Each database has its own set of rules that columns must meet in order to be dropped. These are the conditions that may prevent an SQL column from being dropped:
Is it possible to use ALTER TABLE to DROP COLUMN without losing data if the column is not empty?
No, it is not possible to use ALTER TABLE to DROP COLUMN without losing data if the column is not empty. Dropping a column permanently deletes both the column and all data contained in it. Once the column is dropped, the data cannot be recovered unless you have a backup. If you are a MySQL user, find out more in our mysqldump guide.
