dbDelta given the same PRIMARY KEY and normal KEY produces no error, reports table created successfully, but the table is not created

[radgh]

I spent about 45 minutes trying to find out why one of several dbDelta functions was not creating a table. It wasn't giving a warning, and in fact was saying "Created table wp_dtl_exam_resultmeta". But the table was not created.

I eventually found the problem which was that I had a key called "meta_id" and "answer_id" and simply had put the wrong primary key, which should have been meta_id. But I had:

PRIMARY KEY (answer_id),
KEY answer_id (answer_id)

This gives a success message without creating a table - which doesn't seem right. Of course this isn't a problem with WordPress, it is a problem with my code. But it seems a check is missing from dbDelta, which is what I want to report.

Here is the full code to reproduce the problem, just put it in a plugin or functions.php:

<?php

if ( !defined( 'ABSPATH' ) ) exit;

/**
 * Creates/updates custom database tables for the plugin:
 *  -> wp_dtl_exam_results
 *  -> wp_dtl_exam_resultmeta
 */

function example_database_creation_for_wordpress_trac() {
    global $wpdb;
    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
        
    $charset_collate = $wpdb->get_charset_collate();

    $table_name = $wpdb->prefix . 'dtl_exam_resultmeta';
        
    // MY ERROR IS: PRIMARY KEY (answer_id),
    // IT SHOULD BE: PRIMARY KEY (meta_id),
    // But the dbDelta function doesn't notice that, and says it created a table when it didn't.

    $sql = <<<MYSQL
CREATE TABLE $table_name (
  meta_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  answer_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
  meta_key VARCHAR(255) NULL DEFAULT NULL,
  meta_value LONGTEXT NULL,
  PRIMARY KEY (answer_id),
  KEY answer_id (answer_id),
  KEY meta_key (meta_key)
) $charset_collate;
MYSQL;
        
    $result = dbDelta( $sql );

    // Debug this:
    echo '<pre>';
    echo 'The table creation SQL:' . "\n\n";
    echo $sql;

    echo "\n -- \n";

    // This should give some warning or at least say table creation failed. It doesn't. It says the table was created.
    echo 'dbDelta results: ' . "\n\n";
    if ( $result ) foreach( $result as $m ) echo $m . "\n";
    else echo '(no results from dbDelta)';

    echo "\n -- \n";

    // This should show the table name by checking information_schema, but it's an empty array
    echo 'Table query from INFORMATION_SCHEMA, this should show the table name: ' . "\n\n";
    $table_exists = $wpdb->get_results( "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '". DB_NAME ."' AND TABLE_NAME = '". $table_name ."';" );
    var_dump( $table_exists );
    echo '</pre>';

    exit;
}

example_database_creation_for_wordpress_trac();