How to create multiple database tables on plugin activation?

Why only the second?

Because dbDelta() supports CREATE TABLE table_name format only. I.e. Exactly “CREATE TABLE” followed by (one space and) the table name.

More specifically, dbDelta() uses this regular expression pattern: CREATE TABLE ([^ ]*) when parsing the queries into an array indexed by the table name; i.e. array( 'table_1' => 'query', 'table_2' => 'query', ... ).

Here’s the relevant code:

$cqueries   = array(); // Creation Queries
...

// Create a tablename index for an array ($cqueries) of queries
foreach ( $queries as $qry ) {
    if ( preg_match( '|CREATE TABLE ([^ ]*)|', $qry, $matches ) ) {
        $cqueries[ trim( $matches[1], '`' ) ] = $qry;
        $for_update[ $matches[1] ]            = 'Created table ' . $matches[1];
    }
    ...
}

So in the case of CREATE TABLE IF NOT EXISTS table_name, the table name is (seen as) IF and not table_name due to the regular expression pattern:

preg_match( '|CREATE TABLE ([^ ]*)|', 'CREATE TABLE IF NOT EXISTS table_name', $matches );
var_dump( $matches );
/* Output:
array(2) {
  [0]=>
  string(15) "CREATE TABLE IF"
  [1]=>
  string(2) "IF"
}
*/

preg_match( '|CREATE TABLE ([^ ]*)|', 'CREATE TABLE table_name', $matches );
var_dump( $matches );
/* Output:
array(2) {
  [0]=>
  string(23) "CREATE TABLE table_name"
  [1]=>
  string(10) "table_name"
}
*/

And in your case, both queries do match that pattern, but since the table names are both (seen as) IF, the first/previous array item ($queries['IF']) is then overridden. Hence only the second table (the one in the final value of $queries['IF']) gets created.

And WooCommerce actually do not have CREATE TABLE IF NOT EXISTS in their code:

https://github.com/woocommerce/woocommerce/blob/c04f7b79f972ee854e5f5d726eb78ac04a726b32/includes/class-wc-install.php#L687

Possible Solution when using the CREATE TABLE IF NOT EXISTS table_name format

Call dbDelta() for each of the queries, as in @FahamShaikh’s answer:

$queries = [ // array of queries
    "CREATE TABLE IF NOT EXISTS " . $wpdb->prefix . "test1 ...",
    "CREATE TABLE IF NOT EXISTS " . $wpdb->prefix . "test2 ...",
];

foreach ( $queries as $sql ) {
    dbDelta( $sql );
}

Or:

dbDelta( "CREATE TABLE IF NOT EXISTS " . $wpdb->prefix . "test1 ..." );

dbDelta( "CREATE TABLE IF NOT EXISTS " . $wpdb->prefix . "test2 ..." );

Related Posts: