[jira] [Closed] (DERBY-6781) ErrorCode: 30000 when creating Foreign Keys

["Jacques Le Roux (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-6781?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Jacques Le Roux closed DERBY-6781.
----------------------------------
    Resolution: Abandoned

> 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
>            Priority: Major
>              Labels: engine, entity, ofbiz
>         Attachments: FinAccountTrans.png, FinAccountTransColumns.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 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
(v8.20.1#820001)