Re: Constraint violation with INSERT INTO ... SELECT ...

G Quesnel wrote:
> The typical way of seeding a sequential id is by creating a 'SEQUENCE'.
> Anything else can lead to concurency issues and performance
> degratdation.

I agree. This is the way I would have done it, if I were to write the application
from scratch. Unfortunately, when the first version of the application was
written about 10 years ago, the programmers didn't think of this, and now
we do not have the option of changing the database layout.

> In the Oracle world, as long as a session's data is not commited, then
> it can not be viewed by other sessions/users. This means that if a
> session/user issues the insert statement you provided and then goes for
> lunch before issuing the commit statement, then any session issuing the
> same statement prior to the commit being completed will not be aware
> that someone is in the process of updating the data.

The "going to lunch" problem will not be a big problem for us, since our
application always commits changes immediately after statements are executed.

> The alternatives
> are worst...
> - if you allow other users to see uncommited data, your books will
> never balance
> - if you locked the table until the data is commited then everybody has
> to wait until the user comes back from lunch

I wonder how MSSQL does it then? It works perfectly there.

> Also, your use of max on current value may works well in Dev, but will
> introduce unneccessary delays and resource consumptions in your larger
> production environment.

Again I agree, but I can't do anything about it.

> You might also want to lookup TRIGGERs as a convenient way to populate
> your column.

I'll have to look into this. I'm afraid that I will have big problems trying to
convince the lead programmer to let me use triggers, though. :-(

> If you do a search in this newgroup using "sequence id value", you will
> get plenty of arguments and examples on how and why you should use a
> SEQUENCE to solve your problem.
>
> hth

My general problem is that I have very limited control over the database layout.
I was hoping there would be a setting in Oracle that would make my SQL command behave the way it does in MSSQL.

Anyway, thank you for your answers,

Regards,
Asger Received on Thu Nov 23 2006 - 04:36:45 CST