SQL allows a user to remove one or more columns from a given table in the database if they are no longer needed or become redundant. To do so, the user must have ALTER permission on the object.

Let’s begin with the syntax for using the ALTER TABLE DROP COLUMN statement.


ALTER TABLE table_name

DROP COLUMN column_name;

In the syntax mentioned above, 

First, you define the table name from which you wish to remove or delete the column.

Second, you write the column name you want to delete in the DROP clause.

A user can also drop multiple columns simultaneously from a table. You can do this by listing out the column names that you want to drop, separated by a comma. Refer to the syntax below for a clear understanding.


ALTER TABLE table_name     






Now that you have looked at the syntax, it’s time to see an example for each of the cases above.

Free Course: Business Analysis Basics

Master the Fundamentals of Business AnalysisEnroll Now
Free Course: Business Analysis Basics

Dropping One Column

To drop a column from the table, first, create a table named ‘Students’ with the following statements.



As a result of these statements, you’ll have the table created as given below:


Now, you will have to use the ALTER command to drop a column from the table.


Note that when you use the ALTER command, it gives the output as Table altered. Now, if you check your table, it would look like how it is depicted below.


Now, the AGE column has been dropped out or deleted from the STUDENTS table. Next up, you will look at how to delete multiple columns simultaneously. 

Free Course: Introduction to SQL

Learn MySQL, PostgreSQL and SQL ServerEnroll Now
Free Course: Introduction to SQL

Dropping Multiple Columns

In this example, you will drop the Name and Address columns from the table created.




Now, as you can see, you are only left with the ID column in the ‘Students’ table.

Drop a Column Which is a Foreign Key

If you have created a foreign key in a table, and if you wish to drop that foreign key, you can easily do this using the ALTER command along with the DROP constraint statement. Let’s have a look at the syntax for dropping a foreign key.


ALTER TABLE table_name


Here, table_name represents the table from which the foreign key has to be dropped, and fk_name represents the column name that is the foreign key in the table to be altered.


This is our parent table that has product_id as its primary key.


This statement represents the child table “shop” comprising shop_id as its primary key and fk_shop_product_id as its foreign key that relates to the product_id field in the parent table.


PCP in Business Analysis

In partnership with Purdue UniversityVIEW COURSE
PCP in Business Analysis

Using the above statement, you dropped the foreign key in the child table.

Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Certification Program. Enroll now!

Wrapping up

Here’s a quick summary of the topics covered in this tutorial. First, you learned that SQL provides the user with permission to drop a column that is no longer required using the ALTER command. Next, you saw that there are two ways to drop a column in SQL, you can either drop a single column or multiple columns simultaneously. After that, you covered how you can drop a foreign key in the table using the DROP constraint in the ALTER command.

And now that you have learned about the DROP column concept in SQL, you must be eager to learn and explore more about SQL. Check out Simplilearn’s SQL certification training and enroll right away to master SQL like never before.

Have any questions for us? Leave them in the comments section of this article. Our experts will get back to you on the same, as soon as possible!

About the Author


Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.
  • *According to Simplilearn survey conducted and subject to terms & conditions with Ernst & Young LLP (EY) as Process Advisors