Tag Archives: dbDelta

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.