SQL - SQLDataAdapter Update causing primary key violation

Question

I need to be able to change the primary keys in a table. The problem is, some of the keys will be changing to existing key values. E.g. record1.ID 3=>4 and record2.ID 4=>5. I need to keep these as primary keys as they are set as foreign keys (which cascade up update) Is there a reasonable way to accomplish this, or am I attempting sql heresey?

As for the why, I have data from one set of tables linked by this primary key that are getting inserted/updated into another set of similarly structured tables. The insertion is in parts, as it is part of a deduping process, and if I could simply update all of the tables that are to be inserted with the new primary key, life would be easier.

One solution is to start the indexing on the destination table higher than the incoming tables row count will ever reach (the incoming table gets re=indexed every time), but I'd still like to know if it is possible to do the above, otherwise.

TIA

Answer 1

You are attempting sql heresy. I'm actually pretty open-minded and know a lot of times one must do things that seem crazy. It annoys me when people arrogantly answer with "you should do that differently", when they have know idea what the situation is. However I must tell you that you should do this differently. heh heh.

No, there is no way to do this elegantly with sql\DataAdapter. You could do it through ADO.NET with a series of t-sql commands. You have to, every time, turn on an identity-overwrite mode (set identity_insert theTable on), do your query where all the values on that table are incremented up one, and then turn of autonumber-overwrite mode. But then you would need to increment all the other tables that use this as a foreign key. But wait, it gets worse:

• You would need to all this in a transaction, because you cannot have anything else happening to these tables during this time, and because if there was a failure you would most definitely need to rollback. This could be a good-size chunk of processing; your tables would be locked for a good bit.
• If you have any foreign key constraints between these tables, you would need to turn them off before you do this, and re-implement them afterwards.

Answer 2

If you find yourself starting to think about update primary key values, alarm bells should start ringing. It may seem easier, but I'd class it as more of a hack than a solution. Personally, I'd be having a rethink and try to address the real problem - may seem harder now, but it will be much better to maintain and reduce potential horrible issues down the line.