Thread: 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