View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0003750SymmetricDSBugpublic2018-10-10 14:432018-10-31 18:59
Reporterhanes Assigned Tojosh-a-hicks  
Prioritynormal 
Status closedResolutionduplicate 
Product Version3.9.14 
Target Version3.9.15Fixed in Version3.9.15 
Summary0003750: Create Table fails with foreign key constraints with more than one column in H2 and Postgres
DescriptionIf a table has a constrain that references more than one column, Create Table incorrectly runs two alter statements, at least under H2 (and postgres).

At the source:

ALTER TABLE "ITEM_PRICE"
    ADD CONSTRAINT "CONSTRAINT_DF00" FOREIGN KEY ("ITEM_ID", "STORE_ID") REFERENCES "ITEM" ("ID", "DESCRIPTION");

But the XML that is generated for the Create Table contains two entries for the constraint, not just one.

Interesting, on the source, the SQL Explorer's Source tab shows things correctly.
Additional Information[store-001] - DefaultDatabaseWriter - About to create table using the following definition: <?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database">
<database name="dataextractor">
    <table name="ITEM_PRICE">
        <column name="ITEM_ID" primaryKey="true" required="true" type="VARCHAR" size="50">
            <platform-column name="h2" type="VARCHAR" size="50"/>
        </column>
        <column name="STORE_ID" primaryKey="true" required="true" type="VARCHAR" size="5">
            <platform-column name="h2" type="VARCHAR" size="5"/>
        </column>
        <column name="PRICE" required="true" type="DECIMAL" size="65535,32767">
            <platform-column name="h2" type="DECIMAL" size="65535" decimalDigits="32767"/>
        </column>
        <foreign-key name="CONSTRAINT_DF0" foreignTable="ITEM" foreignTableCatalog="" foreignTableSchema="">
            <reference local="ITEM_ID" foreign="ID"/>
        </foreign-key>
        <foreign-key name="CONSTRAINT_DF00" foreignTable="ITEM" foreignTableCatalog="" foreignTableSchema="">
            <reference local="ITEM_ID" foreign="ID"/>
            <reference local="STORE_ID" foreign="DESCRIPTION"/>
        </foreign-key>
        <foreign-key name="CONSTRAINT_DF00" foreignTable="ITEM" foreignTableCatalog="" foreignTableSchema="">
            <reference local="ITEM_ID" foreign="ID"/>
            <reference local="STORE_ID" foreign="DESCRIPTION"/>
        </foreign-key>
    </table>
</database>
[store-001] - H2DatabasePlatform - Running alter sql:
ALTER TABLE "ITEM_PRICE"
    ADD CONSTRAINT "CONSTRAINT_DF00" FOREIGN KEY ("ITEM_ID", "STORE_ID") REFERENCES "ITEM" ("ID", "DESCRIPTION");
ALTER TABLE "ITEM_PRICE"
    ADD CONSTRAINT "CONSTRAINT_DF00" FOREIGN KEY ("ITEM_ID", "STORE_ID") REFERENCES "ITEM" ("ID", "DESCRIPTION");

[store-001] - JdbcSqlTemplate - Constraint "CONSTRAINT_DF00" already exists; SQL statement:
ALTER TABLE "ITEM_PRICE"
    ADD CONSTRAINT "CONSTRAINT_DF00" FOREIGN KEY ("ITEM_ID", "STORE_ID") REFERENCES "ITEM" ("ID", "DESCRIPTION") [90045-176]. Failed to execute: ALTER TABLE "ITEM_PRICE"
    ADD CONSTRAINT "CONSTRAINT_DF00" FOREIGN KEY ("ITEM_ID", "STORE_ID") REFERENCES "ITEM" ("ID", "DESCRIPTION")
[store-001] - DefaultDatabaseWriter - Failed to alter table using the following xml: <?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database">
<database name="dataextractor">
    <table name="ITEM_PRICE">
        <column name="ITEM_ID" primaryKey="true" required="true" type="VARCHAR" size="50">
            <platform-column name="h2" type="VARCHAR" size="50"/>
        </column>
        <column name="STORE_ID" primaryKey="true" required="true" type="VARCHAR" size="5">
            <platform-column name="h2" type="VARCHAR" size="5"/>
        </column>
        <column name="PRICE" required="true" type="DECIMAL" size="65535,32767">
            <platform-column name="h2" type="DECIMAL" size="65535" decimalDigits="32767"/>
        </column>
        <foreign-key name="CONSTRAINT_DF0" foreignTable="ITEM" foreignTableCatalog="" foreignTableSchema="">
            <reference local="ITEM_ID" foreign="ID"/>
        </foreign-key>
        <foreign-key name="CONSTRAINT_DF00" foreignTable="ITEM" foreignTableCatalog="" foreignTableSchema="">
            <reference local="ITEM_ID" foreign="ID"/>
            <reference local="STORE_ID" foreign="DESCRIPTION"/>
        </foreign-key>
        <foreign-key name="CONSTRAINT_DF00" foreignTable="ITEM" foreignTableCatalog="" foreignTableSchema="">
            <reference local="ITEM_ID" foreign="ID"/>
            <reference local="STORE_ID" foreign="DESCRIPTION"/>
        </foreign-key>
    </table>
</database>
org.jumpmind.db.sql.SqlException: Constraint "CONSTRAINT_DF00" already exists; SQL statement:
ALTER TABLE "ITEM_PRICE"
    ADD CONSTRAINT "CONSTRAINT_DF00" FOREIGN KEY ("ITEM_ID", "STORE_ID") REFERENCES "ITEM" ("ID", "DESCRIPTION") [90045-176]
    at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:302)
    at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:291)
    at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:503)
    at org.jumpmind.db.sql.JdbcSqlTemplate.update(JdbcSqlTemplate.java:411)
    at org.jumpmind.db.sql.SqlScript.execute(SqlScript.java:115)
    at org.jumpmind.db.platform.AbstractDatabasePlatform.alterTables(AbstractDatabasePlatform.java:227)
    at org.jumpmind.db.platform.AbstractDatabasePlatform.alterDatabase(AbstractDatabasePlatform.java:200)
    at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.create(DefaultDatabaseWriter.java:542)
    at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:203)
    at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64)
    at org.jumpmind.symmetric.model.ProcessInfoDataWriter.write(ProcessInfoDataWriter.java:84)
    at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64)
    at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter.java:201)
    at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:210)
    at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:177)
    at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:123)
    at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1039)
    at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1016)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Caused by: org.h2.jdbc.JdbcSQLException: Constraint "CONSTRAINT_DF00" already exists; SQL statement:
ALTER TABLE "ITEM_PRICE"
    ADD CONSTRAINT "CONSTRAINT_DF00" FOREIGN KEY ("ITEM_ID", "STORE_ID") REFERENCES "ITEM" ("ID", "DESCRIPTION") [90045-176]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)
    at org.h2.message.DbException.get(DbException.java:178)
    at org.h2.message.DbException.get(DbException.java:154)
    at org.h2.command.ddl.AlterTableAddConstraint.tryUpdate(AlterTableAddConstraint.java:99)
    at org.h2.command.ddl.AlterTableAddConstraint.update(AlterTableAddConstraint.java:73)
    at org.h2.command.CommandContainer.update(CommandContainer.java:79)
    at org.h2.command.Command.executeUpdate(Command.java:254)
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:186)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:160)
    at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
    at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
    at org.jumpmind.db.sql.JdbcSqlTemplate$6.execute(JdbcSqlTemplate.java:426)
    at org.jumpmind.db.sql.JdbcSqlTemplate$6.execute(JdbcSqlTemplate.java:411)
    at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:501)
    ... 19 more
[store-001] - ManageIncomingBatchListener - Failed to load batch 000-88
org.jumpmind.db.sql.SqlException: Constraint "CONSTRAINT_DF00" already exists; SQL statement:
ALTER TABLE "ITEM_PRICE"
    ADD CONSTRAINT "CONSTRAINT_DF00" FOREIGN KEY ("ITEM_ID", "STORE_ID") REFERENCES "ITEM" ("ID", "DESCRIPTION") [90045-176]
    at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:302)
    at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:291)
    at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:503)
    at org.jumpmind.db.sql.JdbcSqlTemplate.update(JdbcSqlTemplate.java:411)
    at org.jumpmind.db.sql.SqlScript.execute(SqlScript.java:115)
    at org.jumpmind.db.platform.AbstractDatabasePlatform.alterTables(AbstractDatabasePlatform.java:227)
    at org.jumpmind.db.platform.AbstractDatabasePlatform.alterDatabase(AbstractDatabasePlatform.java:200)
    at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.create(DefaultDatabaseWriter.java:542)
    at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:203)
    at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64)
    at org.jumpmind.symmetric.model.ProcessInfoDataWriter.write(ProcessInfoDataWriter.java:84)
    at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64)
    at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter.java:201)
    at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:210)
    at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:177)
    at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:123)
    at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1039)
    at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1016)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Caused by: org.h2.jdbc.JdbcSQLException: Constraint "CONSTRAINT_DF00" already exists; SQL statement:
ALTER TABLE "ITEM_PRICE"
    ADD CONSTRAINT "CONSTRAINT_DF00" FOREIGN KEY ("ITEM_ID", "STORE_ID") REFERENCES "ITEM" ("ID", "DESCRIPTION") [90045-176]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)
    at org.h2.message.DbException.get(DbException.java:178)
    at org.h2.message.DbException.get(DbException.java:154)
    at org.h2.command.ddl.AlterTableAddConstraint.tryUpdate(AlterTableAddConstraint.java:99)
    at org.h2.command.ddl.AlterTableAddConstraint.update(AlterTableAddConstraint.java:73)
    at org.h2.command.CommandContainer.update(CommandContainer.java:79)
    at org.h2.command.Command.executeUpdate(Command.java:254)
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:186)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:160)
    at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
    at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
    at org.jumpmind.db.sql.JdbcSqlTemplate$6.execute(JdbcSqlTemplate.java:426)
    at org.jumpmind.db.sql.JdbcSqlTemplate$6.execute(JdbcSqlTemplate.java:411)
    at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:501)
    ... 19 more
[store-001] - ConfigurationChangedDataRouter - About to refresh the cache of node security because new configuration came through the data route
TagsNo tags attached.

Relationships

duplicate of 0003752 closedjosh-a-hicks Create tables fails if there is a foreign key with multiple referenced columns 

Activities

hanes

2018-10-10 16:18

developer   ~0001266

Seems to work okay in 3.9.12

Issue History

Date Modified Username Field Change
2018-10-10 14:43 hanes New Issue
2018-10-10 14:48 hanes Status new => confirmed
2018-10-10 14:48 hanes Description Updated View Revisions
2018-10-10 15:07 hanes Summary Create Table fails with foreign key constraints with more than one column in H2 => Create Table fails with foreign key constraints with more than one column in H2 and Postgres
2018-10-10 15:07 hanes Description Updated View Revisions
2018-10-10 16:18 hanes Note Added: 0001266
2018-10-10 16:18 hanes Target Version => 3.9.15
2018-10-12 20:05 josh-a-hicks Relationship added duplicate of 0003752
2018-10-12 20:05 josh-a-hicks Assigned To => josh-a-hicks
2018-10-12 20:05 josh-a-hicks Status confirmed => resolved
2018-10-12 20:05 josh-a-hicks Resolution open => duplicate
2018-10-12 20:05 josh-a-hicks Fixed in Version => 3.9.15
2018-10-31 18:59 mmichalek Status resolved => closed