You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Rick Hillegas (JIRA)" <ji...@apache.org> on 2008/12/02 18:28:44 UTC
[jira] Created: (DERBY-3968) Foreign keys accept the ON DELETE SET
DEFAULT clause but do not enforce it
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. I will attach a test cast showing the problem.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3968) Foreign keys accept the ON DELETE SET
DEFAULT clause but do not enforce it
Posted by "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 updated DERBY-3968:
---------------------------------
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.
was: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. I will attach a test cast showing the problem.
> 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.
[jira] Commented: (DERBY-3968) Foreign keys accept the ON DELETE
SET DEFAULT clause but do not enforce it
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (DERBY-3968) Foreign keys accept the ON DELETE
SET DEFAULT clause but do not enforce it
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3968?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12654450#action_12654450 ]
Kim Haase commented on DERBY-3968:
----------------------------------
If we forbid this syntax, should I close DERBY-3962 as invalid?
Thanks!
> 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.
[jira] Commented: (DERBY-3968) Foreign keys accept the ON DELETE
SET DEFAULT clause but do not enforce it
Posted by "Kim Haase (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Commented: (DERBY-3968) Foreign keys accept the ON DELETE
SET DEFAULT clause but do not enforce it
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3968?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12654491#action_12654491 ]
Rick Hillegas commented on DERBY-3968:
--------------------------------------
Thanks, Kim, I agree.
> 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.
[jira] Closed: (DERBY-3968) Foreign keys accept the ON DELETE SET
DEFAULT clause but do not enforce it
Posted by "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.
[jira] Commented: (DERBY-3968) Foreign keys accept the ON DELETE
SET DEFAULT clause but do not enforce it
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3968?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12654783#action_12654783 ]
Knut Anders Hatlen commented on DERBY-3968:
-------------------------------------------
Shouldn't we rather close this issue as a duplicate of DERBY-3963? They appear to be describing the same problem.
> 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.