Problem with deleting data from DB using JAVA-CodePudding

I've got prepared sql file with a lots of lines. When I try to execute folowing lines directly on my DB it's OK

DELETE FROM Edges WHERE ((ed_op_code=20)AND(ed_tt_valid_from="2020-12-13")AND(ed_dep_sn_code=4397)AND(ed_cc_code=1)AND(ed_dest_sn_code=20008));
DELETE FROM Edges WHERE ((ed_op_code=20)AND(ed_tt_valid_from="2020-12-13")AND(ed_dep_sn_code=20005)AND(ed_cc_code=1)AND(ed_dest_sn_code=20007));
DELETE FROM Edges WHERE ((ed_op_code=20)AND(ed_tt_valid_from="2020-12-13")AND(ed_dep_sn_code=20006)AND(ed_cc_code=1)AND(ed_dest_sn_code=20007));
DELETE FROM Edges WHERE ((ed_op_code=20)AND(ed_tt_valid_from="2020-12-13")AND(ed_dep_sn_code=20006)AND(ed_cc_code=1)AND(ed_dest_sn_code=20008));
DELETE FROM Edges WHERE ((ed_op_code=20)AND(ed_tt_valid_from="2020-12-13")AND(ed_dep_sn_code=20007)AND(ed_cc_code=1)AND(ed_dest_sn_code=20005));
DELETE FROM Edges WHERE ((ed_op_code=20)AND(ed_tt_valid_from="2020-12-13")AND(ed_dep_sn_code=20007)AND(ed_cc_code=1)AND(ed_dest_sn_code=20006));
DELETE FROM Edges WHERE ((ed_op_code=20)AND(ed_tt_valid_from="2020-12-13")AND(ed_dep_sn_code=20008)AND(ed_cc_code=1)AND(ed_dest_sn_code=4397));
DELETE FROM Edges WHERE ((ed_op_code=20)AND(ed_tt_valid_from="2020-12-13")AND(ed_dep_sn_code=20008)AND(ed_cc_code=1)AND(ed_dest_sn_code=20006));
DELETE FROM StationRefs WHERE ((sn_op_code=20)AND(sn_tt_valid_from="2020-12-13")AND(sn_code=20005));
DELETE FROM StationRefs WHERE ((sn_op_code=20)AND(sn_tt_valid_from="2020-12-13")AND(sn_code=20006));
DELETE FROM StationRefs WHERE ((sn_op_code=20)AND(sn_tt_valid_from="2020-12-13")AND(sn_code=20007));
DELETE FROM StationRefs WHERE ((sn_op_code=20)AND(sn_tt_valid_from="2020-12-13")AND(sn_code=20008));

However, when I'm iterating over this sql file in my app I got following error:

Abort due to constraint violation (foreign key constraint failed)

What can be the cause if executed directly on DB is working just fine? The DB is SQLite

CodePudding user response：

What can be the cause if executed directly on DB is working just fine? The DB is SQLite

Transactions, probably.

The concept of constraints is not part of the SQL standard. Let alone the concept of how the DB engine deals with constraint violations.

You have constraints here: You have told the DB engine that some column in some table is a 'foreign key': It is referencing a column in some other table. For example, if I have a table of countries, and a table of continents, I might have a row in my continent table representing europe, and then my row in the country table representing france has continent=18, where 18 is the id of the europe row. That column (the continent column) is a foreign key, and most DBs allow you to tell the DB about it. The DB will then aggressively police this, and will simply avoid the situation that you have a country that is 'referencing' a non-existing continent.

So it is here. That is what this error means.

Some DB engines, such as postgres, lets you define precisely which behaviour you want for constraint checking. But, many adhere to the rule of end-of-transaction (and e.g. postgres lets you configure constraints such that they work like this): You can break constraints all you want, and only at the end of the transaction are they checked. If you fail zero constraints, the transaction can go through. But if one or more are broken, the COMMIT; fails.

One explanation for why this works 'in the DB' and not 'in your java code' is that the DB is treating the whole thing as one transaction and your java code 'has no transactions' (which is really just short for 'commits the transaction after every statement').

DBs usually also let you flat out disable constraint checking entirely. This is a bad idea, but, it's there. Perhaps you did that in the DB environment.

To run your statements in a single transaction, start off with .setAutoCommit(false), then run all your con.execute() lines, and then at the end, run con.commit() to close the transaction.

SQLite

Unless you're on android or are working with that DB from non-java processes this is a bad idea. SQLite is not actually 'lite' in a java environment; instead, use an all-java in-process solution such as h2. SQLite requires shipping a million binaries (one for each platform), for example. The SQLite JDBC driver does a great job as making it as easy as possible, but if you started off with 'I have a java app and I need a file-based DB engine that is simple and light weight', then 'sqlite' was the wrong answer, just so you know.