CODEWITHSUNDEEP
c#sqlsql-serverc#-4.0exception
Wasif Mushtaq Kiani
Wasif Mushtaq Kiani

Reputation: 11

Primary Key Duplication Exception

I am working on a project in C# using sql server as a data base. So the problem is there is a algo in my project which returns a single value every time; which is saved to the database (as my project requirement). If the algo repeats a value, that will also save to the database which is not required (duplication) and cause some problems. I need help to overcome the problem that a unique value saves only once when it occurs; no repetition in database. I tried to make that column a primary key, but then I found primary key violation exception.

Upvotes: 0

Views: 2193

Answers (1)

Charleh
Charleh

Reputation: 14002

Just use an EXISTS query either in-line or in a stored procedure

Your query procedure can check if the row already exists

Edit: Aha sorry forgot the NOT, that would be stupid :D

IF NOT EXISTS(SELECT UniqueValue FROM UniqueValuesTable WHERE UniqueValue = @NewValue)
  INSERT INTO UniqueValuesTable VALUES (@NewValue)

Edit: here is a SQL fiddle to show it working

http://sqlfiddle.com/#!3/b87f9/3

As dems pointed out, the operation isn't atomic, so in a multi session situation there could be a PK violation still

Alternative is:

INSERT INTO UniqueValuesTable SELECT @NewValue WHERE NOT EXISTS (SELECT UniqueValue FROM UniqueValuesTable WHERE UniqueValue = @NewValue)

Upvotes: 4

Related Questions