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 "Unai Vivi (JIRA)" <ji...@apache.org> on 2011/03/09 19:29:00 UTC
[jira] Created: (DERBY-5118) Relational integrity
Relational integrity
--------------------
Key: DERBY-5118
URL: https://issues.apache.org/jira/browse/DERBY-5118
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.7.1.1
Environment: WinXPpro@32bit, Apache Derby EMBEDDED
Reporter: Unai Vivi
Priority: Minor
A foreign composite key constraint doesn't match the corresponding primary composite key unless the order of the columns that make up the composite key is the same.
E.g.:
Given a table called "icone" with the following primary key: "CONSTRAINT icone_PK PRIMARY KEY(seticone,idazione)".
"CONSTRAINT BOTTONE_FK3 foreign key(seticone,idazione) references icone on delete cascade"
and
"CONSTRAINT BOTTONE_FK3 foreign key(idazione,seticone) references icone on delete cascade"
should be the same (IMHO) but it's not the same according to Derby.
The latter syntax is apparently broken and when inserting a new row, in the table that has such constraint, there is an error because Derby swaps the two attributes that make up the composite key and thus the FK constraint cannot be fulfilled.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Resolved: (DERBY-5118) Relational integrity
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5118?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas resolved DERBY-5118.
----------------------------------
Resolution: Not A Problem
Bug behavior facts: (was: [Wrong query result, Deviation from standard])
> Relational integrity
> --------------------
>
> Key: DERBY-5118
> URL: https://issues.apache.org/jira/browse/DERBY-5118
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.7.1.1
> Environment: WinXPpro@32bit, Apache Derby EMBEDDED
> Reporter: Unai Vivi
> Priority: Minor
> Labels: keys
>
> A foreign composite key constraint doesn't match the corresponding primary composite key unless the order of the columns that make up the composite key is the same.
> E.g.:
> Given a table called "icone" with the following primary key: "CONSTRAINT icone_PK PRIMARY KEY(seticone,idazione)".
> "CONSTRAINT BOTTONE_FK3 foreign key(seticone,idazione) references icone on delete cascade"
> and
> "CONSTRAINT BOTTONE_FK3 foreign key(idazione,seticone) references icone on delete cascade"
> should be the same thing (IMHO) but it's not the same according to Derby.
> The latter syntax is apparently broken and when inserting a new row, in the table that has such constraint, there is an error because Derby swaps the two attributes that make up the composite key and thus the FK constraint cannot be fulfilled.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-5118) Relational integrity
Posted by "Unai Vivi (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5118?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13005019#comment-13005019 ]
Unai Vivi commented on DERBY-5118:
----------------------------------
You are right, Rick. I didn't read through the SQL2008 standard because I (mistakenly) assumed that SQL couldn't have been designed that way for I found that very counter-intuitive. Thank you for your insightful comment.
Cheers,
Unai
> Relational integrity
> --------------------
>
> Key: DERBY-5118
> URL: https://issues.apache.org/jira/browse/DERBY-5118
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.7.1.1
> Environment: WinXPpro@32bit, Apache Derby EMBEDDED
> Reporter: Unai Vivi
> Priority: Minor
> Labels: keys
>
> A foreign composite key constraint doesn't match the corresponding primary composite key unless the order of the columns that make up the composite key is the same.
> E.g.:
> Given a table called "icone" with the following primary key: "CONSTRAINT icone_PK PRIMARY KEY(seticone,idazione)".
> "CONSTRAINT BOTTONE_FK3 foreign key(seticone,idazione) references icone on delete cascade"
> and
> "CONSTRAINT BOTTONE_FK3 foreign key(idazione,seticone) references icone on delete cascade"
> should be the same thing (IMHO) but it's not the same according to Derby.
> The latter syntax is apparently broken and when inserting a new row, in the table that has such constraint, there is an error because Derby swaps the two attributes that make up the composite key and thus the FK constraint cannot be fulfilled.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-5118) Relational integrity
Posted by "Unai Vivi (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5118?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Unai Vivi updated DERBY-5118:
-----------------------------
Description:
A foreign composite key constraint doesn't match the corresponding primary composite key unless the order of the columns that make up the composite key is the same.
E.g.:
Given a table called "icone" with the following primary key: "CONSTRAINT icone_PK PRIMARY KEY(seticone,idazione)".
"CONSTRAINT BOTTONE_FK3 foreign key(seticone,idazione) references icone on delete cascade"
and
"CONSTRAINT BOTTONE_FK3 foreign key(idazione,seticone) references icone on delete cascade"
should be the same thing (IMHO) but it's not the same according to Derby.
The latter syntax is apparently broken and when inserting a new row, in the table that has such constraint, there is an error because Derby swaps the two attributes that make up the composite key and thus the FK constraint cannot be fulfilled.
was:
A foreign composite key constraint doesn't match the corresponding primary composite key unless the order of the columns that make up the composite key is the same.
E.g.:
Given a table called "icone" with the following primary key: "CONSTRAINT icone_PK PRIMARY KEY(seticone,idazione)".
"CONSTRAINT BOTTONE_FK3 foreign key(seticone,idazione) references icone on delete cascade"
and
"CONSTRAINT BOTTONE_FK3 foreign key(idazione,seticone) references icone on delete cascade"
should be the same (IMHO) but it's not the same according to Derby.
The latter syntax is apparently broken and when inserting a new row, in the table that has such constraint, there is an error because Derby swaps the two attributes that make up the composite key and thus the FK constraint cannot be fulfilled.
Labels: keys (was: )
> Relational integrity
> --------------------
>
> Key: DERBY-5118
> URL: https://issues.apache.org/jira/browse/DERBY-5118
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.7.1.1
> Environment: WinXPpro@32bit, Apache Derby EMBEDDED
> Reporter: Unai Vivi
> Priority: Minor
> Labels: keys
>
> A foreign composite key constraint doesn't match the corresponding primary composite key unless the order of the columns that make up the composite key is the same.
> E.g.:
> Given a table called "icone" with the following primary key: "CONSTRAINT icone_PK PRIMARY KEY(seticone,idazione)".
> "CONSTRAINT BOTTONE_FK3 foreign key(seticone,idazione) references icone on delete cascade"
> and
> "CONSTRAINT BOTTONE_FK3 foreign key(idazione,seticone) references icone on delete cascade"
> should be the same thing (IMHO) but it's not the same according to Derby.
> The latter syntax is apparently broken and when inserting a new row, in the table that has such constraint, there is an error because Derby swaps the two attributes that make up the composite key and thus the FK constraint cannot be fulfilled.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-5118) Relational integrity
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5118?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13004734#comment-13004734 ]
Rick Hillegas commented on DERBY-5118:
--------------------------------------
Those are two different foreign keys according to my reading of the 2008 SQL Standard. According to part 2, section 4.17.3 (table constraints): "The ordering of the lists of referencing column names and referenced column names is implementation-defined, but shall be such that corresponding column names occupy corresponding positions in each list. "
> Relational integrity
> --------------------
>
> Key: DERBY-5118
> URL: https://issues.apache.org/jira/browse/DERBY-5118
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.7.1.1
> Environment: WinXPpro@32bit, Apache Derby EMBEDDED
> Reporter: Unai Vivi
> Priority: Minor
> Labels: keys
>
> A foreign composite key constraint doesn't match the corresponding primary composite key unless the order of the columns that make up the composite key is the same.
> E.g.:
> Given a table called "icone" with the following primary key: "CONSTRAINT icone_PK PRIMARY KEY(seticone,idazione)".
> "CONSTRAINT BOTTONE_FK3 foreign key(seticone,idazione) references icone on delete cascade"
> and
> "CONSTRAINT BOTTONE_FK3 foreign key(idazione,seticone) references icone on delete cascade"
> should be the same thing (IMHO) but it's not the same according to Derby.
> The latter syntax is apparently broken and when inserting a new row, in the table that has such constraint, there is an error because Derby swaps the two attributes that make up the composite key and thus the FK constraint cannot be fulfilled.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira