CODEWITHSUNDEEP
javadb2
stevecross
stevecross

Reputation: 5684

DB2 SQL-Error: -803 when inserting into two related tables

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?

Upvotes: 5

Views: 61699

Answers (2)

stevecross
stevecross

Reputation: 5684

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.

Upvotes: 2

bhamby
bhamby

Reputation: 15450

-803 means that you're trying to insert a row that violates the Unique Constraints of the table. If you're on DB2 Linux/Unix/Windows, you can take that number from SQLERRMC and apply it to this query to get the index you're violating:

SELECT INDNAME, INDSCHEMA
FROM SYSCAT.INDEXES
WHERE IID = <index-id>
  AND TABSCHEMA = 'schema'
  AND TABNAME = 'table'

Upvotes: 16

Related Questions