Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2010
    Posts
    7

    Unanswered: Foreign Key Constraint Error (inserting new field)

    **SOLVED: Just had to make sure there was valid data in both table columns before assigning the foreign key. Silly mistake!
    ------------------

    Yeah I decided to add a new table with a primary key, and create a new field and foreign key reference in an existing table (both InnoDB) - now the field insertion into the existing table worked, but everytime I try and update the fields foreign key I get the following error:

    cannot add or update a child row: foreign key constraints #sql-bd0-21

    Both field attributes are the same (type, size, Not Null etc.) and no matter what I try, I can't seem to get the foreign key to work.

    In host_games table I would like the currency_id to be a foreign key referencing the currency_id in currency_id table.

    host_games table dump:

    Code:
    CREATE TABLE `host_games` (
      `game_id` int(11) unsigned NOT NULL,
      `host_id` int(11) unsigned NOT NULL,
      `location_id` int(4) unsigned NOT NULL,
      `setup_fee` int(11) unsigned NOT NULL,
      `web_hosting` int(11) unsigned NOT NULL,
      `currency_id` int(11) unsigned NOT NULL,
      `ftp_access` int(11) unsigned NOT NULL,
      `redirect_sys` int(11) unsigned NOT NULL,
      `public_slot` decimal(6,2) NOT NULL,
      `private_slot` decimal(6,2) NOT NULL,
      `date_added` date NOT NULL,
      `last_updated` date DEFAULT NULL,
      PRIMARY KEY (`game_id`,`host_id`,`location_id`),
      KEY `game_id` (`game_id`),
      KEY `host_id` (`host_id`),
      KEY `location_id` (`location_id`),
      KEY `setup_fee` (`setup_fee`),
      KEY `redirect_sys` (`redirect_sys`),
      KEY `ftp_access` (`ftp_access`),
      KEY `currency_id` (`currency_id`),
      CONSTRAINT `ftp_access_fk` FOREIGN KEY (`ftp_access`) REFERENCES `options` (`options_id`) ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT `host_gamesid_fk` FOREIGN KEY (`game_id`) REFERENCES `games` (`game_id`) ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT `host_id_fk` FOREIGN KEY (`host_id`) REFERENCES `host` (`host_id`) ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT `location_id_fk` FOREIGN KEY (`location_id`) REFERENCES `location` (`location_id`) ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT `redirect_sys_fk` FOREIGN KEY (`redirect_sys`) REFERENCES `options` (`options_id`) ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT `setup_fee_fk` FOREIGN KEY (`setup_fee`) REFERENCES `options` (`options_id`) ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT `web_hosting_fk` FOREIGN KEY (`ftp_access`) REFERENCES `options` (`options_id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    currency_id table dump:

    Code:
    CREATE TABLE `currency` (
      `currency_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `currency` varchar(3) NOT NULL,
      PRIMARY KEY (`currency_id`),
      UNIQUE KEY `currency` (`currency`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    The only difference I can spot, is that in the currency_id table, currency_id auto increments - but that didnt cause a problem with the location_id foreign key. Any help appreciated - I've searched google but found nothing as specific as what I'm trying to figure out!
    Last edited by Kiada; 03-25-10 at 14:57.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Kiada View Post
    Just had to make sure there was valid data in both table columns before assigning the foreign key.
    actually, that's not necessary -- you can quite happily declare foreign keys when the tables are empty

    what you cannot do, of course, is declare foreign keys when the tables already have data in them that would violate the foreign key

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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