CODEWITHSUNDEEP
sqlsql-servervb.netstored-procedures
Guilherme Natal de Mello
Guilherme Natal de Mello

Reputation: 427

SQL composite primary key duplicate

[vb.net, sql server 2008 r2, procedure]

I am having problems with my composite primary key. I have a table:

CREATE TABLE example (
  column1 NUMERIC,
  column2 NUMERIC,
  PRIMARY KEY (column1, column2)
);

1-1
1-2
1-2
2-1
2-2
2-3
3-1
...

But sometimes the user got the error... "Cannot insert duplicate key".

Trying to find a reason for it... I am doing the insert in the following way:

In my vb.net application, first I am selecting the last number of column1. And after it, I add +1.

Example:

Dim column2 As Integer = 206

// Here I get the last number of column1, and then I add + 1
Dim column1 As Integer = DALExample.GetLastNumber(column2)

// Here I insert the next row
DALExample.Insert(column1 + 1, column2)

I have thousands and thousands of users, maybe 2 more users are doing it at the same time and then they have got the error.

What I am thinking to do to solve this problem:

I will not select the last value of column1 in my web application. I will do it directly in my procedure. Example:

INSERT INTO example
    (column1, 
     column2)
SELECT 
    ISNULL(MAX(column1), 0) + 1 FROM example WHERE column2 = 206, 
    206

In this way, there is some possibility of doubling? (I was looking for auto-increment, but it is impossible in composite primary key) Is there any better way to do it?

Thanks!

Upvotes: 1

Views: 2046

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95082

You can still get the duplicate key error with the Statement given. When two users want to insert at the same time, they execute the insert's select query at the same time and get the same value back.

Here are some ways to deal with this:

  • Lock the table. Select the max id. Insert data. Unlock the table.
  • Insert. If this fails, try again (and again and again till it works).
  • Does column1 have a meaning? If so, how can two users want to insert a record with the same meaning? If not, why not use a sequence to get a value?

Upvotes: 1

Related Questions