[jira] Updated: (DDLUTILS-169) Problem with backing indices for foreign keys

["Thomas Dudziak (JIRA)" <ji...@apache.org>]

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

Thomas Dudziak updated DDLUTILS-169:
------------------------------------

    Fix Version/s: 1.0

> 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
>             Fix For: 1.0
>
>
> 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.