Take the 2-minute tour ×
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. It's 100% free, no registration required.

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`)
);

*UPDATE*: 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.

share|improve this question
    
Yes, you can add the ON CASCADE DELETE option on the foreign keys you want this to happen. Please answer the question by writing an answer, not by editing the question. –  ypercube Aug 21 '13 at 7:03
    
@ypercube thanks, I will answer it after the 10 hour restriction, new here –  Brian Putt Aug 21 '13 at 10:53

1 Answer 1

up vote 1 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.

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.