foreign key always stays null

Question

I need a bit of assistance in getting a connection between my two tables

These are the tables where "idPatient is the foreign key"

I fill the table "tanden" like this

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

Everything is added nicely but the idPatient stays null

idPatient is not included among values of your INSERT query. You don't specify it. Are you expecting to retrieve it automatically someway? — Andrea, May 27 '16 at 08:42
You need to have a patienten inserted at least. Then, knowing its id, use it as value for idPatient's column in tanden. — Andrea, May 27 '16 at 08:46

score 4 · Accepted Answer · answered May 27 '16 at 08:49

4

You should include idPatient in your insert if you want to set value to it. 'foreign key' doesnot mean that it will be set value automically.

answered May 27 '16 at 08:49

Kumar Paraj

76
2

if i try to insert somthing in a foreign key i get this: Exception in thread "AWT-EventQueue-0" org.sql2o.Sql2oException: Error in executeUpdate, Cannot add or update a child row: a foreign key constraint fails (`toondoesselaere`.`tanden`, CONSTRAINT `id` FOREIGN KEY (`id`) REFERENCES `patienten` (`idPatient`) ON DELETE NO ACTION ON UPDATE NO ACTION) – Skupaj May 27 '16 at 08:53

score 1 · Answer 2 · answered May 27 '16 at 08:45

1

You have to insert idPatient column value into tanden table by taking from patienten table.

answered May 27 '16 at 08:45

myinterviewexp.com

42
4

score 1 · Answer 3 · answered May 27 '16 at 08:49

Your id column in the tanden table should be set as primary key and autoincrement and you have to set the idPatient in your insert

insert into tanden(idPatient, fdi, voorstelling, toestand) values(:idVal,:fdiVal, :voorstellingVal, :toestandVal)";

(The idPatient you set in the child table already has to exist in the parent table

foreign key always stays null

3 Answers3