How do I check if an Index was system-generated?

[cpjust]

Hi,
I've noticed that when I create a Unique Constraint, MySQL will automatically create an Index with the same name.

When I query for a list of Indexes, what I need to do is find out which ones were system-generated and which were actually defined by me.

I took a look at INFORMATION_SCHEMA.STATISTICS, but I don't see anything in there that could distinguish who created the Indexes.

 

[guelphdad]

MySQL would need to create the index in order to keep track of whether or not an item being added is unique.

Why do you need to differentiate them? YOU should be adding them at the time you create your table or using an ALTER TABLE statement, there's no real difference whether it is created automatically or declared by you though.

 

[cpjust]

The reason I need to know if it's system-generated is because if it is, the Index will automatically be deleted if I delete the Unique Constraint, but if it's user-generated it won't be deleted automatically.

I'm trying to fix the Liquibase code which synchronizes schema changes to the database. If it sees that a Unique Constraint was removed from the schema, it will try to delete it from the database; so it needs to know whether it should also delete the Index or not...

I found a way to distinguish system vs user Indexes for DB2 here:

http://liquibase.org/forum/index.php?topic=673.0

but not for MySQL.

 

[ingresman]

I suppose you could do it by name convention i.e. indexs you create will have a prefix such ad PK, so anything without that prefix is system generated.
I had a bit of a play with a table and if I did this:

 alter table skill add constraint unique index(skillid);

This will create a system-generated constraint index because I didn't give it a name. If I then query the table information_schema.key_column_usage with

 select * from key_column_usage where table_name = "skill"

I get

*************************** 2. row ***************************
CONSTRAINT_CATALOG: NULL
CONSTRAINT_SCHEMA: skill
CONSTRAINT_NAME: skillID
TABLE_CATALOG: NULL
TABLE_SCHEMA: skill
TABLE_NAME: skill
COLUMN_NAME: skillID
ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: NULL
REFERENCED_TABLE_SCHEMA: NULL
REFERENCED_TABLE_NAME: NULL
REFERENCED_COLUMN_NAME: NULL

as the result, which names CONSTRAINT_NAME as skillID (which is my column name)
If do this:

 alter table skill add constraint PK1 unique index(skillid);

This creates an explicity names constraint index PK1, querying key_column_usage gives:

*************************** 3. row ***************************
CONSTRAINT_CATALOG: NULL
CONSTRAINT_SCHEMA: skill
CONSTRAINT_NAME: PK1
TABLE_CATALOG: NULL
TABLE_SCHEMA: skill
TABLE_NAME: skill
COLUMN_NAME: skillID
ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: NULL
REFERENCED_TABLE_SCHEMA: NULL
REFERENCED_TABLE_NAME: NULL
REFERENCED_COLUMN_NAME: NULL

Note the CONSTRIANT_NAME is PK1. So I reckon if you explicity give a name to each constraint you create, you should be able to get it to work. And beware primary keys which have a CONSTRIANT_NAME of PRIMARY. If you go down this road have a good test first !
Must admit I'm a bit fuzzy on what you are actualy doing e.g. schema, database ?
Any how hope this helps

 

[cpjust]

Actually I came up with my own hack by checking if the name starts with 'SYS_IDX' (although I can't remember if it was MySQL, HSQL or both that gave system-generated Indexes with that prefix).
I also assume the Index is system-generated if the Index Name == Unique Constraint name & Index table == Unique Constraint table & Index columns == Unique Constraint columns.

My tool allows you to do a diff of a database to another database or a database to an XML schema definition, then it generates the SQL statements required to make the target database look like the schema file or other database.