dbDelta error on plugin activation/deactivation/reactivation

[colinhahn]

When a plugin creates a table on activation via dbDelta, dbDelta results in an error on a plugin deactivation/reactivation cycle.

The attached minimalist code demonstrates the problem. The plugin creates the table fine, on reactivation the following error results:

<div id='error'>
	<p class='wpdberror'><strong>WordPress database error:</strong> [Multiple primary key defined]<br />
	<code>ALTER TABLE testsite_minimalist ADD PRIMARY KEY  id (id)</code></p>
</div>

Expected result is no error: dbDelta should not try to define the primary key a second time (or should drop the primary key before attempting to alter the table).

[colinhahn]

Minimalist demonstration of dbDelta error

[SergeyBiryukov]

Related: #10404, #10693.

[jdgrimes]

According to ​the MySQL manual, the syntax for the PRIMARY KEY is like this:

PRIMARY KEY [index_type] (index_col_name,...)

And for reference, the index_type and index_col_name definitions:

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

So your SQL is not correct:

PRIMARY KEY  id (id)

I've actually been doing mine the same way, but now I realize it's not right. You can't name a PRIMARY key, it's name is always PRIMARY (​1). The only think that should be between there is the index_type. So the correct syntax is:

PRIMARY KEY  (id)

And indeed, this works with dbDelta() and doesn't cause this error.