MySQL remove column – MySQL- Drop/ Delete column from table

by

MySQL delete column: In this article we are going to discuss how to delete or drop a column from a table in MYSQL.

Now first we are going to create a table students_data which we will be using in our article to show how to delete or drop a column from a table in MYSQL,

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 values in it by using below query,

INSERT INTO students_data (student_name, student_address, student_grade, student_subject) 
VALUES("Dev","23A Delhi","A","Maths"),
("Riya","188 Pune","B","Hindi"),
("Harsh","78 Indore","C","Hindi"),
("Ravi","58 Cochi","A","Physics"),    
("Aarush","18 Mumbai","B","Physics"),
("Ishan","43 Lucknow","C","Geography"),
("Tanya","78 Arouli","D","Geography"),
("Monika","255 Punjab","A","Chemistry"),
("Atharv","587 Chandigarh","A","Chemistry");

Output:

MySql delete or drop column

MySQL delete column from a table -syntax

Delete column from table MySQL: MySQL allows  ALTER TABLE command to add a new column to an table and we can also drop/delete column using this. The following are the syntax to do this:

Syntax:

ALTER TABLE table_reference DROP COLUMN column_name;

Explaination:

1.ALTER TABLE: This is Alter Command

2.table_reference – table from which we have to delete column

3.column_name – the name of the column which will get deleted.

MySQL delete column from a table – example

Drop column mysql: Here we are going to take an example to understand Alter Table Command,now we are going to delete column ‘student_subject’ from ‘student_data’ table using below query,

ALTER TABLE students_data DROP COLUMN student_subject;

Output:

MySql-delete-column2

You can see that in above output ‘student_subject’ column has deleted.

If we want other sessions to read and write the table while we delete a column we can put LOCK value is none which you can see in below query,

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

MySQL drop a column with foreign key

MySQL remove column: Here we are going to use another method to drop/delete column in MySQL.Let first create a table where we will also create a foreign key constraint,

Let’s create,

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 values in the table “students_address_table”,

INSERT INTO students_address_table (student_id,student_address) 
VALUES(1,"56A Delhi"),
(2,"255 Mumbai"),
(3,"78 Pune"),
(4,"58 Nagpur"),    
(5,"18 Kanpur"),
(6,"43 Rivan"),
(7,"78 Sonbhadra"),
(8,"255 Agra"),
(9,"587 Calcuta");

Output:

MySQL-delete-column-using-foreign

In above output you can see that ‘student_address_id’ is primary key and for this ‘student_id’ is foreign key,

Now we are going to write query for drop/delete column in table ‘students_address_table’,

ALTER TABLE students_address_table DROP COLUMN student_id;

Output:

ERROR 1828 (HY000): Cannot drop column 'student_id': needed in a foreign key 
constraint 'students_address_table_ibfk_1'

In above output you can see that we got an error because we have tried to delete column using same query which we have used for delete column using alter command,

Now we are going to make some changes in it first we will delete foreign key constraint,

ALTER TABLE students_address_table DROP FOREIGN key students_address_table_ibfk_1;

ALTER TABLE students_address_table DROP COLUMN student_id;

Output:

 

Mysql-drop-column

So you can see that in above output column ‘student_id’ deleted.

Conclusion:

In this article we have discussed how to delete or drop a column from a table in MYSQL.Thank you