Drop a column in SQL

Drop a Column in SQL

In many cases, you need to delete or drop a column from a SQL table, Those column which is not in use or you think that should be deleted . To drop a column in SQL table, you need to use ALTER TABLE statement  with DROP COLUMN clause and below are the following syntax:

Syntax: To drop single column from SQL Table

ALTER TABLE 
         Table_name
DROP COLUMN 
         Column;

In Above syntax

• ALTER TABLE: Specify the name of the table from which you would like to delete a column.
• DROP COLUMN: Specify the name of the column that you would like to delete.

Note: The columns which you would like to delete and those columns have Constraint, Then first you need to delete those constraints before deleting the columns.

And SQL does not allow you to delete a column that has a PRIMARY KEY or a FOREIGN KEY constraint.

Syntax: To drop multiple columns from SQL table

ALTER TABLE 
           Table_name 
DROP COLUMN 
          column_1, column_2,...;

Above syntax, you need to specify all columns that you would like to drop as a list of comma separated columns in the DROP COLUMN clause.

Below are 5 examples will be more useful to understand with different situation of query

Example #1: Drop a single column

To drop a column is the simple query which you need to use with single Column name of that table from which you want to drop the column.

In below example we used table Customers_Tbl and trying to delete or drop a column name Street_Details

ALTER TABLE 
           Customers_Tbl
DROP COLUMN 
           Street_Details;

Example #2: Drop Multiple columns

If you would like to drop a multiple columns from a SQL tables, you need to use multiple Columns name separated by comma as shown below example.

In below example we used table Customers_Tbl and trying to delete or drop more then one column like Street_Details, Mobile_Details, PIN_Details

ALTER TABLE 
           Customers_Tbl 
DROP COLUMN
           Street_Details,  Mobile_Details,  PIN_Details

Note: Once you will drop the columns you can do the undo.

Example #3: Drop a column which have PRIMARY KEY constraint

This example is to show, what kind of error you will received once you will DROP Column which have constraint.

Let create a table with PRIMARY Key constraint using below CREATE Table command

CREATE TABLE PRODUCT_TEST (
            product_id int,
            Product_info int,
            discount DEC(10,2) NOT NULL,
            PRIMARY KEY(Product_info)
);

Now once you will try to DROP Column  name = Product_info from above new table it will show the error because this column have PRIMARY KEY constraint.

ALTER TABLE 
           PRODUCT_TEST
DROP COLUMN 
           Product_info

Error:

So if you would like to DROP CONSTRAINT the use below syntax to drop constraint column

ALTER TABLE 
           Table_name
DROP CONSTRAINT 
           Column_name;

Example #4: Delete a column manually (without command)

This is the quick method to drop a column from a table by using SQL management studio. This way of deleting column is only use when you are doing initial designing, else when you are running query and doing analysis using SQL commands then always use SQL Alter and Drop command.

In SQL management studio, click on Database > then click on table > click on Column folder

Then right click on column which you want to delete and drop. As shown below image:

Conclusion

This tutorial helpful to understand how you can Drop or delete a column in SQL with command and without command, both are useful as you need sometime to delete a column faster during designing of tables in database, Else during analysis and scripting of SQL, you should use Drop command.