Copying 1-to-1 relationships with identity fields in SQL

Suppose you have two tables that are in a one-to-one relationship; i.e. the primary key of the child table is also the foreign key that links it to the parent table. Suppose also that the primary key of the parent is an identity field (a monotonically increasing integer that is assigned by the database when the record is inserted).

Suppose that you need to copy records from these two tables into a second pair of identical tables -- the primary key of the parent is an identity, and t开发者_如何学JAVAhe foreign key linking the child to the parent is also the child's primary key.

How should I copy records from one set of tables to the other set?

I currently have three solutions, but I'd like to know if there are others that are better.

• Option 1: Temporarily disable the identity property in the destination parent table. Copy records from the parent table, then the child table, keeping the same values for the primary key. Cross your fingers that there are no conflicts (value of primary key of source table already exists in destination table).

• Option 2: Temporarily add a column to the destination parent table to hold the "old" (source) primary key. Copy records from the parent table, allowing the database to assign a new primary key but saving the old primary key in the temporary column. Copy records from the child table, joining the source child table to the destination parent table via the the old primary key, using the join to insert the record into the destination child table with the new primary key. Drop the temporary column from the destination parent table.

• Option 3: Copy sequentially record-by-record, first from parent to parent, then child to child, using DB-provided "identity of last inserted record" functions to ensure that the link is maintained.

Of these options, I think option 2 is my preference. Does anyone prefer one of the other two options, and if so, why? Does anyone have a different solution that is "better"?

This is one reason why it is so critical to remember that even if you use a surrogate key (like an identity column), you always need a business key. I.e., there always need to be some other unique constraint on the table. If you had that, then another choice would be to insert the values into the copy of the parent table without the identity values and use that unique key to insert the proper parent value for the child rows.

If you do not have that unique key, then given your situation, I agree that your best solution would likely be Option #2.

Before you decide on an approach to copy data to new set of tables, you should investigate following items:

1. a list of tables that reference the data from the parent and child tables (both sets of tables)
2. Are there any stored procedures/triggers that utilize the data in these tables?
3. How does this table get populated? Is there an application/data feed that inserts data in this table?
4. How does the data in this table get deleted?
5. What is the purpose of the primary key beyond ensuring uniqueness in the table? For this you will have to understand how the data in the table is used by the application.

Based on the answers, you should be able to pick the right solution that will meet the requirements of the application.

My money is on Option 1 (see SET IDENTITY INSERT, http://msdn.microsoft.com/en-us/library/ms188059.aspx).

But: Why are you copying them?

• If you are just altering the table schema, or migrating to new tables and retiring the old ones, why not use ALTER TABLE.
• If you are going to run them side-by-side you probably need the keys to match.

But to answer your question, use Option 1, definitely.