Using dbDelta with WordPress to create and alter tables

dbDelta is used in WordPress to create and update tables in the database and you will usually use it in the register_activation_hook with the plugin installation process. Either you want to create or update a table you always have to give dbDelta a CREATE TABLE SQL query. It will automatically detect that the table already exists in case of an update and do the necessary changes.

430px-WP3.8-ERD

This function is defined in wp-admin/includes/upgrade.php and you have to import it BEFORE calling dbDeta:

require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );

But this function is a little bit tricky. You can’t just give it any CREATE TABLE query. As pointed out in the documentation:

  • You must put each field on its own line in your SQL statement.
  • You must have two spaces between the words PRIMARY KEY and the definition of your primary key.
  • You must use the key word KEY rather than its synonym INDEX and you must include at least one KEY.
  • You must not use any apostrophes or backticks around field names.

Based on those premises, this SQL wouldn’t work and would SILENTLY do nothing:

global $wpdb;
$sql = "CREATE TABLE table_name(`id` int(10) NOT NULL AUTO_INCREMENT, `extra_id` int(5) NOT NULL,`username` tinytext NOT NULL);";

require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );

As you can see all fields are in the same line, there is no PRIMARY KEY defined and field names use backticks. The correct SQL query would be:

global $wpdb;
$sql = "CREATE TABLE table_name (
    id int(10) NOT NULL AUTO_INCREMENT,
    extra_id int(5) NOT NULL,
    username tinytext NOT NULL,
    PRIMARY KEY  (id,extra_id)
);";

require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );

I used this 2 field PRIMARY KEY example because there is another trick with dbDelta: there can’t be any spaces in the definition of the primary key.

Hope you find this useful. Those silent “errors” are pretty dificult to catch.

You might also like

Fix “Configuration of pmadb… not OK” in phpMyAdmin
You may get this message in pmpMyAdmin:To fix this you have to import/create the phpmyadmin database...

Exit maintenance mode in WordPress when an update fails
To exit maintenance mode in WordPress when an update fails is as easy as deleting the .maintenance file...

Delete WordPress old revisions
WordPress stores revisions of the posts, pages, etc. while you write them. Without even knowing you may...

Change WordPress table prefix to avoid SQL injection attacks
WordPress uses a table prefix for each installation in case you want/need to have more than one WordPress...

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.