[
https://issues.apache.org/jira/browse/OPENJPA-2795?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17152067#comment-17152067
]
Robert Mayer commented on OPENJPA-2795:
---------------------------------------
The attached patch (against 2.4.X) hopefully fixes OPENJPA-2795 in that
generation of indizes for @ManyToOne relations that are also foreign keys
can optionally be turned on if so desired. Default behaviour of OpenJPA is
unchanged.
This patch contains two new features:
1) A new DB-specific flag DBDictionary#indexPhysicalForeignKeys so that indices
for foreign keys will be generated for
database systems that don't automatically create an index for foreign keys.
2) A new boolean property MappingDefaults.IndexPhysicalForeignKeys that will
turn the feature from 1) on or off.
By default MappingDefaults.IndexPhysicalForeignKeys is false so that the
feature from 1) is disabled.
Note: DBDictionary#indexPhysicalForeignKeys works similar to the pre-existing
flag DBDictionary#indexLogicalForeignKeys.
Note: this commit enables FK indices for Oracle and MS SQLServer. Other
database systems may benefit, too, and should also be changed.
Cheers, Robert.
[^OPENJPA-2795.patch]
> generate foreign key indexes for Oracle
> ---------------------------------------
>
> Key: OPENJPA-2795
> URL: https://issues.apache.org/jira/browse/OPENJPA-2795
> Project: OpenJPA
> Issue Type: Bug
> Components: jdbc
> Affects Versions: 2.4.3, 3.1.0
> Reporter: Mark Struberg
> Assignee: Mark Struberg
> Priority: Major
> Attachments: OPENJPA-2795.patch
>
>
> When generating a schema SQL via OpenJPA then we do omit the CREATE INDEX if
> there is a Forein Key CONSTRAINT generated.
> For the following please consider an Entity {{CarLicensePlate}} which has a
> {{@ManyToOne}} on {{Customer}}.
> Without giving any further generator options we generate the correct CREATE
> INDEX statement:
> {noformat}
> CREATE TABLE CUSTOMER (ID NUMBER NOT NULL, active VARCHAR(1), name
> VARCHAR2(255), PRIMARY KEY (ID));
> CREATE TABLE LIC_PLATE (id VARCHAR2(255) NOT NULL, maker VARCHAR2(255), model
> VARCHAR2(255), optLock NUMBER, CUSTOMER_ID NUMBER, PRIMARY KEY (id));
> CREATE INDEX I_LIC_PLT_CUSTOMER ON LIC_PLATE (CUSTOMER_ID);
> {noformat}
> But once we switch on explicit foreign key constraints via
> {noformat}
> <MappingDefaults>ForeignKeyDeleteAction=restrict,
> JoinForeignKeyDeleteAction=restrict</MappingDefaults>
> {noformat}
> then the index is omitted.
> {noformat}
> CREATE TABLE CUSTOMER (ID NUMBER NOT NULL, active VARCHAR(1), name
> VARCHAR2(255), PRIMARY KEY (ID));
> CREATE TABLE LIC_PLATE (id VARCHAR2(255) NOT NULL, maker VARCHAR2(255), model
> VARCHAR2(255), optLock NUMBER, CUSTOMER_ID NUMBER, PRIMARY KEY (id));
> ALTER TABLE LIC_PLATE ADD FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (ID)
> DEFERRABLE;
> {noformat}
> This is ok for most databases as a foreign key constraint they usually
> automatically internally create an index as well. But sadly this is not the
> case for Oracle. Here we would actually need both, the constraint and the
> index.
> There is even an own switch {{IndexLogicalForeignKeys}} in
> {{MappingDefaults}}. But this doesn't really do what we need right now in
> this case. It's more a switch to disable index creating even if there is no
> constraints.
> We could either introduce a new flag in MappingDefaults or change the
> IndexLogicalForeignKeys from boolean to Boolean or even an enum with a
> {{FORCED}}.
> Even better would be a DbDictionary specific handling. That way we could
> create the index automatically for Oracle while skipping it for others.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
