Return-Path: 
 <ddlutils-user-return-656-apmail-db-ddlutils-user-archive=db.apache.org@db.apache.org>
Delivered-To: apmail-db-ddlutils-user-archive@www.apache.org
Received: (qmail 56318 invoked from network); 23 May 2007 20:01:14 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2)
  by minotaur.apache.org with SMTP; 23 May 2007 20:01:14 -0000
Received: (qmail 3991 invoked by uid 500); 23 May 2007 20:01:19 -0000
Delivered-To: apmail-db-ddlutils-user-archive@db.apache.org
Received: (qmail 3969 invoked by uid 500); 23 May 2007 20:01:18 -0000
Mailing-List: contact ddlutils-user-help@db.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:ddlutils-user-help@db.apache.org>
List-Unsubscribe: <mailto:ddlutils-user-unsubscribe@db.apache.org>
List-Post: <mailto:ddlutils-user@db.apache.org>
List-Id: <ddlutils-user.db.apache.org>
Reply-To: ddlutils-user@db.apache.org
Delivered-To: mailing list ddlutils-user@db.apache.org
Received: (qmail 3957 invoked by uid 99); 23 May 2007 20:01:18 -0000
Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133)
    by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 23 May 2007 13:01:18 -0700
X-ASF-Spam-Status: No, hits=0.0 required=10.0
	tests=
X-Spam-Check-By: apache.org
Received-SPF: neutral (herse.apache.org: local policy)
Received: from [84.77.178.227] (HELO Father.family) (84.77.178.227)
    by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 23 May 2007 13:01:10 -0700
Received: from Father.family (Father.family [192.168.1.3])
	by Father.family (Postfix) with ESMTP id D216B8EDBB
	for <ddlutils-user@db.apache.org>; Wed, 23 May 2007 22:00:48 +0200 (CEST)
Message-ID: <46549D70.3030801@yahoo.es>
Date: Wed, 23 May 2007 22:00:48 +0200
From: Ricardo Palomares <rpmdisguise-java@yahoo.es>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; es-ES;
 rv:1.8.1.2) Gecko/20070221 SeaMonkey/1.1.1 Mnenhy/0.7.4.0
MIME-Version: 1.0
To: DdlUtils Mailing List <ddlutils-user@db.apache.org>
Subject: SQL Exception on trying to add some foreign keys constraints with
 DdlUtils
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: 7bit
X-Virus-Checked: Checked by ClamAV on apache.org

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