Add PRIMARY KEY and UNIQUE KEY to WordPress core database tables

Here is how to add table indexes, keys, and auto_increment fields to WordPress core database tables.

These commands will fix the following database errors caused by missing primary keys:

1. PHP errors in your error log: “WordPress database error: [Duplicate entry ‘0’ for key ‘PRIMARY’]”
2. Manually trying to re-add the primary key with increment resulting in error: “#1075 – Incorrect table definition; there can be only one auto column and it must be defined as a key”
3. Not able to create new pages, all pages have the Publish button replaced with Submit for review. No permalink, just “?preview=true”

⚠️ NOTE: Before running any of the following commands make sure to create a backup of the database. This commands will only fix your WordPress Core tables (users, options, posts etc..) it will not fix any other non-WP-Core tables (such as YoastSEO or any other plugin, or custom tables).

First login to PHPMyAdmin, select your database name and click on SQL tab on the top. Paste one of the following commands (or all 1 by 1) and click on the Go button.

After successful addition you should see a message like this:

💡 In the following snippets replace wp_ with your site prefix.  Here is a guide on where to find your own prefix.

To add primary keys to each WP-core table run:

wp_users

DELETE FROM wp_users WHERE ID = 0;
ALTER TABLE wp_users ADD PRIMARY KEY (ID);
ALTER TABLE wp_users ADD KEY user_login_key (user_login);
ALTER TABLE wp_users ADD KEY user_nicename (user_nicename);
ALTER TABLE wp_users ADD KEY user_email (user_email);
ALTER TABLE wp_users MODIFY ID bigint(20) unsigned NOT NULL auto_increment;

wp_usermeta

DELETE FROM wp_usermeta WHERE umeta_id = 0;
ALTER TABLE wp_usermeta ADD PRIMARY KEY (umeta_id);
ALTER TABLE wp_usermeta ADD KEY user_id (user_id);
ALTER TABLE wp_usermeta ADD KEY meta_key (meta_key(191));
ALTER TABLE wp_usermeta MODIFY umeta_id bigint(20) unsigned NOT NULL auto_increment;

wp_posts

DELETE FROM wp_posts WHERE ID = 0;
ALTER TABLE wp_posts ADD PRIMARY KEY (ID);
ALTER TABLE wp_posts ADD KEY post_name (post_name(191));
ALTER TABLE wp_posts ADD KEY type_status_date (post_type,post_status,post_date,ID);
ALTER TABLE wp_posts ADD KEY post_parent (post_parent);
ALTER TABLE wp_posts ADD KEY post_author (post_author);
ALTER TABLE wp_posts MODIFY ID bigint(20) unsigned NOT NULL auto_increment;

wp_comments

DELETE FROM wp_comments WHERE comment_ID = 0;
ALTER TABLE wp_comments ADD PRIMARY KEY (comment_ID);
ALTER TABLE wp_comments ADD KEY comment_post_ID (comment_post_ID);
ALTER TABLE wp_comments ADD KEY comment_approved_date_gmt (comment_approved,comment_date_gmt);
ALTER TABLE wp_comments ADD KEY comment_date_gmt (comment_date_gmt);
ALTER TABLE wp_comments ADD KEY comment_parent (comment_parent);
ALTER TABLE wp_comments ADD KEY comment_author_email (comment_author_email(10));
ALTER TABLE wp_comments MODIFY comment_ID bigint(20) unsigned NOT NULL auto_increment;

wp_links

DELETE FROM wp_links WHERE link_id = 0;
ALTER TABLE wp_links ADD PRIMARY KEY (link_id);
ALTER TABLE wp_links ADD KEY link_visible (link_visible);
ALTER TABLE wp_links MODIFY link_id bigint(20) unsigned NOT NULL auto_increment;

wp_options

DELETE FROM wp_options WHERE option_id = 0;
ALTER TABLE wp_options ADD PRIMARY KEY (option_id);
ALTER TABLE wp_options ADD UNIQUE KEY option_name (option_name);
ALTER TABLE wp_options ADD KEY autoload (autoload);
ALTER TABLE wp_options MODIFY option_id bigint(20) unsigned NOT NULL auto_increment;

wp_postmeta

DELETE FROM wp_postmeta WHERE meta_id = 0;
ALTER TABLE wp_postmeta ADD PRIMARY KEY (meta_id);
ALTER TABLE wp_postmeta ADD KEY post_id (post_id);
ALTER TABLE wp_postmeta ADD KEY meta_key (meta_key(191));
ALTER TABLE wp_postmeta MODIFY meta_id bigint(20) unsigned NOT NULL auto_increment;

wp_terms

DELETE FROM wp_terms WHERE term_id = 0;
ALTER TABLE wp_terms ADD PRIMARY KEY (term_id);
ALTER TABLE wp_terms ADD KEY slug (slug(191));
ALTER TABLE wp_terms ADD KEY name (name(191));
ALTER TABLE wp_terms MODIFY term_id bigint(20) unsigned NOT NULL auto_increment;

wp_term_taxonomy

DELETE FROM wp_term_taxonomy WHERE term_taxonomy_id = 0;
ALTER TABLE wp_term_taxonomy ADD PRIMARY KEY (term_taxonomy_id);
ALTER TABLE wp_term_taxonomy ADD UNIQUE KEY term_id_taxonomy (term_id,taxonomy);
ALTER TABLE wp_term_taxonomy ADD KEY taxonomy (taxonomy);
ALTER TABLE wp_term_taxonomy MODIFY term_taxonomy_id bigint(20) unsigned NOT NULL auto_increment;

wp_term_relationships

DELETE FROM wp_term_relationships WHERE object_id = 0;
DELETE FROM wp_term_relationships WHERE term_taxonomy_id = 0;
ALTER TABLE wp_term_relationships ADD PRIMARY KEY (object_id,term_taxonomy_id);
ALTER TABLE wp_term_relationships ADD KEY term_taxonomy_id (term_taxonomy_id);

wp_termmeta

DELETE FROM wp_termmeta WHERE meta_id = 0;
ALTER TABLE wp_termmeta ADD PRIMARY KEY (meta_id);
ALTER TABLE wp_termmeta ADD KEY term_id (term_id);
ALTER TABLE wp_termmeta ADD KEY meta_key (meta_key(191));
ALTER TABLE wp_termmeta MODIFY meta_id bigint(20) unsigned NOT NULL auto_increment;

wp_commentmeta

DELETE FROM wp_commentmeta WHERE meta_id = 0;
ALTER TABLE wp_commentmeta ADD PRIMARY KEY (meta_id);
ALTER TABLE wp_commentmeta ADD KEY comment_id (comment_id);
ALTER TABLE wp_commentmeta ADD KEY meta_key (meta_key(191));
ALTER TABLE wp_commentmeta MODIFY meta_id bigint(20) unsigned NOT NULL auto_increment;