How to define composite keys with dbDelta()

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.