Understanding DROP Column In SQL

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. In this article, you will learn about the following topics in detail:

  1. Syntax of DROP column in SQL
  2. How to Drop one column in SQL?
  3. How to drop multiple columns in SQL?
  4. Dropping a column that is a Foreign Key 
  5. Conclusion

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

Syntax

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.

Syntax

ALTER TABLE table_name     

DROP COLUMN   

    column_name1,  

    column_name2,  

    ...,  

    column_name_n;  

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

FREE Course: Introduction to Data Analytics

Mastery to Data Analytics Basics is a Click Away!Start Learning
FREE Course: Introduction to Data Analytics

Dropping One Column

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

DropInSQL_1

DropInSQL_2

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

DropInSQL_3.

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

DropInSQL_4

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.

DropInSQL_5.

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. 

Dropping Multiple Columns

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

DropInSQL_6.

Output-

DropInSQL_7

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

Post Graduate Program in Business Analysis

In partnership with Purdue UniversityVIEW COURSE
Post Graduate Program in Business Analysis

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.

Syntax:

ALTER TABLE table_name

DROP CONSTRAINT fk_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.

DropInSQL_8

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

DropInSQL_9

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.

DropInSQL_10

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

SimplilearnSimplilearn

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.