Auto-Incrementing Fields

• Each new title in our database must receive a unique title_id.
• Let the database itself manage the potential race condition.
• Fields managed in this way are auto-incrementing.
• Different databases handle them differently:
  • MySQL:
    • Insert a special value (such as 0) or omit the value for the auto-incrementing column.
    • The database will automatically assign the next value.
    • This value is retrieved via a special function.
    • Example:

      $sth = $dbh->do(qq{INSERT INTO table (B, C, D) VALUES ("1", 2", "3")});
      $newid = $sth->{'insertid'};

  • Oracle:
    • Before doing the INSERT, call a special function or do a SELECT that returns a unique "next value".
    • INSERT that value into the row.
    • The sequence to which next_val belongs was created as part of the database instance by the table creation script:
      • Oracle version
      • Informix version
      • Mysql version
    • Example:

      $sth = $dbh->prepare(qq{SELECT sequence.next_val FROM dummy});
      $sth->execute;
      $newid = get_the_value_from_the_SELECT;
      $sth = $dbh->do(qq{INSERT INTO table (A, B, C, D)
      VALUES ("$newid", "1", 2", "3")});

Review

Is it better to generate our own ID numbers or to let the database do it?