Обсуждение: Errors attempting to insert duplicate values

I have a table with a unique index.  When I go to insert a new value into
the table my code does not know if the record is already there.  To me there
are two choices:

1) Do a select for the record and insert only if not found.

2) Do a blind insert and relay on the unique index to fail the insert if
there is a duplicate.

I have chosen to do #2 and it has worked well for a long time.  It seems
better to keep the number of database interactions to a minimum.  The only
problem is everytime a duplicate insertion is attempted an error is printed
in the log file.

The anal-retentive part of my personality is bugged by this.  What do other
people think?  Is the technique legitimate and should the database be
reporting errors for legitimate operations?

Bryan White wrote:
>
> I have a table with a unique index.  When I go to insert a new value into
> the table my code does not know if the record is already there.  To me there
> are two choices:
>
> 1) Do a select for the record and insert only if not found.
>
> 2) Do a blind insert and relay on the unique index to fail the insert if
> there is a duplicate.

3) Use a sequence to generate unique indicies for you?
--
Martijn van Oosterhout <kleptog@cupid.suninternet.com>
http://cupid.suninternet.com/~kleptog/

> Bryan White wrote:
> >
> > I have a table with a unique index.  When I go to insert a new value
into
> > the table my code does not know if the record is already there.  To me
there
> > are two choices:
> >
> > 1) Do a select for the record and insert only if not found.
> >
> > 2) Do a blind insert and relay on the unique index to fail the insert if
> > there is a duplicate.
>
> 3) Use a sequence to generate unique indicies for you?

Sorry but I was not clear.  I need to insert a record only if it does not
exist.  If it does exists then I do nothing.  However I do need to know if
it was inserted.  Its seems most efficient to just attempt the insert and
look at the result.  My only complaint is the log file is littered the error
messages about attempting to insert a duplicate.

Bryan White wrote:
>
> I need to insert a record only if it does not
> exist.  If it does exists then I do nothing.  However I do need to know if
> it was inserted.  Its seems most efficient to just attempt the insert and
> look at the result.  My only complaint is the log file is littered the error
> messages about attempting to insert a duplicate.

How about something like this?

    INSERT INTO foo (id, ...)
        SELECT 14, ... <=== literal insert values
        FROM foo
        WHERE NOT EXISTS (SELECT * FROM foo WHERE id = 14)

It'll insert 1 or 0 records, and it won't try to insert a duplicate...

Regards,
Ed Loehr