0

I know the specifics of using dbDelta and I've read the documentation (http://codex.wordpress.org/Creating_Tables_with_Plugins), read other answers to the question I'm asking, but I'm just not seeing what I'm doing wrong. When I var_dump the result of dbDelta, I get no errors, just "array(1) { [0]=> string(48) "Added index wp_ovrdrvamdata PRIMARY KEY id (id)" }" or some other untrue statement. The table just is not being created.

Here is the code/query:

function ovrdrv_asset_install() {
    global $wpdb;
    global $ovrdrv_am_db_version;
    $wpdb->show_errors();
    $data_table_name = $wpdb->prefix . 'ovrdrvamdata';


    $sql = "CREATE TABLE ". $data_table_name ." (
            id int(9) NOT NULL AUTO_INCREMENT,
            data varchar(255) NOT NULL,
            form_id smallint(4) NOT NULL,
            PRIMARY KEY  id (id)
        );";

    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    var_dump(dbDelta( $sql ));
    $wpdb->print_error();
    add_option('ovrdrv_am_db_version', $ovrdrv_am_db_version);
}

EDIT: After turning on WPDB errors, I'm getting these errors:

WordPress database error: [Multiple primary key defined]
ALTER TABLE wp_ovrdrvamdata ADD PRIMARY KEY id (id) /* From             [client.staging.wpengine.com/wp-admin/plugins.php?activate=true&plugin_status=all&paged=1&s=] in [/nas/wp/www/staging/client/wp-content/plugins/client-plugin/table-creation.php:22] */
array(1) { [0]=> string(48) "Added index wp_ovrdrvamdata PRIMARY KEY id (id)" }

WordPress database error: [Multiple primary key defined]
ALTER TABLE wp_ovrdrvamdata ADD PRIMARY KEY id (id) /* From [client.staging.wpengine.com/wp-admin/plugins.php?activate=true&plugin_status=all&paged=1&s=] in [/nas/wp/www/staging/client/wp-content/plugins/client-plugin/table-creation.php:22] */

It seems as if it's trying to add the primary key multiple times or something, even though the table does not exist yet.

Any help would be appreciated.

  • Please post more complete code so we can see what the surrounding code is doing. We have no idea what $data_table_name is here, or how you're using that SQL in the context of the plugin. – Otto Sep 26 '14 at 23:01
  • More code has been added. – Domenic Fiore Sep 27 '14 at 15:31
4

This is invalid.

PRIMARY KEY id (id)

Should just be:

PRIMARY KEY (id)

Unlike other KEYs, PRIMARY KEYs don't have identifiers or names. They don't need them, because you can only have one.

  • Thanks, good eye. I copied and pasted code from the documentation and didn't even think to change that, as the documentation has UNIQUE KEY instead of PRIMARY KEY. Lesson learned. – Domenic Fiore Sep 27 '14 at 20:01
  • Thanks to this answer. The double space after the word PRIMARY KEY is necessary to prevent error "WordPress database error Multiple primary key defined for query ALTER TABLE". – vee Jun 15 '16 at 6:55
1

If you're throwing an error on your SQL statement, you should refer to http://codex.wordpress.org/Class_Reference/wpdb#Show_and_Hide_SQL_Errors for advice on how to identify it. It's not always apparently obvious that an error has occurred if you don't go looking for it.

  • Yeah, I got some errors, but they don't make sense to me. Check it out. – Domenic Fiore Sep 27 '14 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.