How to Fix a Missing Primary Key in WordPress Table wp

Sometimes under certain circumstances, wp_options (or any other table) can lose its primary key index.

The exact reason for that issue is not 100% clear. It seems this can happen if the database has been manually migrated from one database to another or by using a plugin or tool that did not cover all constellations, like using different MySQL versions or types like InnoDB or MyISAM.

To make sure: WP STAGING is not the reason for that error. We only help you to detect that error by showing a prominent warning in your admin dashboard when this happens:

We do this because that error is a critical and very insidious one. You will not notice it until it is too late to solve it quickly. The longer you wait to fix it, the harder it gets to solve.

That’s because your website will act nearly as before and only work differently under the hood.

Let me give you a quick example of why this then matters for the table wp_options:

The table wp_options has a column name option_id. This ID is usually unique and can only be assigned once to a row. The ID is incremented automatically for each row like this:

option_id | option_name
1 | option1
2 | option2
3 | option3

If the primary key is missing, the option_id is not incremented any longer, and all new rows will get the option_id number 0 like this:

The problem appears when you try to export and import these rows into another database. The option_id must be a unique number, so it will show SQL insert errors like “Can not insert duplicate key … ” and stop the migration.

As a result, WP STAGING and any other migration plugin cannot execute the database data transfer to another database. For instance, the pushing process from the staging site to the production site will fail.

This screenshot shows a regular wp_options table with a primary key set to the option_id column:

Find out if the primary key index is missing in WordPress database table wp_options. Image with index

This second screenshot shows the wp_options table with a missing primary key:

Find out if the table has a missing primary key index. Image with index missing

Note: You will need a database management tool in this tutorial, for example, “adminer” or PHPMyAdmin.

Rename Existing option_id’s

Back up your entire database and download it.
Modify the option_id values for all duplicated IDs.
I suggest getting the largest number you have in the option_id column and increasing it one by one. This screencast shows how you can do that:

Add Primary Key Index and Alter Table

Open phpmyadmin or adminer and navigate to the wp_options table.
Click on “alter indexes” as in this screenshot:
Then set the “option_id” as the primary key:
If you get the error “Duplicate entry ‘0’ for key PRIMARY”, it means you still have duplicate IDs in the table, and you did not change all of them:
If that is the case, find the leftovers and count up their values so there are no more duplicates in the table. After that, you can repeat to add the PRIMARY KEY to the table.