[DERBY-3968] Foreign keys accept the ON DELETE SET DEFAULT clause but do not enforce it

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 10.4.2.0, 10.5.1.1
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Richard N. Hillegas
• Created: 02/Dec/08 17:26
• Updated: 04/May/09 18:22
• Resolved: 09/Dec/08 13:20

Description

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.

Issue Links

• duplicates: DERBY-3963 Referential action ON DELETE SET DEFAULT does not work correctly