[
https://issues.apache.org/jira/browse/DERBY-6781?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jacques Le Roux updated DERBY-6781:
-----------------------------------
Description:
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).
was:
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).
> 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 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)
