Mark Struberg created OPENJPA-2795:
--------------------------------------
Summary: 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: 3.1.0, 2.4.3
Reporter: Mark Struberg
Assignee: Mark Struberg
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.2#803003)
