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
