[wp-hackers] dbDelta and duplicated indices

[Ryan Scheuermann]

Owen Winkler wrote:
> 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
>
You're right.  I know when I started using dbDelta for plugin tables, I 
had to really play around with the SQL syntax to get it to work just 
right.  It seems to be quite finicky even with spaces in the wrong 
places.  If there is a correct syntax for specifying UNIQUE KEY, I 
couldn't figure it out - but I didn't spend all that much time 
investigating the code or uncomment those lines and compare the statements.

Maybe if we are going to recommend plugin authors to use dbDelta, we 
should have some documentation on its precise syntax?  Might benefit 
those not willing to fiddle and peruse the core code until it works?  
"We" referring to the collective of WP developers/documenters.  :-)

Ryan