MySQLDump – Changes Primary Key to Unique Key Issue

MySQLmysqldump

While attempting to dump and restore a WordPress database in MySQL, I noticed that mysqldump seems to be changing the PRIMARY KEY on several tables to a UNIQUE KEY instead. For example, this tables:

mysql> describe wp_yoast_seo_meta;
+---------------------+---------------------+------+-----+---------+-------+
| Field               | Type                | Null | Key | Default | Extra |
+---------------------+---------------------+------+-----+---------+-------+
| object_id           | bigint(20) unsigned | NO   | PRI | NULL    |       |
| internal_link_count | int(10) unsigned    | YES  |     | NULL    |       |
| incoming_link_count | int(10) unsigned    | YES  |     | NULL    |       |
+---------------------+---------------------+------+-----+---------+-------+

Is dumped as:

CREATE TABLE `wp_yoast_seo_meta` (
  `object_id` bigint(20) unsigned NOT NULL,
  `internal_link_count` int(10) unsigned DEFAULT NULL,
  `incoming_link_count` int(10) unsigned DEFAULT NULL,
  UNIQUE KEY `object_id` (`object_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Notice the object_id field is used to create a UNIQUE_KEY index, instead of being set as the PRIMARY KEY. I'm not using any special options to mysqldump other than specifying the host name and user name.

How can I get mysqldump to include the primary keys?

Best Answer

In MyISAM, there is no implementation difference between PRIMARY and UNIQUE. (I don't know why it would do what it did.)

You should upgrade to InnoDB.