How To Delete Column in SQL? – The Complete Guide

Originally created by IBM, Standard Query Language (SQL) was popularized by Oracle in the 1970s. Oracle was the first commercial relational database management system (RDBMS) developed in 1979 that supported SQL. 

Deleting columns in SQL, from a table is a common procedure that can be done for several different reasons. For instance, you could get rid of a column that is no longer necessary or rearrange your data to make it work better. Any data analyst or SQL developer should know how to delete column in SQL.

Understanding SQL

Structured Query Language (SQL) is the data definition language and the data manipulation language of several relational database systems. SQL is the main interface for communication between users and relational database management systems.

Among the other database languages, Data Definition Language (DDL) allows users to define data and their relationships to other types of data. For example, CREATE, ALTER, and DROP are SQL DDL statements. DDL is not used to manipulate data but is mainly used to create and remove database objects. 

One of the many operations that SQL is capable of is deleting column(s). A user can use the DDL command to remove column(s) from a database table if they are unnecessary or redundant. The ALTER TABLE DROP COLUMN statement is used to execute the delete column query.

While you will understand the use of the DROP statement through the learning in this blog, you may choose to take a SQL course to learn different SQL commands. 

Syntax to DROP One Column with an Example

Use the ALTER command to drop a column from the table.

Syntax (to delete one column) –

ALTER TABLE table_name

DROP COLUMN column_name;

To explain the above syntax, the tabel_name specifies the name of the table from which you want to delete or remove the column. The column name represents the column you want to delete from the table. 

To DROP a column from a table, you will have to first create a table with the following CREATE statement.

Syntax –

CREATE TABLE table_name
( 
   column_name 1 data type,
   column _name 2 data type,
   …,
   column_name_n
) ;

Example –

CREATE TABLE Students
( 
Stu_name VARCHAR (30),
Roll_no INT,
Subject VARCHAR (20),
Marks INT
);

The next step is to INSERT values to complete the creation of the table.

Syntax –

INSERT INTO Students (column_name 1, column_name 2,…, column_name_n)

VALUES (value_1, value_2,…,value_n);

Example –

INSERT INTO Students (Stu_name, Roll_no, Subject, Marks)

VALUES (‘Vir’, 1, ‘English’, 80);

INSERT INTO Students (Stu_name, Roll_no, Subject, Marks)

VALUES (‘Sam’, 2, ‘History’, 70);

INSERT INTO Students (Stu_name, Roll_no, Subject, Marks)

VALUES (‘Tina’, 3, ‘Math’, 90);

INSERT INTO Students (Stu_name, Roll_no, Subject, Marks)

VALUES (‘Mike’, 4, ‘Computer’, 75);

A table will be created exactly in the sequence of columns mentioned in the INSERT statement. Now, how will you delete the column named ‘Marks’ from the table Students? See the example below-

ALTER TABLE Students

DROP COLUMN Marks;

Syntax to DROP Two or More Columns with an Example

It is the user’s choice to drop one or more columns, depending on the requirements of the information to be visible in the database. List out the column names that you want to drop, and separated them by a comma.

Syntax –

ALTER TABLE table_name     

DROP COLUMN   
 ( 
    column_name1,  
    column_name2,  
    ...,  
    column_name_n
);

Let’s say you want to drop the columns, namely, Roll_No and Subject, from the table.
Example -


ALTER TABLE Students

DROP COLUMN 
(
  Roll_no,
  Subject
); 

The table is altered, and the column in the table that remains in the result is Stu_name.

How To Delete a FOREIGN KEY Constraint Column from a Table?

Before we begin this section, first understand briefly what a FOREIGN KEY is. To avoid actions that would break links between tables, the foreign key constraint is employed. A field (or group of fields) in one table that refers to the primary key in another table is known as a foreign key. The table with a foreign key is called a child table, and the table with a primary key is called a parent table.

Use the ALTER command in SQL and the DROP CONSTRAINT statement to drop the foreign key. 

First, you need to create a parent table. Let’s name it “Students”.

Syntax –

CREATE TABLE Students
(
   student_id INT PRIMARY KEY,
   stu_name VARCHAR (30),
   course_category VARCHAR (25)
);

The parent table, “Students,” is created with the primary key – student_id. The second step is to create the child table. Let’s name it “Course”.

CREATE TABLE Course
(
  hostel_id INT PRIMARY KEY,
  student_id INT,
  CONSTRAINT fk_course_student_id
  FOREIGN KEY (student_id)
  REFERENCES students (student_id)
);

The child table “Course,” with hostel_id as the primary key and fk_course_student_id as its foreign key, is created. The foreign key relates to the student_id field in the parent table, “Students”.

Syntax for DROP a FOREIGN KEY constraint –

ALTER TABLE table_name

DROP CONSTRAINT fk_name;

Table_name is the table from which the foreign key is to be dropped, and fk_name is the column name of the foreign key. Using the above example, let’s define a statement that drops the foreign key from the child table.

Example –

ALTER TABLE Course
DROP CONSTRAINT fk_course_student_id;

Conclusion

We learned how to delete columns in SQL, two or more columns, and a column with a foreign key constraint. The DROP column command in SQL using the ALTER TABLE statement is the go-to method to delete a column or columns in a table. You can simply try out and practice the SQL operations by following the steps and syntax shared in this blog.