[wp-hackers] dbDelta and duplicated indices (WAS: A Question On Plugins)

[Ryan Scheuermann]

Jamie Talbot wrote:
> Andy Skelton wrote:
>   
>> If the db version in the option is outdated or missing:
>> $my_queries = "CREATE TABLE foo (...); CREATE TABLE bar (...);"
>> require_once(ABSPATH/wp-admin/upgrade-functions.php);
>> dbDelta($my_queries);
>> update your db version option.
>>     
>
> Incidentally, (and just to hijack a little bit) does dbdelta handle multi-column primary keys?  When
> the table already exists (say I'm upgrading), I get errors like "multiple key not allowed" unless I
> turn errors off.  Also, it seems like UNIQUE indices are duplicated when calling dbdelta repeatedly.
> -  Instead of being overwritten, they are just added, without being removed first.  I'm pretty sure
> this isn't intended behaviour?  If it isn't, I'll open a ticket...  Can anyone else confirm this
> behaviour for other kinds of indices?
>
> Jamie.
>   
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.

Ryan