CODEWITHSUNDEEP
mysql
Thomas Bang Thomas Gaming
Thomas Bang Thomas Gaming

Reputation: 161

Delete multiple rows while using the id from the row before

I'm trying to create a folder and file system in my database.

And while deleting the folders with the subfolder, it should also delete the corresponding files in the folder from my other table (files_plus). But how can i delete both the folder, it's subfolder and all files contained in the folder and subfolders?

Table: files_plus:

-----------------------------
| id  |  name  |  parentid  |
-----------------------------

(the "parentid" being the "id" from the folders_plus table.)

Table: folders_plus

SQL Table example

I can't think of any way to construct a query for doing so, so how would i manage to do this?

Upvotes: 1

Views: 107

Answers (2)

Ivar
Ivar

Reputation: 6828

You can add a foreign key constraint and set it on "CASCADE DELETE" like this for the folders_plus table.

ALTER TABLE `folders_plus` 
ADD INDEX `folders_plus_parentid_idx` (`parentid` ASC);
ALTER TABLE `folders_plus` 
ADD CONSTRAINT `fk_folders_plus_id_folders_plus_parentid`
  FOREIGN KEY (`parentid`)
  REFERENCES `folders_plus` (`id`)
  ON DELETE CASCADE
  ON UPDATE NO ACTION;

And for the files_plus table:

ALTER TABLE `files_plus` 
ADD INDEX `fk_files_plus_id_folders_plus_parentid_idx` (`parentid` ASC);
ALTER TABLE `files_plus` 
ADD CONSTRAINT `fk_files_plus_id_folders_plus_parentid`
  FOREIGN KEY (`parentid`)
  REFERENCES `folders_plus` (`id`)
  ON DELETE CASCADE
  ON UPDATE NO ACTION;

When you delete a parent row, it will also delete all of it's children.

Do make sure that your parentid column is nullable, and that every top level directory is null. Every parentid that is not null, is required to exist as an id in your folders_plus table.

PS: Please do note that in order for foreign key constraints to work, your database storage engine should be InnoDB.

Upvotes: 1

DreamWave
DreamWave

Reputation: 1940

  1. Make userid and parentid the same type (I'm guessing INT (10) UNSIGNED )
  2. Allow a NULL value to parentid
  3. UPDATE table SET parentid = NULL WHERE parentid = 0
  4. Create a foreign key from parentid to userid
  5. When creating the foreign key set "ON DELETE" to "CASCADE" - this will cause all items that have a parentID that has been deleted to also be deleted.

Upvotes: 0

Related Questions