Why we need the key property in the Upsert class if it is never used?

Question

I recently tried to use an Upsert query in a table with a composite primary key, but when I go to see the key() method it only allows one parameter and not an array, so we only can only have a singular key:

public function key($field) {
  $this->key = $field;

  return $this;
}

I just tried to use one key and the query works fine! How is posible this? Let see in the core files.

This always will work because if you see the code inside the __toString() function the key property is never used:

public function __toString() {
    // Create a sanitized comment string to prepend to the query.
    $comments = $this->connection->makeComment($this->comments);

    // Default fields are always placed first for consistency.
    $insert_fields = array_merge($this->defaultFields, $this->insertFields);

    $query = $comments . 'INSERT INTO {' . $this->table . '} (' . implode(', ', $insert_fields) . ') VALUES ';

    $values = $this->getInsertPlaceholderFragment($this->insertValues, $this->defaultFields);
    $query .= implode(', ', $values);

    // Updating the unique / primary key is not necessary.
    unset($insert_fields[$this->key]);

    $update = [];
    foreach ($insert_fields as $field) {
      $update[] = "$field = VALUES($field)";
    }

    $query .= ' ON DUPLICATE KEY UPDATE ' . implode(', ', $update);

    return $query;
  }

}

Ok, If the key property is never used I will try to made my upsert without it.

$upsert = $this->database->upsert('forcontu_node_votes')
        ->fields(['nid', 'uid', 'vote'])
        ->values([
          'nid' => $nid,
          'uid' => $uid,
          'vote' => $node_vote,
        ]);
dpm($upsert . '');
// Using dpm() because dpq() doesn't works with upsert https://www.drupal.org/node/2847155
$upsert->execute();

The code will throw an exception because always you need to set the key, if you don't set the key property you will get an exception:

protected function preExecute() {
  // Confirm that the user set the unique/primary key of the table.
  if (!$this->key) {
    throw new NoUniqueFieldException('There is no unique field specified.');
  }
...

If you put comments in the lines

  // if (!$this->key) {
  //  throw new NoUniqueFieldException('There is no unique field specified.');
  //}

the code will works fine, and you will see that if you set or not the key property always the query is the same, in my case:

INSERT INTO {forcontu_node_votes} (nid, uid, vote) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2) ON DUPLICATE KEY UPDATE nid = VALUES(nid), uid = VALUES(uid), vote = VALUES(vote)

So, Why we need the key property in the Upsert class if it is never used?

Note: This is just in the mysql and sqlite clases that the key is not used, but in the postgre clases is used (at this moment I don't have postgre to made some test).

Database Schema:

CREATE TABLE `forcontu_node_votes` (
  `nid` int(11) NOT NULL COMMENT 'Node ID',
  `uid` int(11) NOT NULL COMMENT 'User ID',
  `vote` tinyint(4) NOT NULL DEFAULT '0' COMMENT '1 to 5',
  PRIMARY KEY (`nid`,`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Stores Node votes';

forcontu_blocks.install:

/**
 * Implements hook_schema().
 */
function forcontu_blocks_schema() {
  $schema['forcontu_node_votes'] = [
    'description' => 'Stores Node votes',
    'fields' => [
      'nid' => [
        'type' => 'int',
        'not null' => TRUE,
        'description' => 'Node ID',
      ],
      'uid' => [
        'type' => 'int',
        'not null' => TRUE,
        'description' => 'User ID',
      ],
      'vote' => [
        'type' => 'int',
        'not null' => TRUE,
        'default' => 0,
        'size' => 'tiny',
        'description' => '1 to 5',
      ],
    ],
    'primary key' => ['nid', 'uid'],
  ];
  return $schema;
}

Answer 1

You basically said it yourself. The check is there to enforce that Upsert is used in ways that is compatible with all databases, including PostgreSQL, which needs it, as it does not actually support an UPSERT-like operation.

On MySQL, the key doesn't need to be specified as it is only supported on a table with a single unique key anyway:

https://api.drupal.org/api/drupal/core!lib!Drupal!Core!Database!Query!Upsert.php/class/Upsert/8.2.x

This class can only be used with a table with a single unique index. Often, this will be the primary key. On such a table this class works like Insert except the rows will be set to the desired values even if the key existed before.