How to create multiple database tables on plugin activation?

Here's another way that worked for me which I found here:

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

$sql = 'CREATE TABLE table1...';
dbDelta ($sql);

$sql = 'CREATE TABLE table2...';
dbDelta ($sql);

$sql = 'CREATE TABLE table3...';
dbDelta ($sql);

Here's how I used it in a plugin:

/**
 * register_activation_hook implementation
 */
if (!function_exists('custom_install')) {
    function custom_install()
    {
        global $wpdb;
        global $custom_db_version;

        $table_name = $wpdb->prefix . 'customers';

        $table_name2 = $wpdb->prefix . 'favorite';

        // sql to create your table
        $sql = "CREATE TABLE " . $table_name . " (
            id int(11) NOT NULL AUTO_INCREMENT,
            name VARCHAR(255) NOT NULL,
            email VARCHAR(255) NOT NULL,
            subject VARCHAR(255) NOT NULL,
            message text,
            amount VARCHAR(255),
            status VARCHAR(10),
            PRIMARY KEY (id)
        );";

        $sql2 = "CREATE TABLE " . $table_name2 . " (
            `color` bigint(12) NOT NULL,
            `size` varchar(20) NOT NULL,
          ) ENGINE=InnoDB DEFAULT CHARSET=latin1";

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

        // save current database version 
        add_option('custom_db_version', $custom_db_version);
    }

    register_activation_hook(__FILE__, 'custom_install');
}

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 ..." );

One way to get it to work would be to do it like this:

global $wpdb;
$wpdb->hide_errors();
// Require upgrade
// Set charset
$collate = '';
if ( $wpdb->has_cap( 'collation' ) ) {
    $collate = $wpdb->get_charset_collate();
}
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
$queries = array();
array_push($queries, "
CREATE TABLE IF NOT EXISTS {$wpdb->prefix}test1 (
       `test_id1` bigint(20) NOT NULL AUTO_INCREMENT,
       `test_key1` char(64) NOT NULL,
       `test_key21` char(64) NULL, PRIMARY KEY  (`test_id1`)
    ) {$collate}");
array_push($queries, "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}test2 (
        `test_id2` bigint(20) NOT NULL AUTO_INCREMENT,
        `test_key2` char(64) NOT NULL,
        `test_key12` char(64) NULL, PRIMARY KEY  (`test_id2`)
    ) {$collate}");
foreach ($queries as $key => $sql) {
    dbDelta( $sql );
}

I agree that this doesn't actually explains why the supported format for dbdelta is not working but this seems to work at the very least.