Alter MySQL table to add & drop column & add Foreign Key

This article shows:-

– How to add column to mysql database table after the table has already been created

– How to delete column from mysql database table after the table has already been created

– How to add foreign key to table column after the table has already been created

Basically, all this can be done by ‘ALTER TABLE‘ statement. Here is an step-by-step tutorial on how we do it.

Create parent table

CREATE TABLE IF NOT EXISTS parent(
id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
description TEXT DEFAULT NULL,
status TINYINT(1) NOT NULL,
PRIMARY KEY(id)
);


Create child table

CREATE TABLE IF NOT EXISTS child(
id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
description TEXT DEFAULT NULL,
PRIMARY KEY(id)
);


After we create both parent and child table, we remember that:-

– We have added an extra unnecessary column in parent table.
– We have missed adding a column to child table.
– And this column in child table should be the foreign key for the parent table.

So, our step would be:-

First of all, delete the unncessary column from parent table

ALTER TABLE parent DROP status;

Secondly, add a new column in child table

ALTER TABLE child ADD parent_id int(11);

Finally, add foreign key contraint to the child table which relates to the parent table’s column (primary key)

ALTER TABLE child ADD CONSTRAINT child_FK_1
FOREIGN KEY (parent_id) REFERENCES parent (id)
ON DELETE CASCADE;

More detail on MySQL foreign key and its implementation can be found on: MySQL Database: Foreign Key Understanding and Implementation

Thanks.