MySQL and TableAdpators. Three tables:
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
, CONSTRAINTFK_gi_game-x-genre_gi_game
FOREIGN KEY (IDGame
) REFERENCESgi_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:
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).
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)
User contributions licensed under CC BY-SA 3.0