SQL Exception on trying to add some foreign keys constraints with DdlUtils

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(Pl
atformImplBase.java:573)
at
org.apache.ddlutils.platform.PlatformImplBase.alterTables(Pl
atformImplBase.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

On 5/23/07, Ricardo Palomares <rpmdisguise-javayahoo.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(Pl
atformImplBase.java:573)
> at
>
org.apache.ddlutils.platform.PlatformImplBase.alterTables(Pl
atformImplBase.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/re
f/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

Thomas Dudziak escribió:
> On 5/23/07, Ricardo Palomares <rpmdisguise-java at
yahoo.es> wrote:
>> 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.
>> (...)
> 
> 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/re
f/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.

First of all, sorry for the late followup.

Since I'm not going to stop using DdlUtils (too useful to
me, thank
you!) 
what do you think would be the smarter move on my part? I'm
assuming DdlUtils 1.1 is still far away so sit and wait is
not an
option for me. If L10N table has these fields:

<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"/>
  (...other internal fields...)
  <unique name="L10N_BY_CODE">
    <unique-column name="L10NCODE"/>
  </unique>
</table>

would it be OK if I just turn L10NCODE on as part of the
primary key?
(It looks a bit weird to me, to be honest).

Actually, I put ID there just to have a primary key
definition in a
consistent way through the whole database, but I could well
live with
L10NCODE being the PK (and completely drop ID field in that
table),
since it will be unique by design and the table itself would
likely
never grow up over 100 records (being 2 or 3 the more usual
figure).

Sorry for slipping into OT here. I'm fairly new to
relational database
design and still learning what is considered sensible and
what plain
bad design.

TIA

On 5/28/07, Ricardo Palomares <rpmdisguise-javayahoo.es> wrote:

> <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"/>
>   (...other internal fields...)
>   <unique name="L10N_BY_CODE">
>     <unique-column name="L10NCODE"/>
>   </unique>
> </table>
>
> would it be OK if I just turn L10NCODE on as part of
the primary key?
> (It looks a bit weird to me, to be honest).
>
> Actually, I put ID there just to have a primary key
definition in a
> consistent way through the whole database, but I could
well live with
> L10NCODE being the PK (and completely drop ID field in
that table),
> since it will be unique by design and the table itself
would likely
> never grow up over 100 records (being 2 or 3 the more
usual figure).

Well, the question is, do you really need the ID field (i.e.
does it
serve a purpose in your application) or is it just an
artificial field
(i.e. only used in the database or for lookup of rows).
If the latter and L10NCODE has to be unique anyways, then
IMO it does
not really make sense to have an additional ID field with no
real
purpose.
Think of it this way: the primary key sort-of defines the
identity of
the row. So, if L10NCODE uniquely defines a row in the table
(and thus
could be used for primary key), then all you have to ask
yourself is:
does it make sense in your application to have L10NCODE be
the primary
key ? This is not so much a database question but a
application design
question. Imagine that you have multiple unique columns in
the table,
which one would you choose for the primary key (if any) ?

Tom

Thomas Dudziak escribió:
> Think of it this way: the primary key sort-of defines
the identity of
> the row. So, if L10NCODE uniquely defines a row in the
table (and thus
> could be used for primary key), then all you have to
ask yourself is:
> does it make sense in your application to have L10NCODE
be the primary
> key ? This is not so much a database question but a
application design
> question. Imagine that you have multiple unique columns
in the table,
> which one would you choose for the primary key (if any)
?

Definitely, I'll remove the ID field. The actual reason I
added these
"sintetic" ID fields is because I've read so much
about the goodness
of using them, instead of the otherwise natural PKs, to
speed up
databases, that I bit the bullet and ended declaring them
for every
table as a sign of a well designed database. Novice sins, I
guess.

Thank you very much for your time.

Ricardo

I would go the other way; I'd add a unique constraint to
L10NCODE and 
leave the ID field in as the primary key.  I think it's a
bad idea to 
use anything in your data as a primary key because I worry
that you can 
never be absolutely sure that you won't need to change that
data.  To 
me, the only safe primary key is a synthetic primary key,
only because 
it's probably safer in the long run.

Ricardo Palomares wrote:
> Thomas Dudziak escribió:
>   
>> Think of it this way: the primary key sort-of
defines the identity of
>> the row. So, if L10NCODE uniquely defines a row in
the table (and thus
>> could be used for primary key), then all you have
to ask yourself is:
>> does it make sense in your application to have
L10NCODE be the primary
>> key ? This is not so much a database question but a
application design
>> question. Imagine that you have multiple unique
columns in the table,
>> which one would you choose for the primary key (if
any) ?
>>     
>
> Definitely, I'll remove the ID field. The actual reason
I added these
> "sintetic" ID fields is because I've read so
much about the goodness
> of using them, instead of the otherwise natural PKs, to
speed up
> databases, that I bit the bullet and ended declaring
them for every
> table as a sign of a well designed database. Novice
sins, I guess.
>
> Thank you very much for your time.
>
> Ricardo
>
>
>   

-- 
Rusty Wright
UC Berkeley
IS&T Web Applications
510-643-9097  office
925-212-3774  cell

Rusty Wright escribió:
> I would go the other way; I'd add a unique constraint
to L10NCODE and
> leave the ID field in as the primary key.


However, it can't be done right now with DdlUtils (see
Thomas's first
reply) and, for me, using DdlUtils is a must.


> I think it's a bad idea to
> use anything in your data as a primary key because I
worry that you can
> never be absolutely sure that you won't need to change
that data.  To
> me, the only safe primary key is a synthetic primary
key, only because
> it's probably safer in the long run.


Well, in this case, the L10NCODE is certainly the value that
uniquely
sets apart any two records in L10N table. It will never be
two records
with the same value by design. Besides, for efficiency
reasons, those
tables in which references exist to L10N records have an
L10NCODE
field instead of something like a "L10N-ID" field
(L10NCODE has a
meaning for humans, L10N-ID not, which would require an
additional
query to L10N).

Anyway, I clearly see your point, and I agree that, in
general, the
synthetic key (BTW, weird spelling of "sintetic"
the one I did before,
sorry) is a better and safer choice.

Thanks,
Ricardo