[
https://issues.apache.org/jira/browse/DERBY-6781?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14245973#comment-14245973
]
Rick Hillegas commented on DERBY-6781:
--------------------------------------
Thanks for logging this issue. Derby requires an exact match in datatypes
between the primary and foreign key columns. The following script shows this:
{noformat}
connect 'jdbc:derby:memory:db;create=true';
create table primaryTable( a varchar( 20 ) primary key );
-- succeeds
create table sameKeySize( a varchar( 20 ) not null );
alter table sameKeySize
add constraint for2 foreign key( a ) references primaryTable( a );
-- fails
create table biggerKeySize( a varchar( 60 ) not null );
alter table biggerKeySize
add constraint for1 foreign key( a ) references primaryTable( a );
-- fails
create table smallerKeySize( a varchar( 10 ) not null );
alter table smallerKeySize
add constraint for3 foreign key( a ) references primaryTable( a );
{noformat}
I agree that this is overly strict. The column types of corresponding
primary/foreign columns should not have to match exactly. They only have to be
comparable, according to part 2 of the 2011 SQL Standard, section 11.8
(referential constraint definition), syntax rule 11.
I don't think that this is a difficult limitation to lift. I don't seen any
backward compatibility issues in relaxing this check. Thanks.
> ErrorCode: 30000 when creating Foreign Keys
> -------------------------------------------
>
> Key: DERBY-6781
> URL: https://issues.apache.org/jira/browse/DERBY-6781
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.11.1.1
> Environment: Windows 7-64 or Centos 6.4-64 on AWS running Apache
> OFBiz modified on Java 1.7
> Reporter: Jacques Le Roux
> Labels: engine, entity, ofbiz
> Attachments: FinAccountTrans.png, FinAccountTransIndexes.png,
> OrderItem.png, OrderItemIndex.png
>
>
> I'm an Apache OFBiz committer. While working on a custom project I
> encountered an issue I don't have with Postgres using the same data model
> (schema). This issue appears only in a specific case where I use a new
> feature I recently introduced in OFBiz.
> This feature allows to extend the size of a column part of a primary key. By
> extend I mean extend the out of the box OFBiz data model. For instance in
> OFBiz the size of the orderId column, that we find in many related tables (we
> call tables, entities in OFBiz), is 20 chars (VARCHAR(20)) by default, but I
> needed something larger (VARCHAR(60)). Also extend means here that it
> redefines the size of the column, it does not change the it in the out of the
> box OFBiz data model. This means, for instance, that I create what we call an
> hot-deploy component which is actually a mean to dynamically
> redefine/override things, like here the size of the column, without having to
> patch the original source where the original data model is defined.
> This works fine with Postgres but not with Derby. In Derby the indexes are
> created (see FIN_ACT_TX_ODITM FinAccountTransIndexes.png) but not the Foreign
> Keys (see FinAccountTrans.png)
> The OFBiz Entity Engine (the one which is still used in Jira), generates
> approximately the same code for the Foreign Keys creation for Postgres and
> Derby
> Postgres shows no error when generating the Foreign Keys, it uses (generated
> by the OFBiz Entity Engine)
> {code}
> ALTER TABLE fin_account_trans ADD CONSTRAINT fin_act_tx_oditm FOREIGN KEY
> (order_id, order_item_seq_id) REFERENCES order_item (order_id,
> order_item_seq_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION;
> {code}
> Derby shows an error, it uses (for easier comparison I removed the "OFBIZ."
> schema prefix that Derby uses)
> {code}
> ALTER TABLE FIN_ACCOUNT_TRANS ADD CONSTRAINT FIN_ACT_TX_ODITM FOREIGN KEY
> (ORDER_ID, ORDER_ITEM_SEQ_ID) REFERENCES ORDER_ITEM (ORDER_ID,
> ORDER_ITEM_SEQ_ID);
> Error: Constraint 'FIN_ACT_TX_ODITM' is invalid: there is no unique or
> primary key constraint on table '"OFBIZ"."ORDER_ITEM"' that matches the
> number and types of the columns in the foreign key.
> SQLState: X0Y44
> ErrorCode: 30000
> {code}
> As I said, only the foreign keys miss in Derby DB; the indexes related to the
> (Constraint 'FIN_ACT_TX_ODITM') are generated.
> I also tried to remove the last part of the SQL request, but got the same
> kind of error:
> {code}
> ALTER TABLE FIN_ACCOUNT_TRANS ADD CONSTRAINT FIN_ACT_TX_ODITM FOREIGN KEY
> (ORDER_ID, ORDER_ITEM_SEQ_ID) REFERENCES ORDER_ITEM;
> Error: Constraint 'FIN_ACT_TX_ODITM' is invalid: the types of the foreign key
> columns do not match the types of the referenced columns.
> SQLState: X0Y42
> ErrorCode: 30000
> {code}
> I tried to understant the issue, but I'm puzzled because I'm sure it's not an
> Entity Engine SQL generation issue, since Postgres works w/o issues. Could it
> be related with how and when Derby generate its specific "SQL-data-time"
> indexes?
> Since I can't help much for now, I attach also 2 other snapshots which show
> ORDER_ITEM table columns (OrderItem.png) and indexes (OrderItemIndex.png).
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
