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