Continue after primary key violation error

Question

The problem is during inserting data from staging table during import routine.

The system is a legacy one I inherited, and short term while I develop something more suitable I want to patch things to avoid the data transfer failing.

Unfortunately the facility exists via another application to create an entry into table, called CommReceipt. The key is called CR_Key . if this happens then when the auto routine runs to insert, say 1000 rows we need to import from another system (not my system) with CR_Key values already defined, it fails.

The way I see it I have several options, but all suggestions will be appreciated moving forward for the best solution to this issue (both long and short term fixes) .

It is part of the plan to eliminate functionality in the rogue application (but this is a legacy system, written in legacy unfamilar language and might take a bit of effort)

How do I deal with the primary key violation. Can I continue, reporting the violation to deal with after running data insert.

UPDATE: the Primary key CR_Key also happens to be an identity , is there a way to delete rows which should not be there and insert rows using the same ID. I presume....I turn the identity off, then specify unique values in the 'missing rows', is that plausible? I don't need to auto increment id now, theinsert routine has ID's

Thanks

Answer 1

Violation of a PK constraint is a severe error in sqlserver, which according to BOL for sqlserver 2000 is level 14, so not considered fatal, however this is a bit of a trial/error system: the severity level of the error dictates what sqlserver will do with the transaction in progress: if the severity level is high enough it will terminate the transaction at the server level and you can only restart it from the beginning.

Answer 2

You could use an instead of insert trigger. Inside the trigger, do an Insert into the table, where not exists CommReceipt.CR_Key = inserted.CR_Key.

Create trigger T_CommReceiptInsteadOfInsert on CommReceipt
Instead of Insert
As
Begin

--Insert duplicate records into another table
Insert Into CommReceipt_Duplicates(CR_Key, ...)
Select CR_Key, ...
From inserted i
Where exists (select * from CommReceipt c Where c.CR_Key = i.CR_Key)

--Insert non duplicate records
Insert Into CommReceipt(CR_Key, ...)
Select CR_Key, ...
From inserted i
Where not exists (select * from CommReceipt c Where c.CR_Key = i.CR_Key)

End

Answer 3

maybe you could use a trigger, to verify that the insert can be done (the PK don't exist). If the PK already exist, you can store some information in other table as a log, and cancel the insert making a rollback and launching an exception.

Answer 4

Firstly, I'm assuming the PK clashes are accidental, and that the newly-inserted rows are indeed independent entities (as opposed to something that should better have been handled by an UPDATE. Secondly, I'm assuming you can't drop that primary key and handle the "key" clashes with a job running after the batch insert (or use an alternate primary key altogether).

You have a few options open to you if you're using an identity on your primary key column (in which case your legacy app must be bypassing the identity using IDENTITY INSERT for its successful rows):

• You could create a new table with the same schema as CommReceipt, and place an INSTEAD OF trigger on that table, stripping the primary key and inserting into CommReceipt. Then tweak the legacy app to insert into that new table.

• You could adjust the identity seed on the CommReceipt table to some enormous number out of range of the legacy app's rogue keys. This should be treated as a short-term solution.

If you're not using identities, then I'm assuming you're getting your PK values from some other logic that the legacy app doesn't have access to. If that's the case, then you have no other option than to fix the legacy app, or use an independent store of data for its rows.

Answer 5

My suggestion would be to modify the external import routine so that it omits the externally generated CR_key, and just uses the same auto generated key that everybody else uses.

You may have to contort things to get this result. For example, you may have to cause the external input to be done to a staging table, and then use a trigger to insert into the real table, except for the CR_key that you will discard.

There are consequences to doing things this way, and they could be negative, depending on the requirements. In general, there will no connection between the data your main table and the rest of the data over in the legacy system. If somebody attempts an inner join across databases, they are in for a rude shock. I pity them, but not very much.

There could be some information inside the extrnally supplied CR_key. That's poor design of a PK, but it does happen, escpecially in legacy systems.

Good luck.