Problem with indexes and generateChangeLog

General Discussion
1

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" ?>

 
 

 
 
 
 
 

 
 

 
 
 
 
 

 
 

 
 

 
 
 

 
 
 
 
 

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.)

2

(sorry for the slow reply, have been getting very far behind)

I generally consider the changelog created by generateChangeLog to be a way to output a good starting point, not to be what is actually used.  You will want to check it over for any objects/configs that are missing such as index types etc, and possibly re-order the objects as necessary. 

In your case, you may need to reverse the order of the the foreign keys/primary keys/indexes.  If your database is large, it is something you may want to script.

Nathan