#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    2

    [SOLVED] Foreign Key Constraint on easy db (w/ image)


    I get a foreign key constraint error when i try to define a relation.. I want to make sure that if a question is deleted in the VRAGEN table, it's also gone in the ANTWOORDEN table

    Code:
    #1452 - Cannot add or update a child row: a foreign key constraint fails (`radar2`.<result 2 when explaining filename '#sql-12f8_41b'>, CONSTRAINT `#sql-12f8_41b_ibfk_2` FOREIGN KEY (`vraag_id`) REFERENCES `vragen` (`vraag_id`) ON DELETE CASCADE ON UPDATE CASCADE)
    Last edited by notflip; November 5th, 2012 at 11:36 AM. Reason: solved
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,030
    Rep Power
    4210
    Originally Posted by notflip
    I want to make sure that if a question is deleted in the VRAGEN table, it's also gone in the ANTWOORDEN table
    please do a SHOW CREATE TABLE for both tables, so that we can see the column definitions
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    2
    Thanks here you go!

    VRAGEN
    Code:
    CREATE TABLE `vragen` (
     `vraag_id` mediumint(4) NOT NULL AUTO_INCREMENT,
     `vraag_naam` text NOT NULL,
     `categorie_id` mediumint(5) NOT NULL,
     PRIMARY KEY (`vraag_id`),
     KEY `categorie_id` (`categorie_id`),
     CONSTRAINT `vragen_ibfk_1` FOREIGN KEY (`categorie_id`) REFERENCES `categorieen` (`categorie_id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8



    ANTWOORDEN
    Code:
    CREATE TABLE `antwoorden` (
     `antwoord_naam` mediumint(2) NOT NULL,
     `vraag_id` mediumint(4) NOT NULL,
     `werknemer_id` mediumint(4) NOT NULL,
     UNIQUE KEY `vraag_id` (`vraag_id`,`werknemer_id`),
     KEY `werknemer_id` (`werknemer_id`),
     KEY `vraag_id_2` (`vraag_id`),
     CONSTRAINT `antwoorden_ibfk_1` FOREIGN KEY (`werknemer_id`) REFERENCES `werknemers` (`werknemer_id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,030
    Rep Power
    4210
    okay, thanks

    i think i understand the error now...

    you can't define the FK because there are rows currently in the child table that violate it
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    2
    Originally Posted by r937
    okay, thanks

    i think i understand the error now...

    you can't define the FK because there are rows currently in the child table that violate it
    Aha! ohmy that was stupid! working now. had to truncate first. To bad the mysql errors are so unclear to new people. Thanks!

IMN logo majestic logo threadwatch logo seochat tools logo