Introduction
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.
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.
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.
Dropping Multiple Columns
In this example, you will drop the Name and Address columns from the table created.
Output-
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.
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.
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.
Using the above statement, you dropped the foreign key in the child table.
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.
Leran SQL and other top programming tools with out PGP Full Stack Web Development Program. This course provides a high-engagement learning experience with real-world applications and is designed for individuals who want to start a new, more fulfilling career.
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!