db-ddlutils-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thomas Dudziak" <tom...@gmail.com>
Subject Re: SQL Exception on trying to add some foreign keys constraints with DdlUtils
Date Thu, 24 May 2007 00:58:02 GMT
On 5/23/07, Ricardo Palomares <rpmdisguise-java@yahoo.es> wrote:

> This is my first incursion in this list. I've been searching a bit on
> DdlUtils docs but I can't find the solution. It is probably just
> something related to the underlying database (Derby), but after
> browsing Derby docs I don't get it either.
>
> Anyway, I'm using DdlUtils to keep updated the database schema of a
> desktop application without me or my users having to run SQL commands.
>
> I have recently added a new table ("VERSIONS") with this definition:
>
> <table name="VERSIONS">
>   <column name="ID" primaryKey="true" required="true" type="INTEGER"
>    size="10" autoIncrement="true" />
>   <column name="L10NCODE" primaryKey="false" required="true"
>    type="VARCHAR" size="10" autoIncrement="false" />
>   <column name="PRODCODE" primaryKey="false" required="true"
>    type="VARCHAR" size="16" autoIncrement="false" />
>   <column name="VERSCODE" primaryKey="false" required="true"
>    type="VARCHAR" size="16" autoIncrement="false" />
>   (...other table fields without links to other tables...)
>   <unique name="VERSIONS_IDSET">
>     <unique-column name="L10NCODE" />
>     <unique-column name="PRODCODE" />
>     <unique-column name="VERSCODE" />
>   </unique>
>   <foreign-key foreignTable="L10N" name="VERSIONS_FK_L10NCODE_L10N">
>     <reference foreign="L10NCODE" local="L10NCODE" />
>   </foreign-key>
>   <foreign-key foreignTable="PRODUCTS"
>    name="VERSIONS_FK_PRODCODE_PRODUCTS">
>     <reference foreign="PRODCODE" local="PRODCODE" />
>   </foreign-key>
>   <foreign-key foreignTable="USERS"
>    name="VERSIONS_FK_VERSOWNER_USERS">
>     <reference foreign="USERCODE" local="VERSOWNER" />
>   </foreign-key>
> </table>
>
> These foreign key constraints were added on a second round (the table
> itself with the primary key and the fields was added before, and it is
> empy), and now DdlUtils is trying to add them, causing these exceptions:
>
> Caused by: org.apache.ddlutils.DatabaseOperationException: Error while
> executing SQL
> ALTER TABLE VERSIONS ADD CONSTRAINT VERSIONS_FK_L10NCODE_L10N
>                      FOREIGN KEY (L10NCODE) REFERENCES L10N (L10NCODE)
> at
> org.apache.ddlutils.platform.PlatformImplBase.evaluateBatch(PlatformImplBase.java:331)
> at
> org.apache.ddlutils.platform.PlatformImplBase.alterTables(PlatformImplBase.java:573)
> at
> org.apache.ddlutils.platform.PlatformImplBase.alterTables(PlatformImplBase.java:507)
>
> Caused by: SQL Exception: Constraint 'VERSIONS_FK_L10NCODE_L10N' is
> invalid: there is no unique or primary key constraint on table
> 'SA.L10N' that matches the number and types of the columns in the
> foreign key.
>
> (I've just checked it with DdlUtils 1.0 RC-2) It seems that DdlUtils
> can't add the restriction because table L10N has not any unique or
> primary key constraint matching the foreign key in table VERSIONS; in
> other words, that field L10NCODE at table L10N is not defined as
> either a primary key nor being part of a unique restriction, but I
> don't think so:
>
> <table name="L10N">
>   <column name="ID" primaryKey="true" required="true" type="INTEGER"
>    size="10" autoIncrement="true"/>
>   <column name="L10NCODE" primaryKey="false" required="true"
>    type="VARCHAR" size="10" autoIncrement="false"/>
>   (...otros campos internos...)
>   <unique name="L10N_BY_CODE">
>     <unique-column name="L10NCODE"/>
>   </unique>
> </table>
>
> ...And I've checked through NetBeans Database Explorer that the table
> L10N already has an index named L10N_BY_CODE.
>
> So, I don't know if I'm faling to understand the SQL error message, if
> I'm missing something obvious, or if it is just that something at
> DdlUtils is not working fully right (yeah, even I bet on the first two
> options!). :-) Can anyone give me a hint, please?

Derby requires that the columns in the referenced table are part of
the primary key or unique. Note that this is the unique column
constraint, not the unique index
(http://db.apache.org/derby/docs/10.2/ref/rrefsqlj16095.html#rrefsqlj16095).
DdlUtils currently cannot create unique columns (a feature request for
1.1), so with DdlUtils the columns have to be part of the primary key.

Tom

Mime
View raw message