"Cannot add or update a child row: a foreign key constraint fails" driving me nuts

-1

MySQL and TableAdpators. Three tables:

enter image description here

The Games set if quite large (79,000 records). the game-genre link table is constructed whilst constructing the game table. All IDs are essentially GUIDs (therefore no weird syntax errors at play). Starting from scratch, generating the vanilla data from an existing xml datasource.

I update the games table via TableAdpator.update with no problem - all 79,000 upload to mysql without hitch. Genre table is also updates fine. However, when I try to update the game_x_genre table (about 80,000 records) I get that error:

MySql.Data.MySqlClient.MySqlException (0x80004005): Cannot add or update a child row: a foreign key constraint fails (gameinfoz.gi_game_x_genre, CONSTRAINT FK_gi_game-x-genre_gi_game FOREIGN KEY (IDGame) REFERENCES gi_game (ID) ON DELETE SET NULL ON UPDATE CASCADE) at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)

I'm struggling to see any errors in my schema. The three tables:

enter image description here


enter image description here


enter image description here

I've read that the parent tables' IDs must all be populated before the child tables are updated - thus updating game and genre tables first. I've also read that the IDs must exist in the parents tables, or you get this error. So, I wrote a routine to iterate through the proposed game_x_genre foreign key values (it sits in a datatable before update) and check that these exist within the game and genre tables. They do.

The really irritating thing is that I can't think of any way to pull out the row that is causing the error. All I get is that .net error and don't know any way to get the offending article (i.e. the proposed game_x_genre datarow that causes the exception).

Desperately seeking help as this one's got me stumped!

Update:

Someone asked for the Update query. This is auto-generated by the Visual Studio TableAdpater, and it reads thus:

UPDATE       gi_game_x_genre
SET                ID = @p1, IDGame = @p2, IDGenre = @p3
WHERE        (ID = @p4) AND (@p5 = 1) AND (IDGame IS NULL) AND (@p7 = 1) AND (IDGenre IS NULL) OR
                         (ID = @p4) AND (IDGame = @p6) AND (@p7 = 1) AND (IDGenre IS NULL) OR
                         (ID = @p4) AND (@p5 = 1) AND (IDGame IS NULL) AND (IDGenre = @p8) OR
                         (ID = @p4) AND (IDGame = @p6) AND (IDGenre = @p8)

eeep! However, looking at other update queries, not sure this is that wrong. E.g. a working one:

UPDATE       gi_game_genre
SET                ID = @p1, Name = @p2, LaunchboxName = @p3, ScreenScraperName = @p4
WHERE        (ID = @p5) AND (@p6 = 1) AND (Name IS NULL) AND (@p8 = 1) AND (LaunchboxName IS NULL) AND (@p10 = 1) AND (ScreenScraperName IS NULL) OR
                         (ID = @p5) AND (Name = @p7) AND (@p8 = 1) AND (LaunchboxName IS NULL) AND (@p10 = 1) AND (ScreenScraperName IS NULL) OR
                         (ID = @p5) AND (@p6 = 1) AND (Name IS NULL) AND (LaunchboxName = @p9) AND (@p10 = 1) AND (ScreenScraperName IS NULL) OR
                         (ID = @p5) AND (Name = @p7) AND (LaunchboxName = @p9) AND (@p10 = 1) AND (ScreenScraperName IS NULL) OR
                         (ID = @p5) AND (@p6 = 1) AND (Name IS NULL) AND (@p8 = 1) AND (LaunchboxName IS NULL) AND (ScreenScraperName = @p11) OR
                         (ID = @p5) AND (Name = @p7) AND (@p8 = 1) AND (LaunchboxName IS NULL) AND (ScreenScraperName = @p11) OR
                         (ID = @p5) AND (@p6 = 1) AND (Name IS NULL) AND (LaunchboxName = @p9) AND (ScreenScraperName = @p11) OR
                         (ID = @p5) AND (Name = @p7) AND (LaunchboxName = @p9) AND (ScreenScraperName = @p11)
mysql
foreign-keys
tableadapter
asked on Stack Overflow Jan 27, 2019 by stigzler • edited Jan 28, 2019 by stigzler

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0