Sorry to respond to my own post, but the problem was (is?) indeed that
the target of the foreign key was an auto-increment field. When I
altered the schema to set that field's autoincrement attribute to
"false", the new database was created and the data loaded fine.
This leads to the question: how to make the created database function
the way the original one did, that is, with the field in question
operating in an auto-increment mode? Can/should I do this after
creation with an alter column operation? Even if that works, it seems
pretty awkward.
(At a minimum, this apparent limitation to the use of ddlutils should
probably be documented somewhere.)
Terry Steichen wrote:
Tom,
I forgot to mention that I had thought of that, and modified the name
of the foreign key to a different name. No change.
Is it possible that the problem is that the target field for the
foreign key (in the PERSPECTIVE table) is set for auto-increment?
(I've never been sure how this would/should work anyway, because you
can't insert values into an auto-increment field.)
Terry
PS: when you say that the model worked fine for you, I presume you
mean that you could create a structure using the schema I provided.
However, I think the problem (as you can see from the stacktrace)
comes when you try to insert the data into that structure.
Thomas Dudziak wrote:
On 6/6/06, Terry Steichen <[EMAIL PROTECTED]> wrote:
I used ddlutils to dump a schema and contents of an existing (Derby
10.1.2.1) database, containing four tables (PERSPECTIVE, S_POS,
WIKI_PAGE and WIKI_PAGE_VERSIONS). When I try to create a new database
using the dumped schema and data, I get an exception, saying that an
insert to one of my tables (S_POS) violates the associated foreign key.
(S_POS:PERSPECTIVE_ID->PERSPECTIVE:ID). If I remove the foreign key
specification from the schema, it seems to work fine in creating a new
database.
What's particularly puzzling is that two of my other tables are also
linked via a foreign key
(WIKI_PAGE_VERSIONS:VERSION_NAME->WIKI_PAGE:PAGE_NAME) and it works
just
fine - no exceptions at all.
I have three questions:
(1) why is the first foreign key specification causing a problem?
(2) why is the second foreign key specification working fine?
(3) what is the consequence of simply removing the foreign key (and
creating the database without it)?
Your model works fine for me (clean Derby 10.1.2.1 database).
I think the problem lies in the fact that the original foreignkey
between S_POS and PERSPECTIVE was created without a name. Thus, the
database assigned one automatically (SQL060531032406571). Now, since
this is an internal name, it can be that in your target database there
is already an object with this name (a foreignkey, an index, ...) and
thus the database complains.
To remedy this, you should give the foreignkey a useful name, either
in the original database (if possible) or in the generated XML:
Tom
