db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6781) ErrorCode: 30000 when creating Foreign Keys
Date Sun, 14 Dec 2014 16:34:13 GMT

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

Mime
View raw message