db-ddlutils-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Terry Steichen <te...@net-frame.com>
Subject Re: Foreign Key problem
Date Wed, 07 Jun 2006 13:14:10 GMT
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 <terry@net-frame.com> 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
>>
>

Mime
View raw message