[DERBY-3963] Referential action ON DELETE SET DEFAULT does not work correctly

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1
• Component/s: SQL
• Labels: derby_triage10_5_2
• Urgency: Normal
• Issue & fix info: Repro attached
• Assignee: Unassigned
• Reporter: Dag H. Wanvik
• Created: 25/Nov/08 17:02
• Updated: 15/Feb/11 21:08

Description

The ON DELETE SET DEFAULT action is not documented (cf. DERBY-3962), but Derby accepts the construct.
Example from JUnit repro SetDefaultOnDeleteError.java:

> create table dhw( a int default 2, constraint y foreign key(a) references referencedTable(a) on delete set default)

However, it does not work as expected. It would seem that when the referenced table's row is deleted,
the constraint is checked with the old value of the referencing table (DHW.A) instead of the value
after updating the column with the default as required. After updating to the default value the new value should
be checked again against the referenced table, and if there is a row corresponding to the default value, the
operation should succeed. Derby fails with error:

ERROR 23503: DELETE on table 'REFERENCEDTABLE' caused a violation of foreign key constraint 'YYY' for key (1). The statement has been rolled back

Stacktrace:

1) test_1(org.apache.derbyTesting.functionTests.tests.lang.SetDefaultOnDeleteError)java.sql.SQLIntegrityConstraintViolationException: DELETE on table 'REFERENCEDTABLE' caused a violation of foreign key constraint 'YYY' for key (1). The statement has been rolled back.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:81)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:201)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:391)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2201)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1323)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:625)
at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(EmbedStatement.java:175)
at org.apache.derbyTesting.functionTests.tests.lang.SetDefaultOnDeleteError.test_1(SetDefaultOnDeleteError.java:58)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:105)
at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
at junit.extensions.TestSetup.run(TestSetup.java:25)
at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
at junit.extensions.TestSetup.run(TestSetup.java:25)
at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
Caused by: java.sql.SQLException: DELETE on table 'REFERENCEDTABLE' caused a violation of foreign key constraint 'YYY' for key (1). The statement has been rolled back.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:119)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:70)
... 37 more
Caused by: ERROR 23503: DELETE on table 'REFERENCEDTABLE' caused a violation of foreign key constraint 'YYY' for key (1). The statement has been rolled back.
at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:373)
at org.apache.derby.impl.sql.execute.ReferencedKeyRIChecker.doCheck(ReferencedKeyRIChecker.java:108)
at org.apache.derby.impl.sql.execute.RISetChecker.doPKCheck(RISetChecker.java:97)
at org.apache.derby.impl.sql.execute.DeleteResultSet.collectAffectedRows(DeleteResultSet.java:393)
at org.apache.derby.impl.sql.execute.DeleteResultSet.open(DeleteResultSet.java:137)
at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:416)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:297)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1235)
... 31 more

Issue Links

• is duplicated by: DERBY-3968 Foreign keys accept the ON DELETE SET DEFAULT clause but do not enforce it
• relates to: DERBY-3962 Foreign keys: ON DELETE SET DEFAULT possibility not mentioned in docs