Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <700185041.1228828844390.JavaMail.jira@brutus>
Date: Tue, 9 Dec 2008 05:20:44 -0800 (PST)
From: "Rick Hillegas (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Closed: (DERBY-3968) Foreign keys accept the ON DELETE SET
 DEFAULT clause but do not enforce it
In-Reply-To: <1639770200.1228238924267.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


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