Understanding SQL DELETE Column from Table

Adding and removing columns are among the most common tasks when you manage tables in databases. And it might seem strange, but many users face issues. To learn or recall how to delete column in SQL correctly, they often have to refer to the guidelines and conduct additional searches for any specific scenario. That’s why we’ve prepared this article to highlight all essential cases of the drop column SQL server statement usage.

You can delete a column from the table in different ways. The most popular of them are:

1. Table-designer of SQL Server Management studio.
2. ALTER TABLE statement.

In addition to the above methods, we are going to explore the following drop column SQL use cases:

1. Drop the column that has a foreign key constraint.
2. Drop the column that has a unique constraint.

Basic Inputs

I have created a database named StudentDB that I am going to use for demonstration. This database has the tables named tblStudent, tblSchool, and tblCity.

The code for creating the database is as follows:

CREATE DATABASE [StudentDB] 
go 

The below code creates the tables:

USE [StudentDB] 
go 
CREATE TABLE [tblstudent] 
  ( 
     [id]                [INT] IDENTITY(1, 1) NOT NULL, 
     [student_code]      [VARCHAR](20) NOT NULL, 
     [student_firstname] [VARCHAR](250) NOT NULL, 
     [student_lastname]  [VARCHAR](10) NOT NULL, 
     [address]           VARCHAR(max), 
     [city_code]         [VARCHAR](20) NOT NULL, 
     [school_code]       [VARCHAR](20), 
     [admissiondate]     DATETIME DEFAULT Getdate(), 
     CONSTRAINT pk_id PRIMARY KEY (id) 
  ) 
go 

CREATE TABLE [tblschool] 
  ( 
     [id]          [INT] IDENTITY(1, 1) NOT NULL, 
     [school_code] [VARCHAR](20) NOT NULL, 
     [school_name] [VARCHAR](250) NOT NULL, 
     [address]     VARCHAR(max), 
     [city_code]   [VARCHAR](20) NOT NULL, 
     CONSTRAINT pk_school_code PRIMARY KEY(school_code) 
  ) 
go 

CREATE TABLE [tblcity] 
  ( 
     [id]        [INT] IDENTITY(1, 1) NOT NULL, 
     [city_code] [VARCHAR](20) NOT NULL, 
     [city_name] [VARCHAR](250) NOT NULL, 
     CONSTRAINT pk_city_code PRIMARY KEY(city_code) 
  ) 
go 

The code that adds the foreign key into the tblStudent table is:

ALTER TABLE tblstudent 
ADD CONSTRAINT fk_school_code FOREIGN KEY (school_code) REFERENCES tblschool(school_code)
ALTER TABLE tblstudent ADD CONSTRAINT fk_tblStudent_city_code FOREIGN KEY (city_code) REFERENCES tblcity(city_code)

The code adding the foreign key into the tblSchool table is:

ALTER TABLE tblschool ADD CONSTRAINT fk_tblCity_city_code FOREIGN KEY (city_code) REFERENCES tblcity(city_code)

Finally, the following code adds a Unique key constraint into the tblStudent table:

ALTER TABLE tblstudent 
ADD CONSTRAINT unq_tblstudent_student_code UNIQUE (student_code) 

Have a look at the E-R diagram of our StudentDB database:

Now, let us consider the use cases.

Drop a Column Using SQL Server Management Studio

One of the convenient delete column SQL options is using the table designer of the SQL Server Management Studio.

In our example, we are going to drop the admissiondate column from the tblStudent table.

Connect to the SQL Server instance > Expand databases > Expand StudentDB > Expand Tables > Right-click on Design.

In the table designer, right-click on the column that you want to drop. In our case, it is the admissiondate column.

Select Delete Column.

The column will disappear from the list. Click Save.

Once the column is dropped, we can verify the table structure. Run the following query:

SELECT table_catalog, 
       table_schema, 
       table_name, 
       column_name, 
       data_type, 
       character_maximum_length 
FROM   information_schema.columns 
WHERE  table_name = 'tblStudent' 

Output

As you can see, the query does not return any details of the admissiondate column. It indicates that the column has been dropped successfully.

Alternatively, you expand Tables and then expand Columns. In our case, we are expanding tblStudent > Columns section:

You can see that the column has been dropped from the table. Use this SQL remove column method at your convenience!

Now, let explore the next use case that is dropping the column by the ALTER TABLE statement.

Delete a Column From an Existing Table Using the ALTER TABLE Statement

The syntax for the ALTER TABLE drop column statement is as follows:

ALTER TABLE tbl_name DROP COLUMN col_name

Where:

• tbl_name is the table name.
• col_name is the name of the column you want to drop.

Assume that we want to drop the address column from the tblStudent table. To do that, we run the below query:

ALTER TABLE tblstudent DROP COLUMN [address]

Once the column is dropped, we execute the following query to view the updated table structure:

SELECT table_catalog, 
       table_schema, 
       table_name, 
       column_name, 
       data_type, 
       character_maximum_length 
FROM   information_schema.columns 
WHERE  table_name = 'tblStudent' 

Output

Thus, the address column has been dropped from the tblStudent table.

Delete the Column in SQL that Has a Foreign Key

Now, let us review a specific case of using T SQL drop column command for the column having a foreign key.

In our demo database, we have two foreign keys named fk_school_code and fk_tblStudent_city_code on the tblStudent table. The fk_school_code key references the primary key of the tblSchool table, and the fk_tblStudent_city_code key references the primary key of the tblCity table.

Let us try to drop the School_code column from the tblStudent table. For that, execute the following query:

ALTER TABLE [dbo].[tblstudent] 
  DROP COLUMN [school_code] 

Output

Now we see that the query encounters an error. To rectify it, we must first drop the foreign key named fk_school_code. The following code serves for that:

ALTER TABLE [dbo].[tblstudent] 
  DROP CONSTRAINT [fk_school_code]  

We can use SQL Server Management Studio to drop the foreign key too.

Expand the StudentDB database > Keys > right-click on fk_school_code and click Delete.

Once the fk_school_code key is deleted, drop the column with the following query:

USE studentdb 
go 
ALTER TABLE [dbo].[tblstudent] 
  DROP COLUMN [school_code] 

Output

Now, the column has been dropped without an error.

Remove the Column that Has a UNIQUE Constraint

We have created a UNIQUE KEY constraint on the tblStudent table. To drop the column, use the following query:

ALTER TABLE [dbo].[tblstudent] 
  DROP COLUMN [student_code] 

Output

The query had encountered an error. To fix it, we must drop the unique key constraint named UNQ_tblStudent_Student_code.

The following query drops the unique key:

ALTER TABLE [dbo].[tblstudent] 
  DROP CONSTRAINT [UNQ_tblStudent_Student_code]; 

Alternatively, we can drop the key using SSMS. To do that, expand the tblStudent table > Keys > Right-click on the necessary key > Click Delete.

Once the key is dropped, we can drop the column by running the query:

USE studentdb 
go 
ALTER TABLE [dbo].[tblstudent] 
  DROP COLUMN [student_code] 

Output

The column has been dropped.

Summary

This way, we’ve examined the most efficient SQL delete column from table ways applicable for existing tables. In particular, we’ve paid attention to the two methods – using SSMS and using the ALTER TABLE statement. Besides, we reviewed the specific cases of dropping columns that have foreign keys and unique keys.

In practice, SQL specialists have to drop columns frequently, it is one of the basic operations. We hope that this article could provide some highlights on how to perform this operation quickly and accurately, avoiding errors. 

Tags: sql delete, sql server, ssms Last modified: October 07, 2022