# Problem with indexes and generateChangeLog

**URL:** <https://forum.liquibase.org/t/problem-with-indexes-and-generatechangelog/764>\
**Category:** General Discussion\
**Created:** [September 5, 2010, 12:17am UTC](https://forum.liquibase.org/t/problem-with-indexes-and-generatechangelog/764 "2010-09-05T00:17:00Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![jmglenn](https://avatars.discourse-cdn.com/v4/letter/j/ba9def/32.png) [@jmglenn](https://forum.liquibase.org/u/jmglenn)\
**Post date:** [September 5, 2010, 12:17am UTC](https://forum.liquibase.org/t/problem-with-indexes-and-generatechangelog/764/1 "2010-09-05T00:17:00Z")

</div>

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:  
&nbsp; \<?xml version="1.0" encoding="UTF-8" standalone="no" ?\>

- \<databaseChangeLog xmlns=“[http://www.liquibase.org/xml/ns/dbchangelog”](http://www.liquibase.org/xml/ns/dbchangelog%22) xmlns:xsi=“[http://www.w3.org/2001/XMLSchema-instance”](http://www.w3.org/2001/XMLSchema-instance%22) xsi:schemaLocation=“[http://www.liquibase.org/xml/ns/dbchangelog](http://www.liquibase.org/xml/ns/dbchangelog) [http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd”\>](http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd%22>)
- 
- 
- 

&nbsp;   
&nbsp;

- 

&nbsp;   
&nbsp;   
&nbsp;   
&nbsp;   
&nbsp;

- 
- 
- 

&nbsp;   
&nbsp;

- 

&nbsp;   
&nbsp;   
&nbsp;   
&nbsp;   
&nbsp;

- 

&nbsp;   
&nbsp;

- 

&nbsp;   
&nbsp;

- 
- 

&nbsp;   
&nbsp;   
&nbsp;

- 
- 

&nbsp;   
&nbsp;   
&nbsp;   
&nbsp;   
&nbsp;

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

---

<div class="post-metadata">

**Author:** ![nvoxland](https://avatars.discourse-cdn.com/v4/letter/n/87869e/32.png) [@nvoxland](https://forum.liquibase.org/u/nvoxland)\
**Post date:** [September 5, 2010, 12:17am UTC](https://forum.liquibase.org/t/problem-with-indexes-and-generatechangelog/764/2 "2010-09-05T00:17:00Z")

</div>

(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.&nbsp; 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.&nbsp;

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

Nathan