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

[Earnest Berry III]

I have implemented random_id generation which solves 2 problems:

(Link to explination: http://earnestberry.com/node/13 )

 

1.	Tables are NEVER locked when generating an id
2.	ID, and there fore node (and id) generation is MUCH faster
3.	It is non-sequential, so no issues with the sequencing getting out
of sync.

 

The only draw back is on the DB side.if you're writing stored procedures,
you don't have a sequence or counter table to look at. However, one can
easily solve this by implementing the methodology in a sp (stored-proc).

 

 

  _____  

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

 

I believe one of the reasons is that many times you want to know the ID of
your new record. For example, you inserted a new file to the 'files' table,
and you would like to store a reference to it in your xyz table. 

For mysql there is mysql_insert_id(), but not for others.

On 4/25/07, Syscrusher <syscrusher at 4th.com > wrote:

On Wednesday 25 April 2007 10:29, Ezra B. Gildesgame wrote:
> I happened to discuss this with Greg Knaddison yesterday. 
> MySQL 4 does not support auto-incrementing columns.

I must respectfully disagree. I happened to have a copy of the MySQL
reference book from version 3.22 sitting on my bookshelf, and AUTO_INCREMENT
was supported even then. :-) Never mind the question of why I still have
such 
an old book....

I also checked a PostgreSQL reference book, and they offer a SERIAL
column type that works very similarly to the AUTO_INCREMENT in MySQL. I
don't
know how far back this support goes; my book is for PG version 8.something
and
was copyright 2006.

I can't comment on the Microsoft SQL Server implementation of this, if any,
because I don't have any manual for that here.

Scott

--
----------------------------------------------------------------------------
--- 
Syscrusher (Scott Courtney)          Drupal page:
http://drupal.org/user/9184
syscrusher at 4th dot com            Home page:     http://4th.com/
<http://4th.com/> 

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.drupal.org/pipermail/development/attachments/20070425/86bf8953/attachment.htm