2

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

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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