Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Constraint violation with INSERT INTO ... SELECT ...

Constraint violation with INSERT INTO ... SELECT ...

From: <asger.grunnet_at_gmail.com>
Date: 22 Nov 2006 08:02:39 -0800
Message-ID: <1164211359.358055.270820@h48g2000cwc.googlegroups.com>


Hi,

I'm developing an application that stores data in a database. We have many customers for this application, and each customer has its own database server - some uses Oracle, some has MSSQL and some has other types of databases.

A typical customer has between 5 and 50 persons using our application at the same time, so we have to take care when different persons access the same database tables simultaneously.

We often use a number-field with a unique index to identify a row in a table.
When we insert something into the table, we do it like this:

INSERT INTO testtable (uniquenumber, field1, field2, ...)   SELECT MAX(uniquenumber) + 1, value1, value2, ...   FROM testtable

This works fine for MSSQL, but it sometimes fails for Oracle with the message: "ORA-00001: unique constraint (<index-name>) violated", because two users have inserted something at the same time.

Is there some way to make Oracle perform such a command as an atomic action, the way MSSQL apparently does?

Regards,
Asger Received on Wed Nov 22 2006 - 10:02:39 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US