db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] Created: (DERBY-3963) Referential action ON DELETE SET DEFAULT does not work correctly
Date Tue, 25 Nov 2008 17:03:44 GMT
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.


Mime
View raw message