[jira] Closed: (DERBY-3968) Foreign keys accept the ON DELETE SET DEFAULT clause but do not enforce it

["Rick Hillegas (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-3968?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Rick Hillegas closed DERBY-3968.
--------------------------------

    Resolution: Duplicate

> 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.