Problem with backing indices for foreign keys
---------------------------------------------
Key: DDLUTILS-169
URL: https://issues.apache.org/jira/browse/DDLUTILS-169
Project: DdlUtils
Issue Type: Bug
Components: Core - Derby/Cloudscape/JavaDB
Environment: Derby 10.2 on Windows
Reporter: Stefan Huber
Assigned To: Thomas Dudziak
I created the following model in an empty derby db:
<?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database";>
<database name="rmdb">
<table name="MasterA">
<column name="ID" primaryKey="true" required="true" type="INTEGER"/>
</table>
<table name="MasterB">
<column name="ID" primaryKey="true" required="true" type="INTEGER"/>
</table>
<table name="Detail">
<column name="A_FK" primaryKey="true" required="true" type="INTEGER"/>
<column name="B_FK" primaryKey="true" required="true" type="INTEGER"/>
<foreign-key name="FK_A" foreignTable="MasterA">
<reference local="A_FK" foreign="ID"/>
</foreign-key>
<foreign-key name="FK_B" foreignTable="MasterB">
<reference local="B_FK" foreign="ID"/>
</foreign-key>
</table>
</database>
This worked fine.
But when i tried to update the model with the same definition (e.g. via an ant
task with alterdatabase="true") i got this error messages:
[ddlToDatabase] Index SQL070418040159390 needs to be removed from table DETAIL
[ddlToDatabase] Index SQL070418040159510 needs to be removed from table DETAIL
[ddlToDatabase] SQL Command DROP INDEX SQL070418040159390 failed with: Der
Index 'SQL070418040159390' wurde erstellt, um die Integritätsbedingung 'FK_A'
durchzusetzen. Der Index kann nur gelöscht werden, wenn die
Integritätsbedingung gelöscht wird.
[ddlToDatabase] SQL Command DROP INDEX SQL070418040159510 failed with: Der
Index 'SQL070418040159510' wurde erstellt, um die Integritätsbedingung 'FK_B'
durchzusetzen. Der Index kann nur gelöscht werden, wenn die
Integritätsbedingung gelöscht wird.
[ddlToDatabase] Executed 2 SQL command(s) with 2 error(s)
There seems to be a problem with foreign keys that refer to a primary key
column in the local table. If this column is only a part of the primary key a
new index is created for this single column. The alter database task tries to
delete this index which results in an error.
If the local column is not part of a primary key or the primary key consists of
this column only the alter database task works fine.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
