The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

I have a stored procedure running as part of a batch process, and have an issue where every once in a great while (maybe 2 or three months for a procedure that runs every day) it will throw an error to "Primary key for table x not unique". But when I attempt to re run it in ISQL to see where the issue is, it DOES NOT throw the error..

Has anyone run into anything like this?

asked 22 Mar, 11:39

Tim%20O%27Connor's gravatar image

Tim O'Connor
36222
accept rate: 0%


The "Primary key for table x is not unique" error is raised when an attempt is made to insert (or update) a row that has a primary key value equal to a value that is already in the table. There are several reasons why this can happen but most of the the time this is caused by an application logic error that is not generating unique primary key values.

For example, if the application generates the primary key value using this algorithm

set @pk = ( select 1+max( pk ) from T );
insert into T( pk, ... ) values( @pk, ... );
commit;

then it can fail when two copies of the application (or two threads/transactions in the app) runs concurrently. The solution that is often suggested is to use DEFAULT AUTOINCREMENT on your PK columns (if column is numeric).

Without more context - e.g. source of your procedure - it is difficult to determine the exact reason why you are seeing the error.

HTH

permanent link

answered 22 Mar, 12:04

Mark%20Culp's gravatar image

Mark Culp
23.2k9132273
accept rate: 40%

edited 24 Mar, 09:59

> Has anyone run into anything like this?

Yes, many many times.

In addition to what Mark said: please show us the PRIMARY KEY part of the CREATE TABLE for the table in question.

If the primary key is based on a timestamp, it is possible that two successively-generated timestamp values may be identical on rare occasion, making the bug difficult to reproduce.

If the primary key column is declared as DEFAULT CURRENT TIMESTAMP, the default_timestamp_increment option is not helpful since it applies to DEFAULT TIMESTAMP columns which you don't want to use as primary keys... in other words, avoid timestamps as primary keys... you can still have an index on the timestamp column, just add a DEFAULT AUTOINCREMENT integer as the primary key.

permanent link

answered 24 Mar, 09:56

Breck%20Carter's gravatar image

Breck Carter
26.5k433604876
accept rate: 21%

edited 24 Mar, 09:57

I think you're onto it. The primary key is as follows:

PRIMARY KEY ( "act_nbr" ASC, "machine_id" ASC, "eff_dtm" ASC )

The field eff_dtm defaults to current time stamp, so this would explain why when I run the procedure right away after getting the error, it finishes without throwing the error again.

Thanks!

(24 Mar, 14:00) Tim O'Connor
Replies hidden
1

So the "solution" might be to "slow down" the procedure with a WAITFOR DELAY '00:00:00.001'; or the like :)

(27 Mar, 04:36) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×106
×39

question asked: 22 Mar, 11:39

question was seen: 379 times

last updated: 27 Mar, 04:37