CODEWITHSUNDEEP
javamysqljdbcsql2o
Skupaj
Skupaj

Reputation: 83

Fill foreign key in database based on auto increment from other table

I have two tables. The first table patienten has an auto increment id. One "patienten" has multiple "gebit". idPatient is the foreign key from the two tables.

patienten:

enter image description here

gebit:

enter image description here

Now i want to fill the "gebit" table but i keep getting errors.

My code:

public void addTandenToDatabase(int fdi, String voorstelling, String toestand) {
    String insertSql = "insert into gebit(fdi, voorstelling, toestand) values (:fdiVal, :voorstellingVal, :toestandVal)";
    try (Connection con = sql2o.open()) {
        con.setRollbackOnException(false);
        con.createQuery(insertSql)
                .addParameter("fdiVal", fdi)
                .addParameter("voorstellingVal", voorstelling)
                .addParameter("toestandVal", toestand)
                .executeUpdate();
    }
}

the error i get:

enter image description here

I tried a second method where i add a valu to idPatient in gebit:

public void addTandenToDatabase(int fdi, String voorstelling, String toestand) {
    String insertSql = "insert into gebit(idPatient, fdi, voorstelling, toestand) values (:idVal, :fdiVal, :voorstellingVal, :toestandVal)";
    try (Connection con = sql2o.open()) {
        con.setRollbackOnException(false);
        con.createQuery(insertSql)
                .addParameter("fdiVal", fdi)
                .addParameter("idVal", fdi)
                .addParameter("voorstellingVal", voorstelling)
                .addParameter("toestandVal", toestand)
                .executeUpdate();
    }
}

But that gives me this error:

Error in executeUpdate, Cannot add or update a child row: a foreign key constraint fails (`toondoesselaere`.`gebit`, CONSTRAINT `idPatient` FOREIGN KEY (`idPatient`) REFERENCES `patienten` (`idPatient`) ON DELETE NO ACTION ON UPDATE NO ACTION)

Upvotes: 0

Views: 841

Answers (1)

Lajos Arpad
Lajos Arpad

Reputation: 76426

You break a foreign key, as gebit.idPatient references patienten.idPatient and you try to insert a record into gebit with an idPatient which has no matching patienten.idPatient.

You need to review the idPatient you want to insert and see if that is incorrect; if so fix the bug resulting mistaken idPatient.

Upvotes: 1

Related Questions