WP dbDelta function cannot modify unique keys

Whilst making some updates to one of my WordPress plugins earlier today, I discovered an small issue when trying to make use of the dbDelta function.

Although it’s advised you avoid creating tables unless absolutely necessary, it seemed like the correct method to store the data used by this particular plugin.

This method of storage does have it’s downsides, especially when it comes to modifying the table structure. Luckily the dbDelta function comes in really handy in aiding the update process.

There is however one issue that I came across, discovering that is unable to modify a unique key (in this situation a composite index).

After browsing through the source code for the dbDelta function I spotted that something was missing. When including an index in the query, it is not dropped before attempting to recreate it and is therefore ignored when performing an update.

Take for example the following code snippet:

global $wpdb;
$table = $wpdb-prefix.'mytable';
$sql = "CREATE TABLE $table (
	id mediumint(9) NOT NULL AUTO_INCREMENT,
	calendar varchar(255) NOT NULL default 'default',
	year smallint(5) NOT NULL,
	month tinyint(3) NOT NULL,
	day tinyint(3) NOT NULL,
	PRIMARY KEY (id),
	UNIQUE KEY date (year,month,day)
);";
require_once(ABSPATH.'wp-admin/includes/upgrade.php');
dbDelta($sql);

If we make some changes to the SQL statement to add an additional column to the unique index, the index would become something like “UNIQUE KEY date (year,month,day,calendar)“.

This will work if the the table does not already exist, but if it attempts to update the table then the changes to the unique key are ignored.

The short and simple solution is to simply check the table exists and then drop the index manually before calling the dbDelta function.

if ($wpdb->get_var("SHOW TABLES LIKE $table") == $table) {
	$wpdb->query("ALTER TABLE $table DROP INDEX date");
}

I’m not sure if this is actually a bug with the function or whether it wasn’t supposed to allow for updates on indexes in the first place, either way the problem can be quite easily resolved.

Leave a Reply