Understanding SQL DELETE Column from Table

Total: 1 Average: 5

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 use cases:

  1. Drop the column that has a foreign key constraint.
  2. Drop the column that has a unique constraint.
CodingSight - SQL DELETE Column from table

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:

E-R diagram of the StudentDB database

Now, let us consider the use cases.

Drop the Column Using the Table Designer

We can delete the column 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.

Drop the Column using the table designer

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.

Selecting Delete Column

The column will disappear from the list. Click Save.

Column is disappeared from the list

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

Query does not return any details of the admissiondate column

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:

column has been dropped from the table

You can see that the column has been dropped from the table.

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

Delete Column in SQL Using the ALTER TABLE Statement

The syntax for dripping the column is:

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

Drop column using the ALTER TABLE statement.

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

Delete the Column in SQL that has a Foreign Key

In the 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

Drop the column that has a foreign key

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.

SQL Server Management Studio to drop the foreign key

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

Column has been dropped without an error

Now, the column has been dropped without an error.

Drop 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

Drop the column that has a UNIQUE constraint

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.

Drop the key using SSMS

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.

The column has been dropped.

Summary

This way, we’ve examined the most efficient ways of dropping columns from 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. 

Nisarg Upadhyay

Nisarg Upadhyay

Nisarg is a SQL Server Database Administrator and Microsoft certified professional who has more than 5 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the Bachelors in Information Technology from Ganpat University.