[development] Why do we avoid auto-incrementing db columns?

[Earnest Berry III]

Hmm...I'm still more of a fan of controlling the IDs application level 
Than using a db generated serial number, especially when dealing with
referential integrity.

I do believe in db generated id's for non-application/non-information
columns. E.g. the watchdog table; information is just inserted into the
database, and then read later, so the id field isn't really used can be
blind to the application. However, say an NID, of the txid (from e-commerce)
gets passed around drupal and used by other modules, sometimes in loops, and
sometimes for other modules to ref. the node; this I'd rather have some
control of.

Also, the implementations of "next_val" per-database get a little hairy
also. You also then have the issue of if you are in a transaction, the
transaction level, etc. that all starts to play in to the db's
implementation of "next_val" or "last_insert_id".


-----Original Message-----
From: development-bounces at drupal.org [mailto:development-bounces at drupal.org]
On Behalf Of Mark Fredrickson
Sent: Wednesday, April 25, 2007 11:31 AM
To: development at drupal.org
Subject: Re: [development] Why do we avoid auto-incrementing db columns?

I'd like to point out this is a really discussion about mysql, not
about other database. Postgresql Drupal installations haven't used a
sequences table ever, IIRC. As long as each database can implement
some equivalent of db_next_id() -- be it by a sequences table, auto
incremented values, Postgres's CURVAL() function, whatever -- Drupal's
db abstraction will work.

So let's rephrase: do the currently supported versions of mysql all
support some sort of db_next_id() functionality? What about Drupal 6?

If so, the sequences table can be dropped in favor of auto_increment.

-M