SQL DROP COLUMN for Beginners

Posted on by Ian

In SQL, if you want to remove a column from a table, you need to use the ALTER TABLE statement with the DROP COLUMN clause.

That removes the column and all its data.

Syntax

The syntax goes like this:

ALTER TABLE table_name 
DROP COLUMN column_name;

Some RDBMSs accept an optional IF EXISTS argument which means that it won’t return an error if the column doesn’t exist.

Some RDBMSs also accept optional CASCADE and RESTRICT arguments, which specify what to do if the column has any dependencies. See below for more on this.

Example

Here’s an example to demonstrate.

ALTER TABLE Products 
DROP COLUMN ProductDescription;

This removes the ProductDescription column from the Products table.

The IF EXISTS Argument

Depending on your RDBMS, you may be able to use the IF EXISTS argument, which conditionally drops the column only if it already exists.

The benefit of doing this is that you won’t get an error if the column doesn’t exist.

Example:

ALTER TABLE Products
DROP COLUMN IF EXISTS ProductDescription;

Restrict the Change

Depending on your RDBMS, you may be able to use the CASCADE and RESTRICT arguments to specify what to do if the column has any dependencies, such as foreign keys or views.

RESTRICT is typically the default behavior, so if you don’t specify any of these arguments, the DBMS will refuse to drop the column if there are any dependent objects.

Example:

ALTER TABLE Products
DROP COLUMN ProductDescription RESTRICT;

When using the above statement, if the column has any dependencies, the drop operation will fail, and you’ll get an error.

Here’s the error I get in PostgreSQL when trying to drop a table that’s referenced by a view:

cannot drop column productdescription of table products because other objects depend on it

Cascade the Change

Using the CASCADE option will cause any dependent objects to be dropped.

Here’s what happens if I change the previous example to CASCADE:

ALTER TABLE Products
DROP COLUMN ProductDescription CASCADE;

Result:

NOTICE: drop cascades to view vproducts
Commands completed successfully

In this case, the column was dropped and I got a message explaining that the view called vproducts was also dropped.

CASCADE and RESTRICT are supported in PostgreSQL, but not in SQL Server or MySQL. Both keywords are able to be used in MariaDB but they don’t have any effect.

Oracle accepts a CASCADE CONSTRAINTS clause which drops all foreign key constraints that refer to the primary and unique keys defined on the dropped columns as well as all multicolumn constraints defined on the dropped columns.

Drop Multiple Columns

Some RDBMs allow you to drop multiple columns within a single ALTER TABLE statement. The syntax varies between RDBMS.

In SQL Server, you can simply list each column, separated by a comma:

ALTER TABLE t1 
DROP COLUMN c1, c2;

In other RDBMSs (such as MySQL and PostgreSQL), you would need to rewrite DROP COLUMN for each column:

ALTER TABLE t1 
DROP COLUMN c1, DROP COLUMN c2;

Note that this syntax is an extension to SQL, and it doesn’t conform to the SQL standard of only having one DROP clause per ALTER TABLE statement.

Drop the Last Column

Some RDBMs allow you to drop the last column in the table, therefore leaving an empty table with no columns. This is an extension of the SQL standard (which doesn’t allow zero-column tables).

For example, in PostgreSQL, I used the following statement to drop the last remaining column in the table

ALTER TABLE t1 
DROP COLUMN c3;

Result:

Commands completed successfully

But in SQL Server, if I do the same:

ALTER TABLE t1 
DROP COLUMN c1, c2, c3;

Result:

Msg 4923, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN failed because 'c3' is the only data column in table 't1'. A table must have at least one data column.

Note that despite the wording of this error message, c3 wasn’t the only remaining column. There were actually three columns. However, c3 would have been the last one remaining if the other two had been dropped. In this case, none of the three columns were actually dropped.

Either way, even if I did drop the other two, SQL Server would refuse to drop the last one.

MySQL also refuses to drop the last column in a table.

If your intention is to drop the table, use DROP TABLE.

Restrictions by RDBMS

Although the basic DROP COLUMN syntax is pretty similar across most of the major RDBMSs, each RDBMS tends to have its own restrictions for when a column will or won’t be dropped.

Here are some of the restrictions from some of the major RDBMSs.

SQL Server

A column can’t be dropped when it’s:

  • Used in an index, whether as a key column or as an INCLUDE
  • Used in a CHECK, FOREIGN KEY, UNIQUE, or PRIMARY KEY constraint.
  • Associated with a default that’s defined with the DEFAULT keyword, or bound to a default object.
  • Bound to a rule.

Source for SQL Server: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql

MySQL

If columns are dropped from a table, the columns are also removed from any index of which they are a part. If all columns that make up an index are dropped, the index is dropped as well.

Source for MySQL: https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

PostgreSQL

Indexes and table constraints involving the column will be automatically dropped as well.

Multivariate statistics referencing the dropped column will also be removed if the removal of the column would cause the statistics to contain data for only a single column. 

You will need to use CASCADE if anything outside the table depends on the column, for example, foreign keys or views.

Source for PostgreSQL: https://www.postgresql.org/docs/current/sql-altertable.html

SQLite

SQLite doesn’t support the DROP COLUMN syntax. You can’t drop a column in SQLite.

If you need to drop a column in SQLite, it’s recommended that you follow the 12 step process recommended in the SQLite documentation.

Source for SQLite: https://sqlite.org/lang_altertable.html#otheralter

MariaDB

If the column is part of any index, the column will be dropped from them, except if you add a new column with identical name at the same time. The index will be dropped if all columns from the index were dropped. If the column was used in a view or trigger, you will get an error next time the view or trigger is accessed.

From MariaDB 10.2.8, dropping a column that is part of a multi-column UNIQUE constraint is not permitted.

MariaDB accepts RESTRICT and CASCADE in order to make porting from other database systems easier, but in MariaDB, they do nothing.

MariaDB 10.4.0 supports instant DROP COLUMN. DROP COLUMN of an indexed column would imply DROP INDEX (and in the case of a non-UNIQUE multi-column index, possibly ADD INDEX). These will not be allowed with ALGORITHM=INSTANT, but unlike before, they can be allowed with ALGORITHM=NOCOPY.

Source for MariaDB: https://mariadb.com/kb/en/alter-table/#drop-column