MySQL- Drop/ Delete column from table

In this article, we will see how to delete a column from a table in MYSQL.

Table of Contents

• MySQL delete column from a table – syntax
• MySQL delete column from a table – example
• MySQL drop a column with foreign key

Before moving ahead into the tutorial, let us look at the data we will be using in the examples. We will be creating a table students_data followed by adding some rows to it.

CREATE TABLE students_data (
student_id INT AUTO_INCREMENT,
student_name VARCHAR(255),
student_address VARCHAR(255),
student_grade VARCHAR(50),
student_subject VARCHAR(255),
 PRIMARY KEY (student_id)
);
INSERT INTO students_data (student_name, student_address, student_grade, student_subject) 
VALUES("Gustav","56A Denmark","A","Physics"),
("Henric","255 USA","B","Geography"),
("Richa","78 India","C","Physics"),
("Margit","58 Canada","A","Physics"),	
("Rasmus","18 Libya","B","Physics"),
("Erick","43 Sweden","C","Geography"),
("Tanya","78 Singapore","D","Geography"),
("Monika","255 Italy","A","Chemistry"),
("Atharv","587 California","A","Chemistry");

Let us see what went into the table students_data by executing:

SELECT * FROM students_data;

Output:-

<div id=”one” style=”padding-top: 100px; margin-top: -100px;”></div>

MySQL delete column from a table -syntax

ALTER TABLE is used to drop/delete a column from a table.

ALTER TABLE table_reference DROP COLUMN column_name;

• ALTER TABLE statement is used.
• table_reference – the name of the table from which we will be deleting the column.
• column_name – the name of the column which will get deleted.

<div id=”two” style=”padding-top: 100px; margin-top: -100px;”></div>

MySQL delete column from a table – example

We will be deleting the column student_subject from table students_data.

ALTER TABLE students_data DROP COLUMN student_subject;

Action Output Message Response:-

0 row(s) affected Records: 0 Duplicates: 0 Warnings: 0

Let us view if the column the student_subject is deleted by executing:

SHOW COLUMNS FROM students_data; 

Output:-

As shown in figure 2, the column named student_subject is not present in the column list for the table students_data.

Note – MySQL 5.6 onwards allows other sessions to read and write the table while we are deleting the column.

Observe the below query.

ALTER TABLE students_data DROP COLUMN student_subject, ALGORITHM= INPLACE , LOCK=NONE;

Action Output Message Response:-

0 row(s) affected Records: 0 Duplicates: 0 Warnings: 0

LOCK value is none. Therefore other sessions will be allowed to read and write the table while we delete a column.

MySQL drop a column with foreign key

This section lets us see how to drop a column from the table, which is a foreign key. To better understand the concept, let us create a table with a foreign key constraint and insert rows into it.

CREATE TABLE students_address_table (
student_address_id INT AUTO_INCREMENT,
student_id INT NOT NULL,
student_address VARCHAR(255),
FOREIGN KEY (student_id) REFERENCES students_data(student_id),
PRIMARY KEY(student_address_id)
);
INSERT INTO students_address_table (student_id,student_address) 
VALUES(1,"56A Denmark"),
(2,"255 USA"),
(3,"78 India"),
(4,"58 Canada"),	
(5,"18 Libya"),
(6,"43 Sweden"),
(7,"78 Singapore"),
(8,"255 Italy"),
(9,"587 California");

To view all the columns of the table, lets execute:

SHOW COLUMNS FROM students_address_table; 

Output:-

student_id column in the table students_address_table is a foreign key. Now let us try deleting this column. Observe the response after executing below query.

ALTER TABLE students_address_table DROP COLUMN student_id;

Action Output Message Response:-

Error Code: 1828. Cannot drop column ‘student_id’: needed in a foreign key constraint ‘students_address_table_ibfk_1’

Here, we got an error this time. Therefore the solution is to delete the foreign key constraint first. Syntax of deleting a foreign key constraint is similar to deleting a column.

Observe the below queries.

ALTER TABLE students_address_table DROP FOREIGN key students_address_table_ibfk_1;
ALTER TABLE students_address_table DROP COLUMN student_id;

We will recheck if this time the column student_id is deleted or not by executing:

SHOW COLUMNS FROM students_address_table;

Output:-

As we can see in figure 4, column student_id is deleted.

We hope this article helped with queries related to deleting a column in a table. Good Luck !!!.