DBDelta: "table doesn't exist" for a table that was just created

Question

When I run the following code:

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

    $table_tags = $wpdb->prefix . "tags_tb";
    $sql = "CREATE TABLE $table_tags (
                tag text,
                tag_id tinyint,
                UNIQUE KEY id (id)
            ) $charset_collate"; 
    $array = dbDelta( $sql );
    print_r(array_values($array));

    echo $wpdb->insert( 
         $table_tags, 
         array( 
               'tag_id' => 1, 
               'tag' => "#AireLibre"
            ) 
         );  
    echo $wpdb->last_error;

I get the following output:

(
    [0] => Created table wp_tags_tb
)
Table 'acsm_28e28895e1cdaee.wp_tags_tb' doesn't exist

I think this doesn't make any sense. The table was created right but immediately when performing a query it doesn't exist.

Why can this happen?

Answer 1

Glancing at your code I see a few potential problems. According to this Codex page dbDelta is "picky";

• 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.

• Field types must be all lowercase.

• SQL keywords, like CREATE TABLE and UPDATE, must be uppercase.

• You must specify the length of all fields that accept a length parameter. int(11), for example.

The code you provided shows a unique key on a field that has not been created, there must be 2 spaces between KEY and the field name. There should be lengths specified where appropriate.

This Code Reference page show where dbDelta is used by WP. Taking a look at those functions might help in dealing with dbDeltas "pickiness".

EDIT:

Here's an example from upgrade.php

function install_global_terms() {
    global $wpdb, $charset_collate;
    $ms_queries = "
CREATE TABLE $wpdb->sitecategories (
  cat_ID bigint(20) NOT NULL auto_increment,
  cat_name varchar(55) NOT NULL default '',
  category_nicename varchar(200) NOT NULL default '',
  last_updated timestamp NOT NULL,
  PRIMARY KEY  (cat_ID),
  KEY category_nicename (category_nicename),
  KEY last_updated (last_updated)
) $charset_collate;
";
// now create tables
    dbDelta( $ms_queries );
}