I often make or work on tables with a primary key on them using an auto incrementing number i.e. column properties > identity specificalion = yes, identity increment = 1 etc...

This primary key is then used as a forign key in related tables...

The problem or issue is if the database needs to be moved, backed up , downloaded to my developement machine etc... I need to create the tables constraints etc.. on the new sever then try and fill the tables with records from the old server and I will get forign_key_constaint errors because the auto incriment PK in tables on the new server starts from 1 then 2,3,4 etc...

BUT because records have been deleted the old database might have PK's 1,3,4,6 and the related tables have these as the forign key so the forign_key_constaint errors occurs.

Is there an easy way around this - instead of turning off and on the auto increment and checking the keys/data are the same valid etc..?

OR

Is it BETTER PRACTICE to use manual incrementing PK's instead of auto incrementing PK's. Like when inserting a record manually get the last PK number add 1 and use it - i.e. SET newPK = SELECT TOP 1 pk_field FROM table ORDER BY pk_field DESC; SET newPK = newPK + 1 and use it for the insert.?

OR

Any other advice is appreciated... I run into this problem with websites needing to be moved to a better server, re-developed etc...