[wp-hackers] dbDelta and duplicated indices

[Owen Winkler]

Ryan Scheuermann wrote:
> I can confirm this for UNIQUE KEY creation.  dbDelta does not play well 
> with UNIQUE KEY at all.  I found that if you change it to simply KEY and 
> not UNIQUE KEY, dbDelta handles the recreation of the key correctly - or 
> it ignores it completely if it already exists.
> 
> It won't duplicate the unique indices for me, it simply gives me an 
> error stating I can't create an index of the same name.  Whatever the 
> symptom, dbDelta is attempting to recreate a UNIQUE KEY index every time.

I have a vague recollection of something about the structure of the SQL 
required for specifying keys.

In any case, you can see which indexes it can and can't find by 
uncommenting two lines in dbDelta().   dbDelta() attempts to build SQL 
that would create the indexes in the existing table, and then compare 
them to what it found in the SQL schema it was provided.  If what it 
generates does not exactly match what is in the schema, then it attempts 
to create the index as described in the schema.

So you can see, if that schema attempts to generate an index that 
already exists using a method that doesn't exactly match what dbDelta() 
generates, it causes an error.  There may be a way to do what you want; 
you might need to form your schema more precisely.  If dbDelta() is 
incomplete, then it can be tweaked to include it.

Owen