[
https://issues.apache.org/jira/browse/DERBY-3968?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12654454#action_12654454
]
Rick Hillegas commented on DERBY-3968:
--------------------------------------
Hi Kim,
Yes, I think that if we forbid this syntax, then we should close DERBY-3962 as
invalid.
> Foreign keys accept the ON DELETE SET DEFAULT clause but do not enforce it
> --------------------------------------------------------------------------
>
> Key: DERBY-3968
> URL: https://issues.apache.org/jira/browse/DERBY-3968
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.4.2.0, 10.5.0.0
> Reporter: Rick Hillegas
>
> The Derby Reference Guide does not document that you can declare a foreign
> key with ON DELETE SET DEFAULT. However, Derby accepts this syntax. There are
> no tests for this clause and it does not seem to work. A simple solution
> would be to forbid this syntax. The following test case shows this problem:
> ij> drop table t_dhw_4;
> 0 rows inserted/updated/deleted
> ij> drop table t_dhw_3;
> 0 rows inserted/updated/deleted
> ij> create table t_dhw_3( a int primary key );
> 0 rows inserted/updated/deleted
> ij> create table t_dhw_4( a int references t_dhw_3( a ) on delete set default
> default 2, b int );
> 0 rows inserted/updated/deleted
> ij> insert into t_dhw_3( a ) values ( 1 ), ( 2 );
> 2 rows inserted/updated/deleted
> ij> insert into t_dhw_4( a ) values( 1 );
> 1 row inserted/updated/deleted
> ij> insert into t_dhw_4( b ) values( 200 );
> 1 row inserted/updated/deleted
> ij> select * from t_dhw_3 order by a;
> A
> -----------
> 1
> 2
> 2 rows selected
> ij> select * from t_dhw_4 order by a;
> A |B
> -----------------------
> 1 |NULL
> 2 |200
> 2 rows selected
> ij> delete from t_dhw_3 where a = 1;
> ERROR 23503: DELETE on table 'T_DHW_3' caused a violation of foreign key
> constraint 'SQL081202092741910' for key (1). The statement has been rolled
> back.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
