WordPress Plugin Won’t Create Tables

I’ve built a litany of WordPress plugins over the last five years, but one thing that always got me time and time again was the functionality of having my plugins create their own database tables. It’s typically recommended that you avoid creating tables with your WordPress plugins if possible, utilizing resources like the options and meta tables, but sometimes you must. Here are some tips I always find myself falling back to when I can’t quite seem to get my WordPress plugin to create tables in the database upon installation.

Make Your WordPress Plugin Create Tables

Pay attention to the following working PHP and SQL statement:

// Load global DB object
global $wpdb;

// Create the database table. Change your_table_name to your actual table name
$sql = "CREATE TABLE {$wpdb->prefix}your_table_name (
  referral_id bigint(9) NOT NULL auto_increment,
  order_id bigint(9) NOT NULL,
  user_id bigint(9) NOT NULL,
  date_completed datetime default '0000-00-00 00:00:00' NOT NULL,
  status varchar(24) NOT NULL,
  total_amount varchar(24) NOT NULL,
  disbursed boolean NOT NULL,
  PRIMARY KEY  (referral_id),
  KEY referral_id (referral_id)
);";

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

Some things to note here are:

• Every column statement is on its own line.
• I surrounded the $wpdb->prefix portion with curly brackets. This one was big for me.
• I transformed certain commands to lowercase that otherwise might be uppercase, such as ‘auto_increment’.
• There are no apostrophes or quotes around any of the column name declarations.
• Unlike the WordPress.org tutorial, I am using PRIMARY KEY instead of UNIQUE KEY. I am also re-declaring the key below this with KEY key_id (key_id).
• I have two spaces after the PRIMARY KEY declaration.

WordPress’ dbDelta Function Is Tricky, But It May Be Your SQL Statement

One thing I always do is run my SQL statement in phpMyAdmin to see if it works before trying it in the actual plugin activation hook. Often times this will point out that I have malformed commands.

I hope this helps! Good luck and let me know how you do.