Referential action ON DELETE SET DEFAULT does not work correctly
----------------------------------------------------------------
Key: DERBY-3963
URL: https://issues.apache.org/jira/browse/DERBY-3963
Project: Derby
Issue Type: Bug
Reporter: Dag H. Wanvik
Attachments: SetDefaultOnDeleteError.java
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
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
