I suppose everyone runs into this problem once in a while: you have two tables that have autonumber primary keys that need to be merged. There are many good reasons why autonumber primary keys are used in favour of say application-generated keys, but merging with other tables must be one of the biggest drawbacks.
Some problems that arise are overlapping ids and out of sync foreign keys. I would like to hear your approach for tackling this. I always run into problems, so I'm very curious if anybody has some sort of a general solution.
-- EDIT --
In response to the answers suggesting to use guids or other non-numeric keys, there are situations where in advance it just seems a better idea to use autonumber keys (and you regret this later), or you're taking over someone else's project, or you get some legacy database that you have to work with. So I'm really looking for a solution where you have no control over the database design anymore.
In the Navigation Pane, right-click the table to which you want to add the primary key, and click Design View. Tip: If you don't see the Navigation Pane, press F11 to display it. Locate the first available empty row in the table design grid. In the Data Type field, click the drop-down arrow and click AutoNumber.
No, it does not *have to be* autonumber.
Every table can have (but does not have to have) a primary key. The column or columns defined as the primary key ensure uniqueness in the table; no two rows can have the same key. The primary key of one table may also help to identify records in other tables, and be part of the second table's primary key.
Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).
Solutions include:
Use GUIDs as primary keys instead of a simpler identity field. Very likely to avoid overlaps, but GUIDs are harder to use and don't play nicely with clustered indexes.
Make the primary key into a multi-column key, the second column resolving overlapping values by identifying the source of the merged data. Portable, works better with clustered indexes, but developers hate multi-column keys.
Use natural keys instead of pseudokeys.
Allocate new primary key values for one of the merged tables, and cascade these changes to any dependent rows. This changes a merge operation into an ETL operation. This is the only solution you can use for legacy data, if you can't change the database design.
I'm not sure there's a one-size-fits-all solution. Choose one of these based on the situation.
Hm, I'm kind of enthousiastic about the idea that I just put in a comment at AlexKuznetsov's answer, so I'll make a whole answer about it.
Consider the tables to be named table1 and table2, with id1 and id2 as autonumber primary keys. They will be merged to table3 with id3 (a non-autonumber primary key).
Why not:
UPDATE table SET id1 = id1 * 2
, and for FK fields referring to table2, execute an UPDATE table SET id2 = (id2) * 2 + 1
INSERT INTO table3 SELECT id1 * 2 AS id3, ... FROM table1 UNION ALL SELECT id2 * 2 + 1 AS id3 FROM table2
It can even work with 3 or more tables, just by using a higher multiplier.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With