Re: automatic unique key genration

Ramesh K Meda <74053.36_at_CompuServe.COM> wrote:
>Yes, unique key could be generated for primary key. There are two
>different ways.
>
>1. Use sequence and set value for new.<column_name>.
>2. Use max(PrimaryColumnName) + 1 to set new value.
>
>Depending on the platform and oracle release you might be using,
>you may receive "Mutating" error. Hope, you know how to solve
>this.

Solution 2 above is simply wrong, first because of the mutating problem and secondly because two concurrent transactions may well see the same value of max(PrimaryColumnName) + 1. Any way of cheating to get round there restriction on selecting from mutating tables will make the second effect more likely.

The whole point of sequences is that they are outside the transaction structure, and can deliver guaranteed uniquness without blocking locks. The only cost is that sequence numbers can be 'lost' because NEXTVALs given to transaction which roll back are never given to anyone else. Received on Wed Nov 29 1995 - 00:00:00 CET