index created on column with foreign key constraints does not get saved to changelog

General Discussion
1

So it seems there is common misconception regarding foreign key and index?   I came across following discussion which seems to indicate that is the case.   


Does a foreign key automatically create an index?

2

I’m experimenting with generateChangeLog to see if I can move the management of my MS SQL database to Liquibase.  


After doing generateChangeLog and then creating new database with “update”, I noticed that a few of the index didn’t make into the new database.   


After further investigation, I concluded that the index created on a column which has foreign key constants does not get saved off to change log with generateChangeLog.   


This is done in DiffResult.addMissingIndexChanges as below:


  1.             if (index.getAssociatedWith().contains(Index.MARK_PRIMARY_KEY) ||                   index.getAssociatedWith().contains(Index.MARK_FOREIGN_KEY) ||                   index.getAssociatedWith().contains(Index.MARK_UNIQUE_CONSTRAINT)) {
  2.                 continue;
  3.             }


Here is a sample database to demonstrate this.  When generateChangeLog is run with this database, the index “move_person_id_idx” makes into the changelog but move_location_id_idx does not. 


  2. create table location (
  3.        id bigint not null,
  4.        name varchar(100) not null,
  5. );

  7. alter table location add constraint location_id_pk primary key (id);

  9. create table person (
  10.       id bigint not null,
  11.       name varchar(100) not null,
  12. );

  14. create table move (
  15.       id bigint not null,
  16.       location_id bigint,
  17.       person_id bigint,
  18. );

  20. alter table move add constraint move_location_id_fk foreign key (location_id) references location(id);
  21. create index move_location_id_idx on move(location_id);

  23. create index move_person_id_idx on move(person_id);


Why is the index explicitly removed from diff if it is associated with foreign key?   


Hiroto