Re: Auto Increment Primary key?-Q2

A copy of this was sent to John Shaft <shaft_at_meanmutha.com> (if that email address didn't require changing) On Fri, 17 Dec 1999 21:04:14 -0600, you wrote:

>In response to my question about how to increment a Primary key with each
>new INSERT so that I get an automatic unique value:
>
>On Fri, 17 Dec 1999, Steve McDaniels wrote:
>
>> 1. create a sequence
>> 2. create table
>> 3. create trigger for insert on this table
>> which uses the sequence's nextval to
>> populate your primary key field.
>
>
>
>If I'm looking for a way to do this that is more portable. Sequences
>are not real portable, right? (to other RDBMS)
>
>Would it be possible to achieve my end like this:
>
>Creating another table to hold the latest primary key value
>Use a Transaction to select the current value and then insert the new data
>with the incremented value (and update the key value table).
>Finally, commit this as one atomic operation.
>
>Does that sound reasonable?
>

not 100% -- you would need to

• UPDATE the primary key of that table set it = itself+1
• then SELECT it out
• then USE it.

You must update the row to ensure that you lock the row to ensure that no one else can update that row.

You must strictly serialize operations against this. Performance with >1 user is non-existent (one at a time, zero concurrency)

I would suggest that since virtually every database has a builtin, fast way to do this and can be hidden from the application, you would use it.

for example

• in Informix use serial type
• in MSSQL/Sybase use identity
• in Oracle use: create sequence my_seq; create trigger my_trigger on t before insert for each row begin select my_seq.nextval into :new.primary_key from dual; end;
• in MySql use auto_increment

None of them are visible to the application -- they all are the best way to do it in each.

>Any thoughts? I am after portability over pure efficiency. I am used to
>dealing with MySQL, which is not as full-featured as Oracle (but is fast
>as hell and reall cheap). MySQL has an AUTO_INCREMENT feature that can be
>assigned to a column. It probably has this because it lacks transactions
>and sequences.
>
>Thanks,
>
>shaft_at_meanmutha.com
>http://www.meanmutha.com
>
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Dec 18 1999 - 08:23:57 CST