Home » Database, MySQL7 February 2012

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.

From Mukesh Chapagain's Blog, post Alter MySQL table to add & drop column & add Foreign Key

php magento mukesh chapagain

Get New Post by Email

Find me on

Facebook Twitter Google+ LinkedIn RSS Feed