delete main row and all children mysql and php

Question

Hi there,

I have inherited a PHP project and the client is wanting to add some functionality to their CMS, basically the CMS allows them to create some news, all the news starts with the same content, and that is saved in one table, the actually news headline and articles are saved in another table, and the images for the news are saved in another, basically if the base row for the news is deleted I need all the related rows to be deleted, the database is not setup to work with foreign keys so I cannot use cascade deletion, so how can I delete the all the content I need to, when I only what the ID of the base news row is?

Any help would be very helpful I am sorry I cannot give you much more help, here is this the original SQL of tables scheme if that helps?

    --
-- Table structure for table `mailers`
--

CREATE TABLE IF NOT EXISTS `mailers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mailer_title` varchar(150) NOT NULL,
  `mailer_header` varchar(60) NOT NULL,
  `mailer_type` enum('single','multi') NOT NULL,
  `introduction` varchar(80) NOT NULL,
  `status` enum('live','dead','draft') NOT NULL,
  `flag` enum('sent','unsent') NOT NULL,
  `date_mailer_created` int(11) NOT NULL,
  `date_mailer_updated` int(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

-- --------------------------------------------------------

--
-- Table structure for table `mailer_content`
--

CREATE TABLE IF NOT EXISTS `mailer_content` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `headline` varchar(60) NOT NULL,
  `content` text NOT NULL,
  `mailer_id` int(11) NOT NULL,
  `position` enum('left','right','centre') DEFAULT NULL,
  `created_at` int(10) NOT NULL,
  `updated_at` int(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ;

-- --------------------------------------------------------

--
-- Table structure for table `mailer_images`
--

CREATE TABLE IF NOT EXISTS `mailer_images` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(150) NOT NULL,
  `filename` varchar(150) NOT NULL,
  `mailer_id` int(11) NOT NULL,
  `content_id` int(11) DEFAULT NULL,
  `date_created` int(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;

It is worth noting that the schema cannot be changed nor can I change to the DB to MYISAM so that I can use foreign keys.

Answer 1

Add foreign key to table mailer_content

FOREIGN KEY (mailer_id)
REFERENCES mailers(id)
ON DELETE CASCADE

Add foreign key to table mailer_images

FOREIGN KEY (content_id)
REFERENCES mailer_content(id)
ON DELETE CASCADE

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

Answer 2

If you can not change the schema then triggers are not an option.

InnoDB supports transactions, so deleting from two tables should not be an issue, what exactly is your problem?

P.S. It would be worth noting which version of the server are you using.

Answer 3

It is worth noting that the schema cannot be changed nor can I change to the DB to MYISAM so that I can use foreign keys.

Why can't the schema be changed? You designed the app, didn't you? Even if you didn't, adding the proper keys is just a matter of adding the right indexes and then altering the right columns. @Michael Pakhantosv's answer has what looks to be the right bits of SQL.

Further, it's InnoDB that does foreign keys, not MyISAM. You're fine there already.

If you could change the schema, making the appropriate IDs actual, real Foreign Keys and using ON DELETE CASCADE would work. Or maybe triggers. But that's just asking for it.

Now, for some reason, ON DELETE CASCADE isn't liked very much around here. I disagree with other people's reasons for not liking it, but I don't disagree with their sentiment. Unless your application was designed to grok ON DELETE CASCADE, you're in for a world of trouble.

But, given your requirement...

basically if the base row for the news is deleted I need all the related rows to be deleted

... that's asking for ON DELETE CASCADE.

So, this might come as a shock, but if you can't modify the database, you'll just have to do your work in the code. I'd imagine that deleting a news article happens in only one place in your code, right? If not, it'd better. Fix that first. Then just make sure you delete all the proper rows in an appropriate order. And then document it!