#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    5
    Rep Power
    0

    Foreign Key Error 1452


    Hi,

    I'm new to this forum and to MySql so please excuse me if my explanation of the problem lacks anything. Basically, I've created all my Parent tables and now I'm working on the junction tables. I have created one or two junction tables so far that have 2 foreign keys in each table which are also both primary keys in each table. One of the foreign keys I used is called sno_id and it references back to the parent table sno_Table. Now I was able to make sno_id as the foreign key in one of the junction tables by using a constraint (alter table sno_Ref add constraint fk_sno foreign key (sno_id) references sno_Table (sno_id)). However, in this other junction table right now which has 2 primary keys (location_id and sno_id), I was able to make location_id as a foreign key but it won't make sno_id as the foreign key. This time I didn't add the constraint so I made sure there wouldn't be an identical constraint name, and also I know for sure the values in the sno_id column in this table are found in the parent table, sno_Table. The error I get is 1452 - Cannot add or update a child row: a foreign key constraint fails. What do I do?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,260
    Rep Power
    4279
    could you please do a SHOW CREATE TABLE for each table involved so we can see its current definition
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    5
    Rep Power
    0

    sure, here it is, thank you.


    Originally Posted by r937
    could you please do a SHOW CREATE TABLE for each table involved so we can see its current definition
    Here it is for the parent table:

    | sno_Table | CREATE TABLE `sno_Table` (
    `sno_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `sno_name` varchar(20) NOT NULL,
    `family` varchar(10) NOT NULL,
    `type` varchar(10) NOT NULL,
    `source` varchar(10) DEFAULT NULL,
    `org_id` int(10) unsigned NOT NULL,
    PRIMARY KEY (`sno_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=980 DEFAULT CHARSET=latin1 |

    Here it is for the child table:

    | sno_Locations | CREATE TABLE `sno_Locations` (
    `sno_id` int(10) unsigned NOT NULL DEFAULT '0',
    `location_id` int(10) unsigned NOT NULL DEFAULT '0',
    `genedb_id` int(20) unsigned DEFAULT NULL,
    PRIMARY KEY (`sno_id`,`location_id`),
    KEY `location_id` (`location_id`),
    CONSTRAINT `sno_Locations_ibfk_3` FOREIGN KEY (`location_id`) REFERENCES `Genomic_Locations` (`location_id`),
    CONSTRAINT `fk_locat` FOREIGN KEY (`location_id`) REFERENCES `Genomic_Locations` (`location_id`),
    CONSTRAINT `sno_Locations_ibfk_1` FOREIGN KEY (`location_id`) REFERENCES `Genomic_Locations` (`location_id`),
    CONSTRAINT `sno_Locations_ibfk_2` FOREIGN KEY (`location_id`) REFERENCES `Genomic_Locations` (`location_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |


    So as you can see, I was able to add locations_id as a foreign key to a different parent table which I didnt include here, but the one I'm trying to get as foreign key and having issues with is sno_id.

    Thank you so much!
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,260
    Rep Power
    4279
    why are you declaring 4 duplicate foreign keys for location_id to the same table?

    i don't understand
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    5
    Rep Power
    0

    foreign keys


    Originally Posted by r937
    why are you declaring 4 duplicate foreign keys for location_id to the same table?

    i don't understand
    Oh I don't even know why location_id is duplicated 4 times, thank you. So if I drop 3 of the foreign keys will that allow me to add sno_id as a foreign key?
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    5
    Rep Power
    0

    foreign keys


    Originally Posted by AdrianJ217
    Oh I don't even know why location_id is duplicated 4 times, thank you. So if I drop 3 of the foreign keys will that allow me to add sno_id as a foreign key?
    So I dropped 3 of the keys, but I'm still not able to make sno_id a foreign key, and the command I'm using is:

    alter table sno_Locations add foreign key (sno_id) references sno_Table (sno_id);
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,260
    Rep Power
    4279
    hmmm, that looks okay to me...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo