WordPress dbDelta Database Error Multiple Primary Key Defined

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

The plugin generated 202 characters of unexpected output during activation. If you notice “headers already sent” messages, problems with syndication feeds or other issues, try deactivating or removing this plugin.


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.

<div id=’error’>
<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>
</div>

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

4 Comments


  1. 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.

    Reply

  2. Same Problem. Using UNIQUE rather than PRIMARY worked fine.

    Reply

  3. 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.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *