CODEWITHSUNDEEP
mysqlsql
Sonnywhite
Sonnywhite

Reputation: 136

Deleting correct in a many-to-many relationship

I've got a short question with a many-to-many relationship. Here are my tables:

+---------+----------------+
| film_id | title          | 
+---------+----------------+
|       1 | Apocalypse Now |
+---------+----------------+


+-------------+----------------------+
| category_id | name                 | 
+-------------+----------------------+
|           1 | cool category        |
+-------------+----------------------+
|           2 | not so cool category |
+-------------+----------------------+

+---------+-------------+
| film_id | category_id |
+---------+-------------+
|       1 |           1 |
+---------+-------------+
|       1 |           2 |
+---------+-------------+

As you can see there are FKs from the film- and category-table in the film_category-table.

What I want: If one wants to delete a category, then ALL entries in the film_category where the category_id occurs should be deleted, too. BUT not the related films! In addition to that: This should work while deleting a film vice versa! (That's exactly my problem here)

My Question: Can I solve the issue with a FK-definition only or do I have to delete all entries in the film_category-table manually before deleting a film or category?

Upvotes: 0

Views: 122

Answers (1)

Blergh
Blergh

Reputation: 26

Your tables should look something like the following. A delete in the film table should delete the rows where the film category has the same id, and vice versa with the film table.

CREATE TABLE `film` (
  `film_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`film_id`)
) ENGINE=InnoDB;

CREATE TABLE `category` (
  `category_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB;

CREATE TABLE `film_category` (
 `film_category_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `film_id` int(10) unsigned NOT NULL DEFAULT '0',
 `category_id` int(10) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`film_category_id`),
 KEY `film_id` (`film_id`),
 KEY `category_id` (`category_id`),
 CONSTRAINT `film_category_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`) ON DELETE CASCADE,
 CONSTRAINT `film_category_ibfk_1` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON DELETE CASCADE
) ENGINE=InnoDB;

You should also be able to use the following for film_category:

CREATE TABLE `film_category` (
  `film_id` int(10) unsigned NOT NULL DEFAULT '0',
  `category_id` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`film_id`,`category_id`),
  CONSTRAINT `film_category_ibfk_1` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON DELETE CASCADE,
  CONSTRAINT `film_category_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`) ON DELETE CASCADE
) ENGINE=InnoDB;

Upvotes: 1

Related Questions