Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

General Discussion

Posted by Emmanuele Massimi
Emmanuele Massimi
7,420 Points

MySQL: Automatically deleting records from many-to-many relationship table when records deleted from one of the tables

Hi All,

I've been playing around with MySQL lately, and I have the following tables (using SHOW CREATE TABLE):

CREATE TABLE `posts` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `post_title` text,
  `post_body` text,
  `last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=26 DEFAULT CHARSET=latin1 

CREATE TABLE `tags` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `name_2` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1

CREATE TABLE `post_tags` (
  `post_ID` int(11) NOT NULL,
  `tag_ID` int(11) NOT NULL,
  PRIMARY KEY (`post_ID`,`tag_ID`),
  KEY `tag_ID` (`tag_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

This is all pretty basic: the posts table contains all the post-related information (ID, title, body, timestamps) and the tags table just the tag names alongside their ID. The post_tags table contains the many-to-many relationship among the two (i.e. one post can have many tags, and a tag can have many posts).

Now, I just cleared the posts table (DELETE FROM posts), and I noticed that the post_tags table is still full… is there any way that I can make MySQL delete the no-longer-existing entries from that table? Or do I have to delete them manually? I hope not!

Thank you very much for your input! :)

1 Answer

Emmanuele Massimi
Emmanuele Massimi
7,420 Points
Emmanuele Massimi
Emmanuele Massimi
7,420 Points

To reply to my own question: I realised that my tables were using the engine MyISAM, which doesn't support a very useful feature: ON DELETE CASCADE. This makes sure that no orphans are left – exactly what I wanted. In order to use this feature, I had to convert my tables to InnoDB:

ALTER TABLE table_name ENGINE=INNODB

Then I could add the foreign keys (which again aren't supported by MyISAM) to my relationship table, together with the ON DELETE CASCADE option:

ALTER TABLE post_tags 
ADD CONSTRAINT fk_post_id 
FOREIGN KEY (post_ID) REFERENCES posts (ID) ON DELETE CASCADE;

ALTER TABLE post_tags 
ADD CONSTRAINT fk_tag_id 
FOREIGN KEY (tag_ID) REFERENCES tags (ID) ON DELETE CASCADE;

Before I could get this to work, I had to convert all the tables involved (post_tags, posts, tags) to InnoDB, otherwise MySQL would fail with this error:

ERROR 1005 (HY000): Can't create table 'database_name.#sql-239a_317' (errno: 150)

Now, every time I delete an entry either from the posts or the tags table, it is automatically removed from the relationships table! Yay!