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

Question

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:

gebit:

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:

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)

Does gebit has column called idVal? – Unknown May 26 '16 at 18:13 — Unknown, May 26 '16 at 18:13

score 1 · Accepted Answer · answered May 26 '16 at 18:20

1

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.

answered May 26 '16 at 18:20

Lajos Arpad

64,414
37
100
175

How do I make it match? – Skupaj May 26 '16 at 18:51
You need to make sure that whenever you insert a record, all its foreign keys reference existent values in the table it references to. – Lajos Arpad May 26 '16 at 23:14

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

1 Answers1