A new topic, 'Problem with indexes and generateChangeLog', has been made on a
board you are watching.
You can see it at
http://liquibase.org/forum/index.php?topic=685.new#new
The text of the topic is shown below:
A db (SQL 2005) has tables with composite primary keys and dependent tables
that reference only part of the PK (will withhold critiquing that design
anti-pattern for another day). This "RI" is implemented by creating a unique
index on the column referenced by the foreign key in the child table. Example:
CREATE TABLE voucher (
invoice_id int not null,
voucher_id int not null,
gross_amt DECIMAL(10,2) )
ALTER TABLE voucher ADD CONSTRAINT xpkvoucher PRIMARY KEY (invoice_id,
voucher_id)
CREATE UNIQUE INDEX xif_4140 ON voucher(voucher_id)
CREATE TABLE ck_jobcost (
voucher_id int not null,
jobcost_id int not null,
line_item int )
ALTER TABLE ck_jobcost ADD CONSTRAINT xpkck_jobcost PRIMARY KEY (voucher_id,
jobcost_id)
ALTER TABLE ck_jobcost ADD CONSTRAINT r_4171 FOREIGN KEY (voucher_id)
REFERENCES voucher (voucher_id)
Using generateChangeLog gives me this:
<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
- <databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog";
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd";>
- <changeSet author="jg096233 (generated)" id="1283644112444-1">
- <createTable schemaName="dbo" tableName="ck_jobcost">
- <column name="voucher_id" type="INT">
<constraints nullable="false" />
</column>
- <column name="jobcost_id" type="INT">
<constraints nullable="false" />
</column>
<column name="line_item" type="INT" />
</createTable>
</changeSet>
- <changeSet author="jg096233 (generated)" id="1283644112444-2">
- <createTable schemaName="dbo" tableName="voucher">
- <column name="invoice_id" type="INT">
<constraints nullable="false" />
</column>
- <column name="voucher_id" type="INT">
<constraints nullable="false" />
</column>
<column name="gross_amt" type="decimal(10,2)" />
</createTable>
</changeSet>
- <changeSet author="jg096233 (generated)" id="1283644112444-3">
<addPrimaryKey columnNames="invoice_id, voucher_id"
constraintName="xpkvoucher" schemaName="dbo" tableName="voucher" />
</changeSet>
- <changeSet author="jg096233 (generated)" id="1283644112444-4">
<addForeignKeyConstraint baseColumnNames="voucher_id"
baseTableName="ck_jobcost" baseTableSchemaName="dbo" constraintName="r_4171"
deferrable="false" initiallyDeferred="false" onDelete="NO ACTION" onUpdate="NO
ACTION" referencedColumnNames="voucher_id" referencedTableName="voucher"
referencedTableSchemaName="dbo" referencesUniqueColumn="false" />
</changeSet>
- <changeSet author="jg096233 (generated)" id="1283644112444-5">
- <createIndex associatedWith="" indexName="xif_4140" schemaName="dbo"
tableName="voucher" unique="true">
<column name="voucher_id" />
</createIndex>
</changeSet>
- <changeSet author="jg096233 (generated)" id="1283644112444-6">
- <createIndex associatedWith="primaryKey" indexName="xpkvoucher"
schemaName="dbo" tableName="voucher" unique="true">
<column name="invoice_id" />
<column name="voucher_id" />
</createIndex>
</changeSet>
</databaseChangeLog>
Because the indexes are not being creating until after the foreign keys, I'm
having "no primary or candidate keys in the referenced table" SQL errors when I
run update. (I want to get liquibase going with an existing project, so wish to
baseline the schema and redeploy to a new instance.)
Unsubscribe to new topics from this board by clicking here:
http://liquibase.org/forum/index.php?action=notifyboard;board=1.0
Regards,
The Liquibase Community Forum Team.
------------------------------------------------------------------------------
This SF.net Dev2Dev email is sponsored by:
Show off your parallel programming skills.
Enter the Intel(R) Threading Challenge 2010.
http://p.sf.net/sfu/intel-thread-sfd
_______________________________________________
Liquibase-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/liquibase-user
