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

[Sammy Spets]

On 25-Apr-07 13:55, Barry Jaspan wrote:
> 
> Sammy Spets <sammys-drupal at synerger.com> writes:
> > PostgreSQL [has] serial and bigserial types. MySQL [has] auto_increment
> > field property. These are used in Drupal to produce a guaranteed
> > unique ID for tables using them.
> > 
> > We use db_next_id() in Drupal to provide a db-independent method of
> > getting the next unique ID into the application for use just after the
> > insert operation on the DB.
> 
> I interpret this to mean, in your opinion at least, that (a) Drupal
> does officially condone and support the use of auto-incrementing
> columns and (b) it also provides db_next_id() for those situations
> where having it is simply more convenient or efficient than letting
> the database do the id generation.

db_next_id() DOES use the DB to generate the next ID in PostgreSQL. I 
don't remember what it does for MySQL (not my area of care anyway). If 
PHP generates the ID for MySQL then start using a proper DBMS!

db_next_id() is a DB independent wrapper for acquiring the next value 
from the DB's auto-increment implementation. It's never more efficient 
because there is a (albeit) small data transfer back to the app and the 
query to the DB. Considering the size of this data transfer and the fact 
there is no other way to implement insertion of a record with the ID as 
primary key (no other unique key in table) followed by records with the 
ID as foreign key, this is the best you'll get.

Auto-increment columns are already used in Drupal core so your a) is 
correct.

> FYI, the Schema module now supports MySQL and PostgeSQL for table and
> index creation from the same schema data structure.  I discovered the
> pgsql's use of the serial type during the implementation which is what
> led to my initiating this thread.  Schema now supports a 'serial'
> column type which maps to auto_increment for MySQL or {,big}serial for
> PostgreSQL.
> 
> I'd be very happy to have you review the pgsql support.  Schema is
> currently in contrib.
> 
> Thanks,
> 
> Barry
> 

-- 
Sammy Spets
Synerger Pty Ltd
http://synerger.com