SQL Drop Column

Limitations/Restrictions

If you want to delete a column with a CHECK constraint, you cannot directly remove it using SQL DROP COLUMN. You need first to remove the constraint from the respective column using DROP CONSTRAINT. 

SQL server can not be used to remove the column with PRIMARY or FOREIGN KEY constraint. Table designer is used to remove these. The following image shows the error when you try to drop a column with the constraint.

There are primarily two methods for dropping columns in SQL Server:

• Transact-SQL
• SQL Server Management Studio (SSMS)

Transact SQL

For removing columns from an existing table, use the ALTER TABLE DROP COLUMN command in SQL Server. To accomplish this, we can use the following syntax:

ALTER TABLE table_name     
DROP COLUMN column_name;   

In this syntax, we will first put the name of the table from which we wish to remove columns. Then we'll write the name of the column that we're going to delete.

We can also insert many columns into the existing table at the same time. After the DROP COLUMN clause, we can specify a list of columns separated by commas that we want to remove from a table. The syntax below explains it better:

ALTER TABLE table_name     
DROP COLUMN   
   column_name1,  
   column_name2,  
   ...,  
   column_name_n;   

Drop Column Examples

Let's discuss some examples to understand the SQL DROP column statement better. To try these commands, we need to create a table. The following code creates a table named "StudentData" that contains a student's basic data.

CREATE TABLE StudentData (
	RollNo INT PRIMARY KEY NOT NULL,
	Name VARCHAR(50),
  	Weight INT,
  	Height FLOAT NOT NULL,
  	HomeTown VARCHAR(50),
  	CONSTRAINT ck_positive_height CHECK(Height > 0.0)
);

Now, let's add some data to the table "StudentData" using the insert command.

INSERT INTO StudentData
VALUES
	(1, 'Anaya', 52, 5.4, 'Chandigarh'),
	(2, 'Derek', 69, 5.10, 'Delhi'),
	(3, 'Gaurav', 79, 5.8, 'Delhi'),
	(4, 'Harshita', 55, 5.6, 'Chandigarh');

After executing the above command, the below image shows how the table "StudentData" is stored in the database.

Suppose, now, we don't need the HomeTown column, and we want to drop it from the table. We can remove it using the following statement.

ALTER TABLE StudentData
	DROP COLUMN HomeTown;

After the execution of the above statement, the following image shows the updated table.

If you want to drop the Height column, it might show the error shown below:

Error: The object 'ck_positive_height' is dependent on column 'Height'
									OR
Error: Error in table StudentData after drop column: no such column: Height

To drop the Height column, we first need to drop the constraint from that column. To drop the CHECK constraint, use the following statement:

ALTER TABLE StudentData
	DROP Constraint ck_positive_height;

NOTE: You might not encounter this error while working with My SQL Workbench. The above solution is suggested if you ever face this problem.

Also read, Natural Join in SQL

SQL Server Management Studio (SSMS)

MS SQL Server Management Studio is a workstation or client tool for connecting to and managing your SQL Server. SQL Server Management Studio ( SSMS ) is a client tool for connecting to and working with our SQL Server through a graphical interface rather than command line. Microsoft SQL Server 2005 introduced the management studio, which allows you to deal with SQL Server and Azure SQL databases.

It enables DBAs and database developers to configure, manage, and administer all SQL Server components. Its primary functions include creation of databases and tables, execution of SQL queries for inserting, updating, and removing data, and the creation and management of stored procedures, triggers, views, and cursors. It also allows us to assign permissions ( security ) to databases and their objects.

SSMS additionally contains tools for database deployment, health monitoring, and reporting. SQL Profiler is included, allowing us to examine the performance of our SQL databases. It can also be used to schedule background work.

Best practices

When using SQL DROP COLUMN, you should consider these points:

1. Data backup: Back up your data before removing it. You can back up your data using various tools.
    
2. Dependencies: Review your database before dropping a column to avoid unintentional problems. You can use different database tools to identify any dependencies on the column.
    
3. Consider renaming the column: Renaming a column can help preserve data and reduce the risk of unintended consequences.
    
4. Test thoroughly: Before making any changes to your production database, it's important to test your changes thoroughly in a development or staging environment.
    

Must Read SQL Clauses

Frequently Asked Questions

Q. What is DROP or remove in SQL?

In SQL, the Drop command is part of the Data Definition Language command (DDL). The DROP command deletes the table's definition as well as all of its data, indexes, triggers, constraints, and permission specifications. The Drop command in SQL is used to eliminate an existing table.

Q. Which command is used to DROP a column?

In SQL, we use the ALTER TABLE command to drop a column from a table. As a result, the user must have ALTER TABLE access to the object. We use the ALTER TABLE command with the table name we want to drop a column from.

Q. How to drop a column in SQL?

To drop a column from a table in SQL, we can use SQL DROP Column statement. All the data stored is also permanently deleted from our database. We first write ALTER TABLE Table_name followed by DROP COLUMN Column_name to drop a column.

Q. What are the limitations of dropping a column?

When you drop a column using SQL DROP Column, the data stored is permanently deleted and cannot be restored. It can also cause failure of other columns depending on it. As it affects the database size, it can affect the performance.

Conclusion

We expect this article was insightful and that you learned something new today. In this blog, we learned about a SQL statement, DROP COLUMN, which is used to remove a column from a table in our database. We discussed its syntax and also saw some examples for better understanding.

If you want to learn more about SQL functions, do visit 

• SQL Introduction
• TCL Commands in SQL
• Difference between DROP and TRUNCATE,
• SQL Alter Command,
• Alter Table Command,
• Functions in SQL.

You may refer to our Guided Path on Code Studios for enhancing your skill set on DSA, Competitive Programming, System Design, etc. Check out essential interview questions, practice our available mock tests, look at the interview bundle for interview preparations, and so much more!

Happy Learning, Ninja!