Java DB2 SQL-Error: -803 插入两个相关表时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21479931/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
DB2 SQL-Error: -803 when inserting into two related tables
提问by stevecross
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 behandeltI have to insert into Behandlungsvorgangto fulfill the foreign key requirements. The method looks like this:
我有一个方法可以将数据插入到这个表中。它总是插入新数据，所以在插入之前behandelt我必须插入Behandlungsvorgang以满足外键要求。该方法如下所示：
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:
我总是得到一个例外说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。如果我手动插入数据，我不会出错。例如：
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?
我在java代码中做错了什么？
采纳答案by stevecross
I just searched for the error in he wrong table. behandeltinstead of Behandlungsvorgang. I always tried to insert the same date. Thus i violated the primary key constraint of this table.
我只是在他错误的表中搜索错误。behandelt而不是Behandlungsvorgang. 我总是试图插入相同的日期。因此，我违反了该表的主键约束。
回答by bhamby
-803means 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 SQLERRMCand apply it to this query to get the index you're violating:
-803意味着您正在尝试插入违反表的唯一约束的行。如果您使用的是 DB2 Linux/Unix/Windows，则可以从中获取该数字SQLERRMC并将其应用于此查询以获取您违反的索引：
SELECT INDNAME, INDSCHEMA
FROM SYSCAT.INDEXES
WHERE IID = <index-id>
AND TABSCHEMA = 'schema'
AND TABNAME = 'table'
