Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jan 2006
    Posts
    17

    Unanswered: MySQL won't let me add a row

    I get the following error when I try to add a row to a table:

    Cannot add or update a child row: a foreign key constraint fails

    There are two foreign keys, but this is not a referential integrity problem.

    Here is the query:

    insert into showsongs(show_id, song_id, songnumber, isSegue, isCloser, isEncore1, isEncore2, isSetBreak) values (342, 141, 1,0,0,0,0,0)

    I think the problem lies in the song_id field because using Navicat, I'm able to add data to all columns except the song_id field.

    both foreign keys do have records associated with them. Here's proof:
    show_id 342: http://btdb.org/showstats.asp?show_id=342
    song_id 141: http://btdb.org/songstats.asp?song_id=141

    Thanks in advance for any help.

  2. #2
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    If MySQL is telling you that something can't be done because a foreign key constraint fails, most likely it is because a foreign key constraint fails.

    Post your table structure for the problematic table and any related tables (including all indexes, foreign keys, etc.) Also anything else which may be affecting it (ie. triggers.)

  3. #3
    Join Date
    Jan 2006
    Posts
    17
    Quote Originally Posted by jfulton
    If MySQL is telling you that something can't be done because a foreign key constraint fails, most likely it is because a foreign key constraint fails.

    Post your table structure for the problematic table and any related tables (including all indexes, foreign keys, etc.) Also anything else which may be affecting it (ie. triggers.)
    You'll have to forgive me, I'm not too proficient with MySQL as I've had this problem since I migrated my Access backend to MySQL. I dont have any triggers, I know that but here are the tables in question and their attributes:

    shows(show_id(pk), date, venue_id(fk), tour_id(fk), noSetlist, showNotes, rarity, cancelled)
    indexes:
    PRIMARY PRIMARY 115 show_id
    fi0 INDEX 2 tour_id
    fi1 INDEX 115 venue_id

    song(song_id(pk), s_name, album_id(fk), isCover, original, isNotStudio, lyrics, track_no, redirect, unique)
    indexes:
    PRIMARY PRIMARY 298 song_id
    song_id_3 UNIQUE 298 song_id
    fi0 INDEX 149 album_id
    song_id INDEX 298 song_id
    song_id_2 INDEX 298 song_id
    Warning PRIMARY and INDEX keys should not both be set for column `song_id`
    Warning More than one INDEX key was created for column `song_id`
    (I have no idea what these warnings mean, they could be part of the problem but I dont know how to resolve these.)

    showsongs(showsong_id(pk), show_id(fk), song_id(fk), songNumber, isSegue, isCloser, isEncore1, isEncore2, isSetBreak, songDesc, songLength)
    indexes:
    PRIMARY PRIMARY 4288 showSong_id
    fi0 INDEX 178 song_id
    fi1 INDEX 476 show_id
    fi2 INDEX 178 song_id
    Warning More than one INDEX key was created for column `song_id`


    Thanks for the help.

  4. #4
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    Hmmm...I'm not sure what kind of effect having two indices on the same column will have, but I can't imagine it being helpful. Try dropping those and see how things go from there. If you're using a GUI, there should be options to drop the index, otherwise use this. (I'd assume since they're duplicates, dropping one won't ruin any sort of referential integrity, but be careful.)

  5. #5
    Join Date
    Jan 2006
    Posts
    17
    Quote Originally Posted by jfulton
    Hmmm...I'm not sure what kind of effect having two indices on the same column will have, but I can't imagine it being helpful. Try dropping those and see how things go from there. If you're using a GUI, there should be options to drop the index, otherwise use this. (I'd assume since they're duplicates, dropping one won't ruin any sort of referential integrity, but be careful.)

    alright, i no longer have any index issues (at least as far as I know) but i still get the same error when trying to insert a row.

  6. #6
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    OK, let's try one more time...

    Post the actual mysqldump of your table structures so we can see exactly what's going on and make sure these keys are set up correctly.

  7. #7
    Join Date
    Jan 2006
    Posts
    17
    like i said, i'm new to this, and i dont THINK i have access to the shell to run mysqldump, so i took some screenshots of my db from phpadmin. here are 3 of the tables in question:

    shows:http://img219.imageshack.us/img219/6008/showsscr7vt.jpg
    song:http://img213.imageshack.us/img213/5011/songscr7vo.jpg
    showsongs:http://img230.imageshack.us/img230/5...ongsscr9xp.jpg

  8. #8
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    In phpMyAdmin, double check and make sure that your foreign keys are referencing the correct fields on the correct tables. It sounds simple, but I can't think of why you wouldn't be able to insert this data unless something was wrong with those keys...

    So in showsongs table, index fi0 should be referencing songs.song_id and fi1 should be referencing shows.show_id.

    If that's not the problem respond back.

    (Also, one more thing which may be worth trying...create another foreign key index for the show id/song id. If it let's you create it, then all of your data should be correct and there is something with the old key.)

  9. #9
    Join Date
    Jan 2006
    Posts
    17
    Ok, they are referencing the correct tables (http://img235.imageshack.us/img235/8...inksscr0rn.jpg), but do i need to set anything for ON DELETE or ON UPDATE?

    Also, I'm unable to delete the current indexes. I tried to delete fi0 and fi1 to recreate them and I get the following error: #1025 - Error on rename of '.\db\#sql-574_6622' to '.\db\showsongs' (errno: 150) .

  10. #10
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    The ON DELETE/ON UPDATE will tell the database what to do on one of those events (ie. if a foreign key's parent is deleted, cascade and delete the child as well.)

    After a quick google search apparently foreign keys can be created even if the data does not conform to the restrictions specified. (I thought this wasn't possible.) Apparently if you have orphaned data you can get the error you've been seeing. Try something like this to find the bad data:
    Code:
    select showsong.id 
    from showsong 
    left join song on (showsong.song_id=song.id) 
    where showsong.id is not null and song.id is null;
    Also, you should be able to drop the Foreign Keys...phpMyAdmin won't let you?
    Code:
    ALTER TABLE showsong DROP FOREIGN KEY fi0
    See if you can just create another index without deleting the existing one just for kicks.

  11. #11
    Join Date
    Jan 2006
    Posts
    17
    SELECT showsong_id
    FROM showsongs
    LEFT JOIN song ON ( showsongs.song_id = song.song_id )
    WHERE showsong_id IS NOT NULL
    AND song.song_id IS NULL
    LIMIT 0 , 30


    returned an empy resultset.


    ALTER TABLE showsongs DROP FOREIGN KEY fi0

    #1025 - Error on rename of '.\db\showsongs' to '.\db\#sql2-574-6658' (errno: 152)

  12. #12
    Join Date
    Jan 2006
    Posts
    17
    also, it finally did let me delete an index only after creating the duplicate first. but i still have the same error.

  13. #13
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    I'm stumped. The referenced tables have the correct data, the keys are all set up correctly, there is no orphaned data, but it still won't let you insert? ...it's probably something glaringly obvious...

    Anyone else have any ideas???

    If this is a super-serious problem, feel free to pm me and send me a dump of the database (as long as there's no sensitive data) and I'll play around with it for a little while. (You can make a dump by selecting export in phpMyAdmin and check off the "save as file" option.)

  14. #14
    Join Date
    Jan 2006
    Posts
    17
    i'll definitely send you a dump if you want one. however, i did do something that fixed the issue but may not be very good practice. i basically deleted the foreign key references so now, i think, mysql just thinks those are regular int fields. my joins are still done with in code and i am now able to add records. but surely this isnt the only fix.

  15. #15
    Join Date
    Feb 2006
    Posts
    1
    hmm interesting how you can't add a row

Posting Permissions

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