Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Does mysql have a way to delete all sub links if a row was deleted from a "parent" table?

Example table setup is below, the issue I am trying to avoid is having to maintain a script that deletes the data as the table ex_item_a will have a lot of similar tables[they have that same foreign key relationship]....In this case, let's say if I delete someone from the user table it should delete all links.

CREATE TABLE IF NOT EXISTS `account` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `user` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `account_id` int(10) unsigned NOT NULL,
    `email` varchar(50) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `account_id` (`account_id`),
    CONSTRAINT `FK_user_account` FOREIGN KEY (`account_id`) REFERENCES `account` (`id`) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS `account_tbl_ex` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `account_id` int(10) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    KEY `account_id` (`account_id`),
    CONSTRAINT `FK_account_tbl_ex_account` FOREIGN KEY (`account_id`) REFERENCES     `account` (`id`)
);

CREATE TABLE IF NOT EXISTS `account_tbl_ex_link` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `link_id` int(10) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    KEY `link_id` (`link_id`),
    CONSTRAINT `FK_account_tbl_ex_link_account_tbl_ex` FOREIGN KEY (`link_id`) REFERENCES `account_tbl_ex` (`id`)
);

CREATE TABLE IF NOT EXISTS `ex_item_a` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `tbl_link_id` int(10) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    KEY `tbl_link_id` (`tbl_link_id`),
    CONSTRAINT `FK_ex_item_a_account_tbl_ex_link` FOREIGN KEY (`tbl_link_id`) REFERENCES `account_tbl_ex_link` (`link_id`)
);
share|improve this question
up vote 3 down vote accepted

I did a little more research and tested on my local database, found out that the child tables just need to cascade delete and it works from the top down perfectly.

You do this by adding the ON CASCADE DELETE option to the foreign keys where you want this to happen.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.