Deleting rows from related tables in MySQL

I've seen different implementations to maintain related records in MySQL, especially when there is a need to delete related rows in multiple tables. This article shares how to delete related rows using a single SQL query when using either InnoDB or MyISAM storage engine

Table 1: blogposts (blogid, blogcontent, etc) which stores blog posts

Table 2: comments (id, blogid, comment, etc) which stores blogs comments and references blog post table by blogid

Deleting rows from related tables in MySQL using InnoDB

If you use InnoDB storage engine, then you can use foreign keys, which will automatically delete records in comments table when a related blog post is deleted in blogposts.

The foreign keys can be added by running the following query:

ALTER TABLE `comments`
ADD CONSTRAINT `fk_commnts_blogposts` FOREIGN KEY (`blogid`) REFERENCES `blogpost` (`blogid`) ON DELETE CASCADE;

The same query with comments

-- name of the table that references   our primary blogposts table
ALTER TABLE `comments` -- name of our new constraint. ADD CONSTRAINT `fk_commnts_blogposts`
-- they column used as a key in the comments table that will reference the primary table FOREIGN KEY(`blogid`) -- primary table and a column referenced by our secondary table REFERENCES `blogpost`(`blogid`) -- action that should be taken when blogpost is updated ON DELETE CASCADE;

Source: http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

MySQL Error Number 1005 Can’t create table ‘.\XYZ.frm’ (errno: 150)

A troubleshooting list of common issues that you may run into when creating the foreign keys

  1. The two key fields type and/or size do not match.
  2. One of the key field that you are trying to reference does not have an index and/or is not a primary key. If one of the fields in the relationship is not a primary key, you must create an index for that field.
  3. The foreign key name already exists. Check that the name of your foreign key is unique within your database.
  4. One or both of your tables is a MyISAM table. In order to use foreign keys, the tables must both be InnoDB.
  5. You have specified a cascade ON DELETE SET NULL, but the relevant key field is set to NOT NULL.  You can fix this by either changing your cascade or setting the field to allow NULL values.
  6. Make sure that the Charset and Collate options are the same both at the table level as well as individual field level for the key columns.
  7. You have a default value (ie default=0) on your foreign key column.
  8. One of the fields in the relationship is part of a combination (composite) key and does not have it’s own individual index.
  9. You have a syntax error in your ALTER statement or you have mistyped one of the field names in the relationship
  10. The name of your foreign key exceeds the max length of 64 chars.

Source: http://verysimple.com/2006/10/22/mysql-error-number-1005-cant-create-table-mydbsql-328_45frm-errno-150/

Deleting rows from related tables in MySQL using MyISAM

If you use MyISAM storage engine, then you will need to run an extended MySQL query to delete related records in comments table when a blog post is deleted.

DELETE blogposts, comments
FROM comments JOIN blogposts ON comments.blogid = blogposts.blogid
WHERE blogposts.blogid = $myID;

or

DELETE blogposts, comments
FROM blogposts INNER JOIN Comments ON blogposts.blogid = comments.blogid
WHERE blogposts.blogid = $myID;

 

Would you like to contribute?



Free business joomla templates