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
