How to define composite keys with dbDelta()

I am getting error while trying to create a table with dbDelta() and composite primary key. The sql is pretty straight forward.

$sql = "CREATE TABLE {$wpdb->prefix}voicemail_call (

    user_id BIGINT(9) UNSIGNED NOT NULL,
    call_id BIGINT(9) UNSIGNED NOT NULL,
    opened BOOL DEFAULT 0 NOT NULL,

    PRIMARY KEY  (user_id, call_id)
    );";

dbDelta($sql);

This shows error

WordPress database error: [Multiple primary key defined]
ALTER TABLE wp_voicemail_call ADD PRIMARY KEY (user_id, call_id)

Am I doing it wrong? How to correctly defined composite primary key with dbDelta?

Note: Although error is shown but it still creates the table where both columns are set as primary key.

The problem

If the table already exists your code will still try to execute the following queries:

1) ALTER TABLE wp_voicemail_call CHANGE COLUMN user_id user_id BIGINT(9) UNSIGNED NOT NULL
2) ALTER TABLE wp_voicemail_call CHANGE COLUMN call_id call_id BIGINT(9) UNSIGNED NOT NULL
3) ALTER TABLE wp_voicemail_call CHANGE COLUMN opened opened BOOL DEFAULT 0 NOT NULL
4) ALTER TABLE wp_voicemail_call ADD 
5) ALTER TABLE wp_voicemail_call ADD PRIMARY KEY (user_id, call_id)

Note that this query:

ALTER TABLE wp_voicemail_call ADD PRIMARY KEY (user_id, call_id)

is trying to add another primary key that’s already defined and we can only have of one of those. Thus the error.

This query:

ALTER TABLE wp_voicemail_call ADD

comes from the empty line above the PRIMARY KEY line.

In the dbDelta() function there’s this part that should unset the primary parts:

foreach ( $index_strings as $index_string ) {
    if ( ! ( ( $aindex = array_search( $index_string, $indices ) ) === false ) ) {
        unset( $indices[ $aindex ] );
        break
     }
}

but the array search always return false in your case for some reason.

I dug deeper and in your case the $indices array is:

Array
(
    [0] => 
    [1] => PRIMARY KEY  (user_id, call_id)
)

but the $index_strings array is

Array
(
    [0] => PRIMARY KEY  (user_id,call_id)
    [1] => PRIMARY KEY  (user_id,call_id)
)

So we can see the mismatch:

PRIMARY KEY  (user_id,call_id) 

versus

PRIMARY KEY  (user_id, call_id)

… a single space!!

The suggested solution

So if we remove the extra empty line and the extra space:

$sql = "CREATE TABLE {$wpdb->prefix}voicemail_call (
    user_id BIGINT(9) UNSIGNED NOT NULL,
    call_id BIGINT(9) UNSIGNED NOT NULL,
    opened BOOL DEFAULT 0 NOT NULL,
    PRIMARY KEY  (user_id,call_id)
);";

then we should only get these queries to run with dbDelta():

1) ALTER TABLE wp_voicemail_call CHANGE COLUMN user_id user_id BIGINT(9) UNSIGNED NOT NULL
2) ALTER TABLE wp_voicemail_call CHANGE COLUMN call_id call_id BIGINT(9) UNSIGNED NOT NULL
3) ALTER TABLE wp_voicemail_call CHANGE COLUMN opened opened BOOL DEFAULT 0 NOT NULL

when the table already exists.