Cannot add or update a child row....
Hello i get this error and would appreciate some help on the matter =).
2009-jun-16 02:32:06 trav.Main saveHorseMouseClicked
ALLVARLIG: null
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityCons traintViolationException: Cannot add or update a child row: a foreign key constraint fails (`trotterdb`.`horse`, CONSTRAINT `FK_horse_race` FOREIGN KEY (`Horse`) REFERENCES `race` (`RaceDate`))
I try to insert the test-data from my java program into the db when i have added foreign keys but it fails as above.
Without the foreign keys it inserts the test-data correctly so that's not the problem.
Code:
dbm.addHorse("Bläckis", "anka", "sto", "2004-06-20", 54.72, 12.48, "O", 142, 73);
dbm.addRace("2002-02-23", "S", 2140, 2, "auto", 12.6, "1,3,4,5,6,7,8,9", "lätt", 12.6, 6, 999);
dbm.addJockey("JockeyJocke",1983,2008,31,1,2,4,33.33,1232525);
Code:
public void addHorse(String horse,String race, String sex, String born, double placePercentage, double winPercentage, String homeTrack, int victoriesThisYear, int startsThisYear) throws SQLException {
String query = "INSERT INTO horse (horse,race,sex,born,placePercentage,winPercentage,homeTrack,victoriesThisYear,startsThisYear)"+
"VALUES ('"+horse+"',"+
"'"+race+"',"+
"'"+sex+"',"+
"'"+born+"',"+
"'"+placePercentage+"',"+
"'"+winPercentage+"',"+
"'"+homeTrack+"',"+
"'"+victoriesThisYear+"',"+
"'"+startsThisYear+"')";
Statement statement = (Statement) c.createStatement();
int rowCount = statement.executeUpdate(query);
}
public void addJockey(String jockey, int born, int year, int numberOfStarts, int firsts, int seconds, int thirds, double winPercentage, int moneyWon) throws SQLException {
String query = "INSERT INTO jockey (jockey,born,year,numberOfStarts,firsts,seconds,thirds,winPercentage,moneyWon)"+
"VALUES ('"+jockey+"',"+
"'"+born+"',"+
"'"+year+"',"+
"'"+numberOfStarts+"',"+
"'"+firsts+"',"+
"'"+seconds+"',"+
"'"+thirds+"',"+
"'"+winPercentage+"',"+
"'"+moneyWon+"')";
Statement statement = (Statement) c.createStatement();
int rowCount = statement.executeUpdate(query);
}
public void addRace(String raceDate, String trackCode, int stretch, int trackNumber, String typeOfStart, double time,String orderOfHorses, String trackCondition, double winMarginal, int startTrack, int odds) throws SQLException {
String query = "INSERT INTO race (raceDate,trackCode,stretch,trackNumber,typeOfStart,time,orderOfHorses,trackCondition,winMarginal,startTrack,odds)"+
"VALUES ('"+raceDate+"',"+
"'"+trackCode+"',"+
"'"+stretch+"',"+
"'"+trackNumber+"',"+
"'"+typeOfStart+"',"+
"'"+time+"',"+
"'"+orderOfHorses+"',"+
"'"+trackCondition+"',"+
"'"+winMarginal+"',"+
"'"+startTrack+"',"+
"'"+odds+"')";
Statement statement = (Statement) c.createStatement();
int rowCount = statement.executeUpdate(query);
}
my db looks like this.
CREATE TABLE `trotterdb`.`horse` (
`Horse` varchar(45) NOT NULL,
`Race` varchar(20) NOT NULL,
`Sex` varchar(6) NOT NULL,
`Born` varchar(10) NOT NULL,
`Hometrack` varchar(2) NOT NULL,
`PlacePercentage` double NOT NULL,
`WinPercentage` double NOT NULL,
`VictoriesThisYear` int(10) unsigned NOT NULL,
`StartsThisYear` int(10) unsigned NOT NULL,
PRIMARY KEY (`Horse`) USING BTREE,
CONSTRAINT `FK_horse_race` FOREIGN KEY (`Horse`) REFERENCES `race` (`RaceDate`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `trotterdb`.`race` (
`RaceDate` varchar(10) NOT NULL,
`TrackCode` varchar(3) NOT NULL,
`Stretch` int(4) unsigned NOT NULL,
`TrackNumber` int(10) unsigned NOT NULL,
`TypeOfStart` varchar(45) NOT NULL,
`Time` double NOT NULL,
`OrderOfHorses` varchar(45) NOT NULL,
`TrackCondition` varchar(45) NOT NULL,
`WinMarginal` double NOT NULL,
`StartTrack` int(10) unsigned NOT NULL,
`Odds` int(10) unsigned NOT NULL,
PRIMARY KEY (`RaceDate`) USING BTREE,
CONSTRAINT `FK_race_jockey` FOREIGN KEY (`RaceDate`) REFERENCES `jockey` (`Jockey`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `trotterdb`.`jockey` (
`Jockey` varchar(45) NOT NULL,
`Born` int(10) unsigned NOT NULL,
`Year` int(10) unsigned NOT NULL,
`NumberOfStarts` int(10) unsigned NOT NULL,
`Firsts` int(10) unsigned NOT NULL,
`Seconds` int(10) unsigned NOT NULL,
`Thirds` int(10) unsigned NOT NULL,
`WinPercentage` double NOT NULL,
`MoneyWon` int(8) unsigned NOT NULL,
PRIMARY KEY (`Jockey`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
one horse can have done many races.
one race can only have one jockey.
(that's what i hope to get at least).
I think i made the foreign keys right...
