You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ddlutils-user@db.apache.org by Terry Steichen <te...@net-frame.com> on 2006/06/06 22:00:18 UTC
Foreign Key problem
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)?
Below is the schema, followed by the stacktrace.
TIA,
Terry Steichen
PS: I can't find the version of ddlutils - but I just checked the source
out from svn today and built it.
================= schema ===============================
<database name="MyModel">
<table name="PERSPECTIVE">
<column name="ID" primaryKey="true" required="true" type="INTEGER"
size="10" autoIncrement="true"/>
<column name="LOG_NAME" primaryKey="false" required="false"
type="VARCHAR" size="30" autoIncrement="false"/>
<column name="PERS_NAME" primaryKey="false" required="false"
type="VARCHAR" size="60" autoIncrement="false"/>
<column name="DESCRIPTION" primaryKey="false" required="false"
type="CLOB" size="1048576" autoIncrement="false"/>
<column name="VISIBILITY" primaryKey="false" required="false"
type="INTEGER" size="10" autoIncrement="false"/>
<column name="AUTHORITY" primaryKey="false" required="false"
type="CLOB" size="1048576" autoIncrement="false"/>
<column name="VERIFICATION" primaryKey="false" required="false"
type="INTEGER" size="10" autoIncrement="false"/>
</table>
<table name="S_POS">
<column name="ID" primaryKey="true" required="true" type="INTEGER"
size="10" autoIncrement="true"/>
<column name="PAGE_NAME" primaryKey="false" required="false"
type="VARCHAR" size="60" autoIncrement="false"/>
<column name="VERSION" primaryKey="false" required="false"
type="INTEGER" size="10" autoIncrement="false"/>
<column name="STRENGTH" primaryKey="false" required="false"
type="INTEGER" size="10" autoIncrement="false"/>
<column name="TOPIC" primaryKey="false" required="false"
type="VARCHAR" size="30" autoIncrement="false"/>
<column name="LEVEL" primaryKey="false" required="false"
type="VARCHAR" size="8" autoIncrement="false"/>
<column name="PARENT_PAGE" primaryKey="false" required="false"
type="VARCHAR" size="60" autoIncrement="false"/>
<column name="MTIME" primaryKey="false" required="false"
type="TIMESTAMP" size="26" autoIncrement="false"/>
<column name="PERSPECTIVE_ID" primaryKey="false" required="false"
type="INTEGER" size="10" autoIncrement="false"/>
<!-- *this is the offending element*
<foreign-key foreignTable="PERSPECTIVE" name="SQL060531032406571">
<reference local="PERSPECTIVE_ID" foreign="ID"/>
</foreign-key>
-->
<index name="IX2_S_POS">
<index-column name="PAGE_NAME"/>
</index>
</table>
<table name="WIKI_PAGE">
<column name="PAGE_NAME" primaryKey="true" required="true"
type="VARCHAR" size="100" autoIncrement="false"/>
<column name="PAGE_VERSION" primaryKey="false" required="false"
type="INTEGER" size="10" default="0" autoIncrement="false"/>
<column name="PAGE_MODIFIED" primaryKey="false" required="false"
type="TIMESTAMP" size="26" autoIncrement="false"/>
<column name="PAGE_MODIFIED_BY" primaryKey="false"
required="false" type="VARCHAR" size="50" autoIncrement="false"/>
<column name="PAGE_TEXT" primaryKey="false" required="false"
type="CLOB" size="1048576" autoIncrement="false"/>
<index name="IX_PAGE_MODIFIED">
<index-column name="PAGE_MODIFIED"/>
</index>
</table>
<table name="WIKI_PAGE_VERSIONS">
<column name="VERSION_NAME" primaryKey="true" required="true"
type="VARCHAR" size="100" autoIncrement="false"/>
<column name="VERSION_NUM" primaryKey="true" required="true"
type="INTEGER" size="10" default="0" autoIncrement="false"/>
<column name="VERSION_MODIFIED" primaryKey="false"
required="false" type="TIMESTAMP" size="26" autoIncrement="false"/>
<column name="VERSION_MODIFIED_BY" primaryKey="false"
required="false" type="VARCHAR" size="50" autoIncrement="false"/>
<column name="VERSION_TEXT" primaryKey="false" required="false"
type="CLOB" size="1048576" autoIncrement="false"/>
<!-- *this foreign key element works fine* -->
<foreign-key foreignTable="WIKI_PAGE"
name="FK_WIKI_PAGE_VERSIONS_WIKI_PAGE">
<reference local="VERSION_NAME" foreign="PAGE_NAME"/>
</foreign-key>
</table>
</database>
=================== trace =========================
F:\components\ddlutils>ant -buildfile derby_create.xml
Buildfile: derby_create.xml
database-setup:
[ddlToDatabase] Read schema file F:\components\ddlutils\db-schema.xml
[ddlToDatabase] Created database
[ddlToDatabase] Jun 6, 2006 3:54:36 PM
org.apache.ddlutils.alteration.ModelComparator compare
[ddlToDatabase] INFO: Table PERSPECTIVE needs to be added
[ddlToDatabase] Jun 6, 2006 3:54:36 PM
org.apache.ddlutils.alteration.ModelComparator compare
[ddlToDatabase] INFO: Table S_POS needs to be added
[ddlToDatabase] Jun 6, 2006 3:54:36 PM
org.apache.ddlutils.alteration.ModelComparator compare
[ddlToDatabase] INFO: Table WIKI_PAGE needs to be added
[ddlToDatabase] Jun 6, 2006 3:54:36 PM
org.apache.ddlutils.alteration.ModelComparator compare
[ddlToDatabase] INFO: Table WIKI_PAGE_VERSIONS needs to be added
[ddlToDatabase] Jun 6, 2006 3:54:37 PM
org.apache.ddlutils.platform.PlatformImplBase evaluateBatch
[ddlToDatabase] INFO: Executed 8 SQL command(s) with 0 error(s)
[ddlToDatabase] Written schema to database
[ddlToDatabase] Jun 6, 2006 3:54:38 PM
org.apache.ddlutils.platform.PlatformImplBase insert
[ddlToDatabase] WARNING: The database does not support querying for
auto-generated pk values
[ddlToDatabase] Jun 6, 2006 3:54:38 PM
org.apache.ddlutils.platform.PlatformImplBase insert
[ddlToDatabase] WARNING: The database does not support querying for
auto-generated pk values
[ddlToDatabase] Jun 6, 2006 3:54:38 PM
org.apache.ddlutils.platform.PlatformImplBase insert
[ddlToDatabase] WARNING: The database does not support querying for
auto-generated pk values
[ddlToDatabase] Jun 6, 2006 3:54:38 PM
org.apache.ddlutils.platform.PlatformImplBase insert
[ddlToDatabase] WARNING: The database does not support querying for
auto-generated pk values
[ddlToDatabase] Jun 6, 2006 3:54:38 PM
org.apache.ddlutils.platform.PlatformImplBase insert
[ddlToDatabase] WARNING: The database does not support querying for
auto-generated pk values
[ddlToDatabase] Jun 6, 2006 3:54:38 PM
org.apache.commons.digester.Digester endElement
[ddlToDatabase] SEVERE: End event threw exception
[ddlToDatabase] org.apache.ddlutils.io.DataSinkException:
org.apache.ddlutils.DynaSqlException: Error while inserting in
to the database
[ddlToDatabase] at
org.apache.ddlutils.io.DataToDatabaseSink.insertSingleBeanIntoDatabase(DataToDatabaseSink.jav
a:391)
[ddlToDatabase] at
org.apache.ddlutils.io.DataToDatabaseSink.insertBeanIntoDatabase(DataToDatabaseSink.java:330)
[ddlToDatabase] at
org.apache.ddlutils.io.DataToDatabaseSink.addBean(DataToDatabaseSink.java:273)
[ddlToDatabase] at
org.apache.ddlutils.io.DynaSqlCreateRule.end(DynaSqlCreateRule.java:79)
[ddlToDatabase] at
org.apache.commons.digester.Digester.endElement(Digester.java:1130)
[ddlToDatabase] at
org.apache.xerces.parsers.AbstractSAXParser.endElement(Unknown Source)
[ddlToDatabase] at
org.apache.xerces.parsers.AbstractXMLDocumentParser.emptyElement(Unknown
Source)
[ddlToDatabase] at
org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanStartElement(Unknown
Source)
[ddlToDatabase] at
org.apache.xerces.impl.XMLDocumentFragmentScannerImpl$FragmentContentDispatcher.dispatch(Unkn
own Source)
[ddlToDatabase] at
org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown
Source)
[ddlToDatabase] at
org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
[ddlToDatabase] at
org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
[ddlToDatabase] at
org.apache.xerces.parsers.XMLParser.parse(Unknown Source)
[ddlToDatabase] at
org.apache.xerces.parsers.AbstractSAXParser.parse(Unknown Source)
[ddlToDatabase] at
org.apache.commons.digester.Digester.parse(Digester.java:1631)
[ddlToDatabase] at
org.apache.ddlutils.task.WriteDataToDatabaseCommand.readSingleDataFile(WriteDataToDatabaseCom
mand.java:189)
[ddlToDatabase] at
org.apache.ddlutils.task.WriteDataToDatabaseCommand.execute(WriteDataToDatabaseCommand.java:1
33)
[ddlToDatabase] at
org.apache.ddlutils.task.DatabaseTaskBase.executeCommands(DatabaseTaskBase.java:198)
[ddlToDatabase] at
org.apache.ddlutils.task.DatabaseTaskBase.execute(DatabaseTaskBase.java:222)
[ddlToDatabase] at
org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:275)
[ddlToDatabase] at org.apache.tools.ant.Task.perform(Task.java:364)
[ddlToDatabase] at
org.apache.tools.ant.Target.execute(Target.java:341)
[ddlToDatabase] at
org.apache.tools.ant.Target.performTasks(Target.java:369)
[ddlToDatabase] at
org.apache.tools.ant.Project.executeTarget(Project.java:1214)
[ddlToDatabase] at
org.apache.tools.ant.Project.executeTargets(Project.java:1062)
[ddlToDatabase] at org.apache.tools.ant.Main.runBuild(Main.java:673)
[ddlToDatabase] at org.apache.tools.ant.Main.startAnt(Main.java:188)
[ddlToDatabase] at
org.apache.tools.ant.launch.Launcher.run(Launcher.java:196)
[ddlToDatabase] at
org.apache.tools.ant.launch.Launcher.main(Launcher.java:55)
[ddlToDatabase] Caused by: org.apache.ddlutils.DynaSqlException: Error
while inserting into the database
[ddlToDatabase] at
org.apache.ddlutils.platform.PlatformImplBase.insert(PlatformImplBase.java:1126)
[ddlToDatabase] at
org.apache.ddlutils.io.DataToDatabaseSink.insertSingleBeanIntoDatabase(DataToDatabaseSink.jav
a:376)
[ddlToDatabase] ... 28 more
[ddlToDatabase] Caused by: SQL Exception: INSERT on table 'S_POS' caused
a violation of foreign key constraint 'SQL06053
1032406571' for key (15). The statement has been rolled back.
[ddlToDatabase] at
org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
[ddlToDatabase] at
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown
Source)
[ddlToDatabase] at
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown
Source)
[ddlToDatabase] at
org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
[ddlToDatabase] at
org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
[ddlToDatabase] at
org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
[ddlToDatabase] at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown
Source)
[ddlToDatabase] at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown
Source)
[ddlToDatabase] at
org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement
.java:101)
[ddlToDatabase] at
org.apache.ddlutils.platform.PlatformImplBase.insert(PlatformImplBase.java:1115)
[ddlToDatabase] ... 29 more
BUILD FAILED
F:\components\ddlutils\derby_create.xml:20: Could not read data file
F:\components\ddlutils\data.xml
Re: Foreign Key problem
Posted by Thomas Dudziak <to...@gmail.com>.
On 6/7/06, Terry Steichen <te...@net-frame.com> wrote:
> 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.)
Well, that is not exactly true: you can insert into an auto-increment
field if the auto-increment is specified to allow this. For Derby,
this can be done via the "generate by default as identity" column
constraint (which is the one that DdlUtils uses). Though you'll likely
have problems later on because the identity value generator does not
pick up your inserted values, so it is likely that it spits out values
that you've inserted manually before.
> 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.
Yes, you're right, my mistake.
The stacktrace says that the key "15" is inserted twice, perhaps the
data that you insert is wrong ?
Tom
Re: Foreign Key problem
Posted by Terry Steichen <te...@net-frame.com>.
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 <te...@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
>>
>
Re: Foreign Key problem
Posted by Terry Steichen <te...@net-frame.com>.
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 <te...@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
>
Re: Foreign Key problem
Posted by Thomas Dudziak <to...@gmail.com>.
On 6/6/06, Terry Steichen <te...@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