db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jacques Le Roux (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-6781) ErrorCode: 30000 when creating forein keys
Date Fri, 12 Dec 2014 11:07:13 GMT

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

Jacques Le Roux updated DERBY-6781:
-----------------------------------
    Attachment: FinAccountTransIndexes.png

> ErrorCode: 30000 when creating forein 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 3 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