db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kim Haase (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3968) Foreign keys accept the ON DELETE SET DEFAULT clause but do not enforce it
Date Mon, 08 Dec 2008 17:54:44 GMT

    [ https://issues.apache.org/jira/browse/DERBY-3968?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12654483#action_12654483
] 

Kim Haase commented on DERBY-3968:
----------------------------------

Sorry, I forgot to mention DERBY-3963, which I believe would also need to be closed if this
syntax is forbidden.


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


Mime
View raw message