WordPress dbDelta returns a SQL error when updating a table. It returns “Multiple primary key defined“.
I was stuck for two days on this problem. WordPress shown me following error message on plugin activation
I searched for the main cause. I edited WordPress core file to save the output that was being generated on plugin activation. I found WP returns following MySQL error on behind.
<p class=’wpdberror’><strong>WordPress database error:</strong> [Multiple primary key defined]<br />
<code>ALTER TABLE wp_my_stat CHANGE COLUMN id `id` bigint(20) NOT NULL PRIMARY KEY auto_increment</code></p>
But I could not find the sql syntax error in my query. My query is bellow.
$my_stat_sql = "CREATE TABLE IF NOT EXISTS `$my_stat_table` ( `id` bigint(20) NOT NULL auto_increment, `cid` bigint(20) NOT NULL, `v_time` datetime NOT NULL, `ip` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; "; dbDelta($my_stat_sql);
Are you see any error on above codes? probably not.
Finally I got the solution.
Actually WP dbDelta can not handle when you use PRIMARY KEY (`id`) with back-tick (`) any single or double quote , or space between columns names like PRIMARY KEY (“id, pid”).
I removed the back-ticks (`) from my query when assigning the primary keys. PRIMARY KEY (id). My issue has been solved. My new codes is bellow
$my_stat_sql = "CREATE TABLE IF NOT EXISTS `$my_stat_table` ( `id` bigint(20) NOT NULL auto_increment, `cid` bigint(20) NOT NULL, `v_time` datetime NOT NULL, `ip` varchar(20) NOT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; "; dbDelta($my_stat_sql);
More problem I found. My plugin returns error on installation yet. Got error : [Multiple primary key defined]
ALTER TABLE my_stat_table ADD PRIMARY KEY (id)
After a long experiment I decided to use UNIQUE KEY.
Now I am using UNIQUE KEY id (id) instead of PRIMARY KEY (id)
because dbDelta were returning error “[Multiple primary key defined]”
Difference between PRIMARY KEY and UNIQUE KEY
Primary keys and unique keys are similar. A primary key is a column, or a combination of columns, that can uniquely identify a row. It is a special case of unique key. A table can have at most one primary key, but more than one unique key. When you specify a unique key on a column, no two distinct rows in a table can have the same value.
Read : Discussion 1 , Discussion 2
I saw my plugin table is not being updated with new column on plugin activation.
I stopped using “IF NOT EXISTS” with create table because, if I use this, dbdelta don’t update my table.
I removed all back tick(`) from all column name and table name to be more safe.
SO NOW MY FINAL ERROR FREE CODE IS :
$my_stat_sql = "CREATE TABLE IF NOT EXISTS $my_stat_table ( id bigint(20) NOT NULL auto_increment, cid bigint(20) NOT NULL, v_time datetime NOT NULL, ip varchar(20) NOT NULL, UNIQUE KEY id (id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; "; dbDelta($my_stat_sql);
Read more detail about WordPress dbDelta Function and core documentation about dbDelta and creating tables with plugin
Thanks for the information – just spent a couple of hours with with the exact same problem. Now with no back ticks anywhere and UNIQUE rather than PRIMARY it’s all good.
Thanks, It was really helpful…
Same Problem. Using UNIQUE rather than PRIMARY worked fine.
Changing PRIMARY KEY to UNIQUE is not a proper fix. These two keys are very different.
Both Primary and Unique Key is implemented for Uniqueness of the column. Primary Key creates a clustered index of column where as an Unique creates un-clustered index of column. Moreover, Primary Key doesn’t allow NULL value, however Unique Key does allows one NULL value.
Clustered index (Primary Key):
– only 1 per table;
– Faster to read than non clustered as data is physically stored in the index order
Nonclustered index (Unique):
– Can be used many times per table;
– Quicker for insert and update operations than clustered index
So this work-a-round is not a proper fix but a alternative solution. Depending on the plugin, this fix may be fine but this is not the correct solution.
The correct fix is to not use back-ticks and to make sure you have 2 spaces after PRIMARY KEY and the column name; unlike UNIQUE, you do not specify the key name:
PRIMARY KEY (id) — Correct (with 2 spaces)
PRIMARY KEY id (id) — Not correct, will throw an error due to name of the key
I have developed hundreds of plugins and have never had an issue with dbDelta using PRIMARY KEYS. The issue is almost always caused by the developer and not a problem with the WordPress dbDelta function.
To clarify, do not use back-ticks at all in the entire query and use 2 spaces between PRIMARY KEY and the (column)
Thanks! I could not accept the “change PRIMARY to UNIQUE” solution as well
As awkward as it sounds – the 2 space solution actually worked