Missing Records

[tgavin]

I have two tables

CREATE TABLE IF NOT EXISTS `movies` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `chapters` (
  `mc_mov_id` int(10) unsigned NOT NULL DEFAULT '0',
  `mc_lang` char(2) CHARACTER SET latin1 NOT NULL,
  `mc_chapter_num` int(10) NOT NULL,
  PRIMARY KEY (`mc_mov_id`,`mc_chapter_num`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The movies table holds the PK for a particular movie. The chapters.mc_mov_id is the FK. I've been going through the data and have noticed that there are a number of records in the chapters table with an mc_movie_id that doesn't exist anymore in the movies table.

For instance
SELECT * FROM chapters WHERE mc_mov_id=698 - contains records.
SELECT * FROM movies WHERE id=698 - contains no records.

How do I delete the records from chapters that don't have a corresponding record in the movies table?

[r937]

How do I delete the records from chapters that don't have a corresponding record in the movies table?

like this --

DELETE FROM chapters
WHERE mc_mov_id NOT IN ( SELECT id FROM movies)

it should not matter how efficient this is, as presumably you're only going to do this once -- after that, you're going to actually declare the foreign keys, right? (you'll have to change the tables to InnoDB, though)

[tgavin]

Excellent, worked like a charm.

Do all tables have to be InnoDB to add the FK? I tried

ALTER TABLE
chapters
ADD
FOREIGN KEY(mc_mov_id)
REFERENCES movies(id)

But received the MySQL Error Number 1005 Can’t create table ‘.\mydb\#sql-328_45.frm’ (errno: 150) message.

[r937]

both tables have to be InnoDB, and both PK and FK columns must be the exact same datatype

[tgavin]

I'm wondering about using InnoDB for this because these tables are going to be selected much more than inserted, deleted or updated. Plus, what about searching on InnoDB? I don't think I'm using FULLTEXT right now (can't remember) but if I want to in the future, I can't with InnoDB, right?

Also, this is a busy site. Currently I'm receiving 'too many connections' warnings. Which then makes me concerned about RAM as well.

[r937]

you're right about FULLTEXT not working on InnoDB

but it sounds like you have many other problems to tackle first...