SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: Missing Records

  1. #1
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Missing Records

    I have two tables
    Code:
    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?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by tgavin View Post
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Excellent, worked like a charm.

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

    Code:
    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.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    both tables have to be InnoDB, and both PK and FK columns must be the exact same datatype
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard tgavin's Avatar
    Join Date
    Feb 2003
    Location
    FL
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you're right about FULLTEXT not working on InnoDB

    but it sounds like you have many other problems to tackle first...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •