0

I am receiving the error below when running the below sql statements on very simple tables. the only foreign key used here is in the table runs and the foreign key is runs.sample_id which references samples.id . I'm not sure why I am receiving this error as I am trying to delete all the runs before deleting the samples in which they depend on. Code below:

java code:

String deleteRunsQuery = "DELETE FROM runs"; //delete rows from runs table String deleteSamplesQuery = "DELETE FROM samples"; //delete rows from samples table

stmt.executeUpdate(deleteRunsQuery);

stmt.executeUpdate(deleteSamplesQuery);

and the exception:

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (gc_image_relational.runs, CONSTRAINT runs_ibfk_1 FOREIGN KEY (sample_id) REFERENCES samples (id)) at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) at java.lang.reflect.Constructor.newInstance(Unknown Source) at com.mysql.jdbc.Util.handleNewInstance(Util.java:400) at com.mysql.jdbc.Util.getInstance(Util.java:383) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:973) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2541) at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1604) at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1535) at com.gcimage.relational.RelationalDatabaseTest.disposeDb(RelationalDatabaseTest.java:40)

GregH
  • 5,125
  • 8
  • 55
  • 109
  • Run this query first `SET FOREIGN_KEY_CHECKS=0` after you are done set it to 1 – Mihai Dec 06 '15 at 19:13
  • how about cascade deletes, is that an option ? Turning on and off fk checks is a bit iffy. You either want integrity or you don't. So why pretend you have it (some of the time) – Drew Dec 06 '15 at 19:14
  • see [This Answer](http://stackoverflow.com/a/33838108) of mine, and stop reading pretty much after "good, the cascade delete deleted the 1 row here" – Drew Dec 06 '15 at 19:20

2 Answers2

2

Not enough rep to post a comment, I'm afraid. Maybe this may count as an answer anyway:

Your question is marked mysql, but you don't clearly state the programming language used; apparently it's Java. AFAIK Java works asynchronously, i.e. the engine doesn't wait for a statement to finish before executing the next. In this case it means that the second DELETE statement (on the samples table) will be triggered before the MySQL engine has finished DELETEing all the runs. Triggering the second DELETE in some sort of callback on the first would avoid this problem, or any other method to synchronize the two statements. Not sure how you'd do that, I'm not a Java programmer. Others may probably help you with that.

Aldo
  • 302
  • 1
  • 7
0

Check your trigger OnDelete. Probably you try to add the new record after deleting and got a key duplication error

Andrij Muravskyi
  • 1
  • 1