DDLUtils and autoincrement values

Question

When trying to use DDLUtils it always seems to take over the id values for the columns that are set to autoincrement. How can I prevent this?

For example, I have a Dogs table with a column called ownerID. The column ownerID is set to autoincrement. However my list of owners is not continuous, there are gaps (for example, ownerID's 2, 4, 5, 6, 7, 10 exist but not 1, 3, 8, 9 because they've since been deleted). The problem is that on DdlToDatabase restoring, the owner ids are reset to 1, 2, 3, 4, and so on. This means that the links in my Dogs table through ownerID are now all incorrect.

How can I get the DDlUtils to correctly import the values for autoincrementing fields?

Answer 1

I don't know much about DDLUtils, but for this sort of ETL (export, transform, load) operation, you have to do a bit more work. I know two ways that are common

• Insert the new Dog record. Keep a map of the old ID to the new ID. When inserting into another table that has an FK to ownerID, use the map to choose the new ID.

    Original Dogs           New Dogs  
    [ID]  [Name]            [ID]  [Name]
    2     Henry              1    Henry
    5     Oliver             2    Oliver

When you insert both records, you end up with an old to new map of [ 2 => 1, 5 => 2 ]. Then when you insert any record that uses an ID of 2, you instead use 1.

• Another way is to disable the auto increment nature of the table when loading your data. This is DB specific.

For instance, in SQL Server, you can use set identity_insert Dogs on

In MySQL, you can simple insert records and specify the ID you want.

Answer 2

It looks like you can't import the values from the auto-increment rows, which means if they are referenced in other tables, you're in trouble. Instead what you should do is use UUID's if you wish to go this route.