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

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

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 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 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.

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

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 Received on Wed Nov 22 2006 - 11:48:02 CST