Custom database table for plugin not creating on activation

Question

Let me start by saying I know there are other posts about this, and a lot of documentation is available for this topic, but none of these resources have helped me to solve my issue.

I'm a beginner at creating plugins for WordPress. This is my first, so I'm sure I'm missing something simple, but everything I have read says this should be working.

The plugin activates no problem, my menu shows up, etc. But the following does not create a new table in the database. I removed everything besides the id just to see if it was the SQL syntax, still no results.

register_activation_hook( __FILE__, 'pf_rb_install' );      
function pf_rb_install() {
    global $wpdb;

    $table_name = $wpdb->prefix . 'pf_parts';
    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE '$table_name' (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            PRIMARY KEY (id)
            ) $charset_collate;";

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

Thanks in advance for any help.

Answer 1

Here's an updated version of pf_rb_install().

In the table creation SQL, $table_name should not be in quotes. The quotes should have triggered an error which would appear in PHP's error.log file. Also, there should be two spaces after the PRIMARY KEY (id) portion of the SQL statement.

register_activation_hook( __FILE__, 'pf_rb_install' );
function pf_rb_install() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'pf_parts';
    $pf_parts_db_version = '1.0.0';
    $charset_collate = $wpdb->get_charset_collate();

    if ( $wpdb->get_var( "SHOW TABLES LIKE '{$table_name}'" ) != $table_name ) {

        $sql = "CREATE TABLE $table_name (
                        id mediumint(9) NOT NULL AUTO_INCREMENT,
                        PRIMARY KEY  (id)
                        ) $charset_collate;";

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

The code also has a version number added which will help if modifications are made to the database schema in the future. Finally, a check was added so that the database creation code is only run if the table does not exist. More on creating custom tables can be found in the Codex article Creating Tables with Plugins.

Answer 2

Looks like your SQL statement is a little off. Try this:

function pf_rb_install() {
    global $wpdb;

    $table_name = $wpdb->prefix . 'pf_parts';
    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE $table_name (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            PRIMARY KEY  (id)
            )
            COLLATE $charset_collate";

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

Note: PRIMARY KEY MUST have two spaces after it.