db-ddlutils-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ricardo Palomares <rpmdisguise-j...@yahoo.es>
Subject SQL Exception on trying to add some foreign keys constraints with DdlUtils
Date Wed, 23 May 2007 20:00:48 GMT
Hi,

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?

TIA


Mime
View raw message