How do you merge tables with autonumber primary keys?

Question

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.

Answer 1

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:

1. Remove all foreign key constraints to table1 and table2
2. For all foreign key fields referring to table1, execute an UPDATE table SET id1 = id1 * 2, and for FK fields referring to table2, execute an UPDATE table SET id2 = (id2) * 2 + 1
3. Fill table3 by executing an INSERT INTO table3 SELECT id1 * 2 AS id3, ... FROM table1 UNION ALL SELECT id2 * 2 + 1 AS id3 FROM table2
4. Create new foreign key constraints to table3

It can even work with 3 or more tables, just by using a higher multiplier.