I have scene a similar post on this error, but my problem is that I get this error while the foreign key value is clearly in the Contact Table
java.sql.SQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`client_schedule`.`appointments`, CONSTRAINT `fk_contact_id` FOREIGN KEY (`Contact_ID`) REFERENCES `contacts` (`Contact_ID`) ON DELETE CASCADE ON UPDATE CASCADE)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:354)
at com.example.customerdatabaseprojectii/com.example.customerdatabaseprojectii.daos.AppointmentsDao.updateAppointment(AppointmentsDao.java:61)
at com.example.customerdatabaseprojectii/com.example.customerdatabaseprojectii.view.AppointmentFormController.addAppointmentClicked(AppointmentFormController.java:241)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
The error gets thrown from this method:
private static final String appointmentUpdateQuery = "UPDATE appointments SET Appointment_ID = ?, Title = ?, Description = ?, " +
"Location = ?, Type = ?, Start = ?, End = ?, Last_Update = ?, Last_Updated_By = ?, " +
"Customer_ID = ?, User_ID = ?, Contact_ID = ? WHERE Appointment_ID = ?";
public static void updateAppointment(Appointments appointment) throws SQLException {
Connection apptConnection = DbConnection.getConnection();
DbConnection.makePreparedStatement(appointmentUpdateQuery, apptConnection);
PreparedStatement ps = DbConnection.getPreparedStatement();
if (ps != null) {
ps.setInt(1, appointment.getAppointmentID());
ps.setString(2, appointment.getTitle());
ps.setString(3, appointment.getDescription());
ps.setString(4, appointment.getLocation());
ps.setString(5, appointment.getType());
ps.setTimestamp(6, Timestamp.valueOf(appointment.getStartDateTime()));
ps.setTimestamp(7, Timestamp.valueOf(appointment.getEndDateTime()));
ps.setTimestamp(8, Timestamp.valueOf(LocalDateTime.now()));
ps.setString(9, UsersDao.getUserNameByID(appointment.getUsersID()));
ps.setInt(10, appointment.getCustomerID());
ps.setInt(11, appointment.getUsersID());
ps.setInt(12, appointment.getContactsID());
ps.setInt(13, appointment.getAppointmentID());
if(ps.execute()) {
AppointmentFormController.isValidated = true;
}
Here is the calling method:
public void addAppointmentClicked(ActionEvent event) throws SQLException {
Appointments scheduleAppointment = new Appointments();
fieldValidator(scheduleAppointment);
DateTimeFormatter hourAndMinuteFormat = DateTimeFormatter.ofPattern("HH:mm");
try {
LocalDate localAppointmentDate = afDatePicker.getValue();
LocalTime localEndTime = RelatedTime.formattedTimeParser(hourAndMinuteFormat, afEndTimePicker.getValue());
LocalTime localStartTime = RelatedTime.formattedTimeParser(hourAndMinuteFormat, afStartTimePicker.getValue());
LocalDateTime localDateTimeStartAppointment = LocalDateTime.of(localAppointmentDate, localStartTime);
LocalDateTime localDateTimeEndAppointment = LocalDateTime.of(localAppointmentDate, localEndTime);
scheduleAppointment.setStartDateTime(localDateTimeStartAppointment);
scheduleAppointment.setEndDateTime(localDateTimeEndAppointment);
} catch (NullPointerException e) {
System.out.println("Date or time selection is null");
}
try {
if (!isModified && fieldValidator() && compareAppointmentToBusiness(scheduleAppointment)) {
insertAppointmentIntoMap(scheduleAppointment.getAppointmentID(), scheduleAppointment); //is validated set false if sql exception caught
AppointmentsDao.insertAppointmentIntoDB(scheduleAppointment); //WHERE ERROR IS OCCURRING
isModified = false;
if (isValidated) {
closeSceneWindow();
}
}
} catch (SQLException e) {
e.printStackTrace();
System.out.println("There was an error inserting the appointment into the database");
}
try {
if (isModified && fieldValidator() && compareAppointmentToBusiness(scheduleAppointment)) {
insertAppointmentIntoMap(scheduleAppointment.getAppointmentID(), scheduleAppointment);
AppointmentsDao.updateAppointment(scheduleAppointment);
isModified = false;
if (isValidated) {
closeSceneWindow();
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
Contact Table
java.sql.SQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`client_schedule`.`appointments`, CONSTRAINT `fk_contact_id` FOREIGN KEY (`Contact_ID`) REFERENCES `contacts` (`Contact_ID`) ON DELETE CASCADE ON UPDATE CASCADE)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117)
I have tried to do first check that all the entered text fields that pertain to foreign keys are found within the database before executing the query.
if (UsersDao.getAllUsersObservableList().stream().
anyMatch(m -> Objects.equals(m.getUser_ID(), appointment.getUsersID())) &&
CustomerMainController.getAllCustomers().stream().
anyMatch(m -> Objects.equals(m.getCustomerID(), appointment.getCustomerID())) &&
ContactsDao.addContactToObservableList().stream().
anyMatch(m -> Objects.equals(m.getContactID(), appointment.getContactsID()))) {
if (ps.execute()) {
System.out.println("Successfully inserted appointment into database" +
"\nTime: " + LocalTime.now());
AppointmentFormController.isValidated = true;
}
} else {
AppointmentFormController.isValidated = false;
}
But when I did this I got this error:
java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '2' for key 'appointments.PRIMARY'
After I got this error I tried to remove SET Appointment_ID = ? from the query and also the prepared statement. I am pretty confused with whether its my query that is the issue or what is being checked that goes in it.
