How to Fix a Missing Primary Key in WordPress Table wp_options

TL;DR: A missing primary key on the wp_options table means MySQL stops auto incrementing option_id, so new rows are written with option_id = 0. WordPress keeps working, but every export, migration, or staging push fails with a duplicate key error, and queries against the table get slower because MySQL can no longer use the key. The fastest fix: back up the database, renumber the duplicate option_id values so each is unique, then run ALTER TABLE wp_options ADD PRIMARY KEY (option_id) and restore AUTO_INCREMENT. You can do the whole repair in phpMyAdmin or with raw SQL, and no plugin is required.

Sometimes, under certain circumstances, the wp_options table (or any other table) can lose its primary key index. The exact trigger is rarely obvious. In WP STAGING support tickets, we most often see this on databases that were manually migrated from one server to another, or moved with a tool that did not account for every case, such as a switch between MySQL versions or between the InnoDB and MyISAM storage engines.

To be clear: WP STAGING is not the cause of this error. WP STAGING only helps you detect it, by showing a prominent warning in your WordPress admin dashboard when it finds a core table with no primary key. We surface it early because this is a critical and insidious problem. Your site keeps loading as if nothing is wrong, so you will not notice it until a migration or backup fails, and by then the table may hold hundreds of broken rows. The longer it goes unfixed, the more duplicate rows accumulate and the more work the repair becomes.

What a primary key is and why wp_options needs one

A primary key is a column (or set of columns) that uniquely identifies every row in a table. In a standard WordPress install, the wp_options table uses option_id as its primary key. option_id is defined as bigint(20) unsigned NOT NULL auto_increment, which means the database assigns each new row the next unused number automatically and guarantees no two rows share the same value.

Two things break when that key disappears:

• Auto increment stops. Without the primary key, MySQL no longer increments option_id. Every new row is inserted with the default value 0. The original example below shows what a healthy sequence looks like:And here is what the same column looks like once the key is gone and new rows pile up with option_id = 0:
• Exports and migrations fail. A primary key column must hold unique values. The moment you export these rows and import them into another database, MySQL rejects the duplicates with an error such as Cannot insert duplicate key, and the import stops partway through. This is why WP STAGING and every other migration plugin cannot complete a database transfer when the key is missing. The push from a staging site to production fails at exactly the point the duplicate 0 rows are written.

There is also a quieter cost. wp_options is one of the most read tables in WordPress because autoloaded options are fetched on nearly every request. In our testing, a missing primary key forces MySQL to fall back to a full table scan for lookups that would otherwise use the key, which adds measurable overhead to ordinary page loads. If you want background on how wp_options fits into the wider schema, see our guide to the essential WordPress database tables.

How to confirm the primary key is actually missing

Before you change anything, confirm this is really your problem. Open a database tool such as phpMyAdmin or Adminer, select your WordPress database, and run:

SHOW CREATE TABLE wp_options;

In a healthy table the output contains a line like PRIMARY KEY (option_id) and the option_id column is marked AUTO_INCREMENT. If both are missing, the key is gone. You can also list the indexes directly:

SHOW INDEX FROM wp_options;

A table with an intact key returns a row where Key_name is PRIMARY. If that row is absent, there is no primary key.

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

This second screenshot shows the same table with the primary key missing:

To see how many rows are already affected, count the duplicates:

SELECT option_id, COUNT(*) AS copies
FROM wp_options
GROUP BY option_id
HAVING copies > 1;

Any group with option_id = 0 and a count above one confirms the broken rows you need to renumber before the key can be restored.

Replace wp_options with your real table name throughout if your install uses a custom table prefix (for example wp_xyz_options).

Before you start: back up the database

Every step below modifies the database directly, so take a full backup first and download it off the server. If anything goes wrong you want a clean copy to restore. A WP STAGING backup, a phpMyAdmin export, or a mysqldump are all fine. This is also a good moment to do the work on a staging copy rather than production: clone the site, repair the table there, confirm the result, and only then apply the same fix to the live database.

Fix the missing primary key in phpMyAdmin

If you prefer a graphical interface over raw SQL, phpMyAdmin handles the whole repair from the Structure tab.

1. Open phpMyAdmin or Adminer and navigate to the wp_options table.
2. First deal with the duplicate IDs. Take the largest existing value in the option_id column and renumber each 0 row one by one so every value is unique again. This screencast shows the renumbering:
3. Open the table indexes editor:
4. Set option_id as the primary key:
5. If you see the error Duplicate entry '0' for key 'PRIMARY', some duplicate IDs are still in the table:
6. Find the leftover duplicates and count their values up so none repeat, then add the primary key again. Once it succeeds, re-enable auto increment on option_id so future rows number themselves correctly.

Fix the missing primary key with SQL

The same repair takes three statements if you are comfortable with the SQL console. Run them in order against your WordPress database.

First, give every broken 0 row a fresh unique number by continuing from the current maximum option_id:

SET @next := (SELECT MAX(option_id) FROM wp_options);
UPDATE wp_options
SET option_id = (@next := @next + 1)
WHERE option_id = 0;

Next, add the primary key now that every value is unique:

ALTER TABLE wp_options ADD PRIMARY KEY (option_id);

Finally, restore auto increment so WordPress numbers new options automatically again:

ALTER TABLE wp_options MODIFY option_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;

The official syntax for these statements is documented in the MySQL manual under ALTER TABLE. For the canonical definition of the wp_options columns, the WordPress database description lists the exact column types WordPress core expects.

Verify the fix

Confirm the repair held before you trust the table:

SHOW CREATE TABLE wp_options;

The output should now include PRIMARY KEY (option_id) and show AUTO_INCREMENT on the option_id column. As a final check, insert and delete a throwaway option, or simply save a setting in WordPress, and confirm the new row receives a unique incrementing option_id rather than another 0.

With the key back in place you can run the migration or staging push that previously failed. Even so, take a fresh backup before the push so you have a recovery point that includes the repaired table.

What to do if ALTER TABLE fails

The happy path covers most installs, but a few environments need extra handling. Work through these in order:

• Duplicate entry '0' for key 'PRIMARY'. Not every duplicate was renumbered. Re-run the duplicate count query from the diagnostic section, renumber the remaining rows, then add the key again. This is the single most common reason the ALTER TABLE step refuses to run.
• Lock wait timeout on a large table. ALTER TABLE rewrites the table and holds a lock while it runs. On a large wp_options table, or on a busy production server, the statement can time out. Run the repair during low traffic, on a staging clone, or use an online schema change tool such as pt-online-schema-change if your host provides it.
• MyISAM instead of InnoDB. The steps work on both engines, but WordPress runs best on InnoDB. If SHOW CREATE TABLE wp_options reports ENGINE=MyISAM, consider converting after the key is in place with ALTER TABLE wp_options ENGINE=InnoDB. Take a backup first, because the conversion also rewrites the table.
• Hosting blocks schema changes. Some managed hosts restrict direct DDL statements or run the database under a user without ALTER privileges. If the statement fails with a permissions error, ask your host to run the three statements for you, or to grant the privilege temporarily.

A failed ALTER TABLE is almost always one of these four cases. A reader tracking the underlying SQL error in the server log may find it surfaced in the WordPress debug output too; our guide on how to spot a problem in the WordPress database tables covers turning that logging on.

Why this matters for migrations and pushes

A missing primary key is one of the quieter reasons a site move stalls. If a push from staging to live times out or aborts mid-transfer, the wp_options table is a prime suspect, especially when serialized option values are involved and the row count is large. The same applies when you change hosts: readers wrestling with siteurl and redirect loops after a move, covered in our guide to migrating siteurl in wp_options, are exactly the audience who may also be carrying a broken primary key from the old server. Fixing the key first removes a whole class of migration failures before they start.

Related Articles

• The WordPress Database Structure
• Push Changes From Staging to Production
• How to Migrate Your WordPress Site to a New Host
• Skip WooCommerce Orders and Products
• How to Find the WordPress Version Number of Your Website