# Why does SqlDatabaseSnapshot "remove PK indexes"

**URL:** <https://forum.liquibase.org/t/why-does-sqldatabasesnapshot-remove-pk-indexes/301>\
**Category:** General Discussion\
**Created:** [October 15, 2009, 3:17pm UTC](https://forum.liquibase.org/t/why-does-sqldatabasesnapshot-remove-pk-indexes/301 "2009-10-15T15:17:00Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![tthunt](https://avatars.discourse-cdn.com/v4/letter/t/dbc845/32.png) [@tthunt](https://forum.liquibase.org/u/tthunt)\
**Post date:** [October 15, 2009, 3:17pm UTC](https://forum.liquibase.org/t/why-does-sqldatabasesnapshot-remove-pk-indexes/301/1 "2009-10-15T15:17:00Z")

</div>

I’ve create several tables in Postgres with Indexes, Primary Keys and Foreign Keys.&nbsp; When I run generateChangeLog it wasn’t creating all of the indexes that I specified in PGAdmin.&nbsp; While stepping through the LiquiBase code (v 1.9.4), I found that it is removing any indexes that are associated with Primary Keys (PKs), Foreign Keys (FKs) and Unique Constraints.&nbsp;

> &nbsp; &nbsp; &nbsp; &nbsp; Set indexesToRemove = new HashSet(); &nbsp; &nbsp; &nbsp; &nbsp; //remove PK indexes &nbsp; &nbsp; &nbsp; &nbsp; for (Index index : indexes) { &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; for (PrimaryKey pk : primaryKeys) { &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (index.getTable().getName().equalsIgnoreCase(pk.getTable().getName()) && index.getColumnNames().equals(pk.getColumnNames())) { &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; indexesToRemove.add(index); &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; for (ForeignKey fk : foreignKeys) { &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (index.getTable().getName().equalsIgnoreCase(fk.getForeignKeyTable().getName()) && index.getColumnNames().equals(fk.getForeignKeyColumns())) { &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; indexesToRemove.add(index); &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; for (UniqueConstraint uc : uniqueConstraints) { &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (index.getTable().getName().equalsIgnoreCase(uc.getTable().getName()) && index.getColumnNames().equals(uc.getColumnNames())) { &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; indexesToRemove.add(index); &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }
> 
> &nbsp; &nbsp; &nbsp; &nbsp; }  
> &nbsp; &nbsp; &nbsp; &nbsp; indexes.removeAll(indexesToRemove);

I talked with other developers and our DBA and this is not desirable, at least for what we need.

Based on the code, I don’t see an option to turn this functionality off.&nbsp; Am I using it incorrectly?&nbsp; I know that I can manually at the XML needed to create the indexes, but it seems to me that I shouldn’t have to do that.&nbsp; Is this an education issue on my part?

Thank you.

---

<div class="post-metadata">

**Author:** ![tthunt](https://avatars.discourse-cdn.com/v4/letter/t/dbc845/32.png) [@tthunt](https://forum.liquibase.org/u/tthunt)\
**Post date:** [October 15, 2009, 3:17pm UTC](https://forum.liquibase.org/t/why-does-sqldatabasesnapshot-remove-pk-indexes/301/2 "2009-10-15T15:17:00Z")

</div>

Also, to help me understand the code a bit better, what it the reasoning for removing those index types for the main list?

---

<div class="post-metadata">

**Author:** ![alexthe1st](https://avatars.discourse-cdn.com/v4/letter/a/8e8cbc/32.png) [@alexthe1st](https://forum.liquibase.org/u/alexthe1st)\
**Post date:** [October 15, 2009, 3:17pm UTC](https://forum.liquibase.org/t/why-does-sqldatabasesnapshot-remove-pk-indexes/301/3 "2009-10-15T15:17:00Z")

</div>

This bug was found in November last year and fix was sent as patch. In the latest version i see same bug.  
In index comparison index name is not taken in consideration, but only index columns which is wrong.

---

<div class="post-metadata">

**Author:** ![tthunt](https://avatars.discourse-cdn.com/v4/letter/t/dbc845/32.png) [@tthunt](https://forum.liquibase.org/u/tthunt)\
**Post date:** [October 15, 2009, 3:17pm UTC](https://forum.liquibase.org/t/why-does-sqldatabasesnapshot-remove-pk-indexes/301/4 "2009-10-15T15:17:00Z")

</div>

> _Originally posted by: alexthe1st_  
> This bug was found in November last year and fix was sent as patch. In the latest version i see same bug. In index comparison index name is not taken in consideration, but only index columns which is wrong.

Thank you alexthe1st.

Do you know what patch that is in?

---

<div class="post-metadata">

**Author:** ![alexthe1st](https://avatars.discourse-cdn.com/v4/letter/a/8e8cbc/32.png) [@alexthe1st](https://forum.liquibase.org/u/alexthe1st)\
**Post date:** [October 15, 2009, 3:17pm UTC](https://forum.liquibase.org/t/why-does-sqldatabasesnapshot-remove-pk-indexes/301/5 "2009-10-15T15:17:00Z")

</div>

My version of this code for postgresql is following

 &nbsp; &nbsp;for (Index index : indexes) { &nbsp; &nbsp; &nbsp; &nbsp;for (PrimaryKey pk : primaryKeys) { &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;if (index.getTable().getName().equalsIgnoreCase(pk.getTable().getName()) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&& index.getColumnNames().equals(pk.getColumnNames()) && index.getName().equalsIgnoreCase(pk.getName())) { &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;indexesToRemove.add(index); &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;} &nbsp; &nbsp; &nbsp; &nbsp;} &nbsp; &nbsp; &nbsp; &nbsp;for (ForeignKey fk : foreignKeys) { &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;if (index.getTable().getName().equalsIgnoreCase(fk.getForeignKeyTable().getName()) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&& index.getColumnNames().equals(fk.getForeignKeyColumns()) && index.getName().equalsIgnoreCase(fk.getName())) { &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;indexesToRemove.add(index); &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;} &nbsp; &nbsp; &nbsp; &nbsp;} &nbsp; &nbsp; &nbsp; &nbsp;for (UniqueConstraint uc : uniqueConstraints) { &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;if (index.getTable().getName().equalsIgnoreCase(uc.getTable().getName()) && index.getColumnNames().equals(uc.getColumnNames()) && index.getName().equalsIgnoreCase(uc.getName())) { &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;indexesToRemove.add(index); &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;} &nbsp; &nbsp; &nbsp; &nbsp;} &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;}

---

<div class="post-metadata">

**Author:** ![jbisotti](https://avatars.discourse-cdn.com/v4/letter/j/b9e5f3/32.png) [@jbisotti](https://forum.liquibase.org/u/jbisotti)\
**Post date:** [October 15, 2009, 3:17pm UTC](https://forum.liquibase.org/t/why-does-sqldatabasesnapshot-remove-pk-indexes/301/6 "2009-10-15T15:17:00Z")

</div>

A patch is referred to above.&nbsp; What JIRA issue is this?&nbsp; Does this affect ALL databases?

Thanks,  
&nbsp; Jamie

---

<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:** [October 15, 2009, 3:17pm UTC](https://forum.liquibase.org/t/why-does-sqldatabasesnapshot-remove-pk-indexes/301/7 "2009-10-15T15:17:00Z")

</div>

You are thinking that the indexes on FK columns etc should be created as well, right?

The trouble is that different databases have different ways they handle indexes and PKs/FKs, etc.&nbsp; For example, MySQL automatically creates indexes for FK columns and it will throw an error if you create the FK and create the index, even though both are returned through the JDBC metadata.&nbsp; SqlServer, on the other hand, does not create indexes for FK columns and so you would want to create both.&nbsp;

I think we are going to have to do a bit of work around better control of the diff tool, which is scheduled for 2.1.

I created an issue to track it: [http://liquibase.jira.com/browse/CORE-499](http://liquibase.jira.com/browse/CORE-499)

For now, you’ll have to modify your generated changelog manually (if there are not too many indexes) or patch the code to remove the check.

Nathan&nbsp;

---

<div class="post-metadata">

**Author:** ![gadge\_suffix](https://avatars.discourse-cdn.com/v4/letter/g/0ea827/32.png) [@gadge\_suffix](https://forum.liquibase.org/u/gadge_suffix)\
**Post date:** [October 15, 2009, 3:17pm UTC](https://forum.liquibase.org/t/why-does-sqldatabasesnapshot-remove-pk-indexes/301/8 "2009-10-15T15:17:00Z")

</div>

I was stopped by this issue too 😃

But I have an idea!! ;D

What if we add new attribute to index-creation xml-element, something like:

associated-with
or
key
that will contain only key-word: primaryKey, foreignKey, uniqueConstraint or none. Then fill it by these keys instead of adding index to indexesToRemove set. We just mark it!

After that we can add checking of this field in index sql-generation process and then execute or skip and ignore creation of this index according to DB type.

Results:

1. Generation of index creation will run in all cases.
2. SQL-Creation of indexes, which marked by new attribute, will run only if database rules requires manual creation of indexes for PK, FK, UC.

This solution have some bad points. It seems, that changeLog may have changeSet with task which was not executed.

If it is an acceptable way - i’ll try to fix it by my own.

---

<div class="post-metadata">

**Author:** ![gadge\_suffix](https://avatars.discourse-cdn.com/v4/letter/g/0ea827/32.png) [@gadge\_suffix](https://forum.liquibase.org/u/gadge_suffix)\
**Post date:** [October 15, 2009, 3:17pm UTC](https://forum.liquibase.org/t/why-does-sqldatabasesnapshot-remove-pk-indexes/301/9 "2009-10-15T15:17:00Z")

</div>

Hm… Only now i found: this kind of solution is not compatible for early liquibase versions…

then +1 question 🙂  
Is there some way to set requirement level of elements or attributes?  
Something like that:

usage=“optional” - then element may\_be/may\_be\_not initialized through xml description  
usage=“required” - then element must be initialized through xml description

---

<div class="post-metadata">

**Author:** ![gadge\_suffix](https://avatars.discourse-cdn.com/v4/letter/g/0ea827/32.png) [@gadge\_suffix](https://forum.liquibase.org/u/gadge_suffix)\
**Post date:** [October 15, 2009, 3:17pm UTC](https://forum.liquibase.org/t/why-does-sqldatabasesnapshot-remove-pk-indexes/301/10 "2009-10-15T15:17:00Z")

</div>

I’m ready to commit these fixations.

I added associatedWith attribute to createIndex xml-element.  
On this stage it’s only xsd:string typed value. I marked it as optional-use in xsd.  
For example:

or

By default, it have empty value:

.. associatedWith="" ..

After that - absolutely all indexes have been set to ChangeSets.

And finally, i have inserted this statement in CreateIndexGenerator.generateSql method:

if (database instanceof OracleDatabase) { List associatedWith = StringUtils.splitAndTrim(statement.getAssociatedWith(), ","); if (associatedWith.contains(Index.MARK\_PRIMARY\_KEY) || associatedWith.contains(Index.MARK\_UNIQUE\_CONSTRAINT)) { return new Sql[0]; } }

Also, I can add else statement to keep old sql-generation way for other Databases. Something like that:

if (database instanceof OracleDatabase) { List associatedWith = StringUtils.splitAndTrim(statement.getAssociatedWith(), ","); if (associatedWith.contains(Index.MARK\_PRIMARY\_KEY) || associatedWith.contains(Index.MARK\_UNIQUE\_CONSTRAINT)) { return new Sql[0]; } } else { if (associatedWith.contains(Index.MARK\_PRIMARY\_KEY) || associatedWith.contains(Index.MARK\_UNIQUE\_CONSTRAINT) || associatedWith.contains(Index.MARK\_FOREIGN\_KEY)) { return new Sql[0]; } }

Is it suitable?

---

<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:** [October 15, 2009, 3:17pm UTC](https://forum.liquibase.org/t/why-does-sqldatabasesnapshot-remove-pk-indexes/301/11 "2009-10-15T15:17:00Z")

</div>

That is a very good idea.&nbsp; Go ahead and commit it if you haven’t yet.

Sorry for the slow responses lately.&nbsp; Busy couple weeks at work…

Nathan

---

<div class="post-metadata">

**Author:** ![gadge\_suffix](https://avatars.discourse-cdn.com/v4/letter/g/0ea827/32.png) [@gadge\_suffix](https://forum.liquibase.org/u/gadge_suffix)\
**Post date:** [October 15, 2009, 3:17pm UTC](https://forum.liquibase.org/t/why-does-sqldatabasesnapshot-remove-pk-indexes/301/12 "2009-10-15T15:17:00Z")

</div>

I fixed it and commited.

Pls, monitor this commit carefully 🙂 I’m afraid to break something ))

---

<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:** [October 15, 2009, 3:17pm UTC](https://forum.liquibase.org/t/why-does-sqldatabasesnapshot-remove-pk-indexes/301/13 "2009-10-15T15:17:00Z")

</div>

Thanks, I’ll take a look.&nbsp; It looks like it’s causing derby to fail, I can fix that up.&nbsp; For future reference, the H2IntegrationTest, HsqlIntegrationTest and DerbyIntegrationTest classes will run using embedded databases so you can test against a few other systems without needing to install anything.

Nathan

---

<div class="post-metadata">

**Author:** ![ekupcik](https://avatars.discourse-cdn.com/v4/letter/e/73ab20/32.png) [@ekupcik](https://forum.liquibase.org/u/ekupcik)\
**Post date:** [October 15, 2009, 3:17pm UTC](https://forum.liquibase.org/t/why-does-sqldatabasesnapshot-remove-pk-indexes/301/14 "2009-10-15T15:17:00Z")

</div>

The latest version is causing a NPE when the associatedWith attribute is undefined in combination with Oracle. I fixed it in SVN

---

<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:** [October 15, 2009, 3:17pm UTC](https://forum.liquibase.org/t/why-does-sqldatabasesnapshot-remove-pk-indexes/301/15 "2009-10-15T15:17:00Z")

</div>

Thanks for fixing that.

Nathan