DB2 SQL-Error: -803 when inserting into two related tables
db2, java
Solution
I just searched for the error in he wrong table. `behandelt` instead of `Behandlungsvorgang`. I always tried to insert the same date. Thus i violated the primary key constraint of this table.
Problem
I have two tables which are created with this statements: ``` CREATE TABLE Behandlungsvorgang ( patientId SMALLINT NOT NULL REFERENCES Patient(id), datum DATE NOT NULL, notizen VARCHAR(100), PRIMARY KEY (patientId, datum) ); CREATE TABLE behandelt ( arztLogin VARCHAR(50) NOT NULL REFERENCES Arzt(login), behandlungsDatum DATE NOT NULL, behandlungsPatientId SMALLINT NOT NULL, medikamntPzn SMALLINT NOT NULL REFERENCES Medikament(pzn), krankheitName VARCHAR(50) NOT NULL REFERENCES Krankheit(name), PRIMARY KEY (arztLogin, behandlungsDatum, behandlungsPatientId, medikamntPzn, krankheitName), FOREIGN KEY (behandlungsDatum, behandlungsPatientId) REFERENCES Behandlungsvorgang(datum, patientId) ); ``` And I have a method which should insert data into this tables. It always inserts new data so before inserting into `behandelt` I have to insert into `Behandlungsvorgang` to fulfill the foreign key requirements. The method looks like this: ``` public void add(TreatmentProcess tp) throws StoreException { try { PreparedStatement psBehandlungsvorgang = connection.prepareStatement("INSERT INTO Behandlungsvorgang (patientId, datum, notizen) VALUES (?, ?, ?)"); psBehandlungsvorgang.setInt(1, tp.getPatientId()); psBehandlungsvorgang.setDate(2, tp.getDate()); psBehandlungsvorgang.setString(3, tp.getNotes()); psBehandlungsvorgang.executeUpdate(); PreparedStatement psBehandelt = connection.prepareStatement("INSERT INTO behandelt (arztLogin, behandlungsDatum, behandlungsPatientId, medikamntPzn, krankheitName) VALUES (?, ?, ?, ?, ?)"); for (Drug drug : tp.getDrugs()) { psBehandelt.setString(1, tp.getDoctor()); psBehandelt.setDate(2, tp.getDate()); psBehandelt.setInt(3, tp.getPatientId()); psBehandelt.setInt(4, drug.getPzn()); psBehandelt.setString(5, tp.getDisease()); psBehandelt.addBatch(); } psBehandelt.executeBatch(); } catch (SQLException e) { throw new StoreException(e); } } ``` I always get an exception saying `de.unidue.inf.is.stores.StoreException: com.ibm.db2.jcc.am.go: DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505, SQLERRMC=1;DBP10.BEHANDLUNGSVORGANG, DRIVER=4.7.85`. If I insert data manually I get no errors. For example: ``` insert into Behandlungsvorgang values (1, '2014-01-25', 'Test'); insert into behandelt values ('doc', '2014-01-25', 1, 1, 'Kater'); insert into behandelt values ('doc', '2014-01-25', 1, 2, 'Kater'); ``` What am I doing wrong in the java code?