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 Foreign Key problem
Date Tue, 06 Jun 2006 20:00:18 GMT
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


Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message