Violation of PRIMARY KEY constaint...

[Guest]

Dear Community,

I'm using ADO.net to update a SQL Server 2005 table. The table has a an
aggregate primary key made of two integers. I use the typcial
SqlDataSet/SqlDataAdapter tools to perform updates and inserts, after an
initial call to da.FillSchema(). My select command is 'select * from t'
(where t is the table). My update and insert commands are both generated by
SqlCommandBuilder.

Before attempting any updates, I call t.Rows.Contains(<key>). Depending on
the result of the above, I either (1) use t.Rows.Find(<key>) and then set
some columns that are not part of the primary key or (2) call t.NewRow() set
the fields and then t.Rows.Add(). After a series of insert/update actions I
call da.Update(t).

My application works, as do most applications I have written in the past
using this simple model. However, I occasionally (~1/100,000) catch an
"Violation of PRIMARY KEY" exception when calling da.Update(t).

This baffles me since (1) no other programs or human users access the table,
(2) the updates are all performed in the same thread, (3) the call to
t.Rows.Add() never fails.

Has anyone experienced a similar issue and found the explanation?

 

[Cowboy \(Gregory A. Beamer\)]

First, I really loath usign the CommandBuilder in a production app. It works
in most cases, but there are some issues.

Second, aggregate proimary keys are problematic, even when you have full
control over them. If any code is going to bomb, this is it.

Third, concurrency checking and key violation checking are good things to
add to any application that uses the standard MS FUD, as it is quite easy to
step out and beomce a fringe case, esp. as your apps grow in complexity and
functionality (I know this first hand).

I have not seen your particular error, but I generallly abstract out my
connection to the database with a stored procedure layer. While the sprocs
versus embedded SQL argument is still not over, I find that the abstraction
layer makes it easier to loosely couple apps that work in conjunction with
each other and make it easier to have schema changes without impacting
current applications.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*********************************************
Think outside the box!
*********************************************

 

[RobinS]

I don't agree with this statement:

It works in most cases, but there are some issues.

I would state it this way:

It works in a few cases, and there are lots of issues.

;-)

Robin S.
-------------------------------

 

[Guest]

Thank you all for your input. Based on the assumption that SqlCommandBuilder
is probably the culprit, I went a head an implement my own SP which handles
both insert and update. It does a query on the two integers that make up the
primary key and performs either an insert or an update accordingly:

if (select count(*) from t where key1 = @key1 and key2 = @key2) > 0
<update>
else
<insert>

All I achieved is to slow down the application. The Primary Key constraint
violation still happens.

If aggregate primary keys are problematic, what's an alternative approach to
solving this kind of problem, aside from not using ADO.net?

 

[RobinS]

Most people use an identity column for their primary key.

What's killing you here is the SELECT COUNT. Your dataset tracks whether a
record is supposed to be inserted or updated. Do you think somebody is
going to come in and add the same record that the current user has added?
Is that really likely?

If you can rely on the dataset, you can set up an InsertCommand,
UpdateCommand, and DeleteCommand object for the data adapter, and the data
adapter will run the Insert command for Adds, and the Update command for
Changes and the DeleteCommand for deletes. You just call the Update method
of the TableAdapter once. It goes through the rows and processes them
accordingly.

Robin S.

 

[Jesse Houwing]

* Ken Abe wrote, On 28-3-2007 3:58:

Dear Community,

I'm using ADO.net to update a SQL Server 2005 table. The table has a an
aggregate primary key made of two integers. I use the typcial
SqlDataSet/SqlDataAdapter tools to perform updates and inserts, after an
initial call to da.FillSchema(). My select command is 'select * from t'
(where t is the table). My update and insert commands are both generated by
SqlCommandBuilder.

Before attempting any updates, I call t.Rows.Contains(<key>). Depending on
the result of the above, I either (1) use t.Rows.Find(<key>) and then set
some columns that are not part of the primary key or (2) call t.NewRow() set
the fields and then t.Rows.Add(). After a series of insert/update actions I
call da.Update(t).

My application works, as do most applications I have written in the past
using this simple model. However, I occasionally (~1/100,000) catch an
"Violation of PRIMARY KEY" exception when calling da.Update(t).

This baffles me since (1) no other programs or human users access the table,
(2) the updates are all performed in the same thread, (3) the call to
t.Rows.Add() never fails.

Has anyone experienced a similar issue and found the explanation?

Is either one of these fields auto numbering? If so, try setting the
seed and step to -1 in the dataset before adding a new record. Under
special circumstances you can get in trouble if the generated number in
the dataset conflicts with the value returned from the database.

Another option is that two threads add the same combination
concurrently. The onl
y way to prevent that is to select, add the record and update it back to
the database from the same transaction. This will be a pretty expensive
one at that.

Jesse