Dealing with transfer of auto increment columns?

(I'm new to this newsgroup, if this isn't the right place, please point
me to the right one :) )

I'm having a problem which basically comes down to "how to keep relational integrity when transferring tables with references to a new database".

The context for this is a tool I'm writing to be able to transfer a complete database (in this case MS SQL Server) to a XML file, this is the simple half of the tool. The other half of the tool (after distributing this XML file on a cd or by email) should be able to reconstruct this database in the same or another RDBMS (target at this moment is Oracle).
The problem primarily lies in the fact that the source database uses a lot of auto increment columns to make ID's and those ID's are referenced in other tables.
An extension of this problem is the update of an existing database in the same way (using a XML file), but then problem isn't directly the auto increment, but the possible overlap in ID's.  

Let me explain this with an example:

db 1

table A

a.id	a.name
1	chrysler
2	cadillac
3	chevrolet
5	buick
6	toyota
7	mercedes
9	beamer
10	trabant

note: id's 4 and 8 have been removed

table B

b.id	b.refID	b.color
1	1	grey
2	2	black
3	3	red
5	5	blue
6	6	green
7	7	black
9	9	silver
10	10	grey

note: id's 4 and 8 have been removed

now say a.id and b.id are both auto incrementing integers.

when we want to transfer both tables to a fresh db 2, a problem arises, because most db's will simply ignore the original a.id and b.id and instead use their own auto incrementing numbers. The result will be:

db 2

table A

a.id	a.name
1	chrysler
2	cadillac
3	chevrolet
4	buick
5	toyota
6	mercedes
7	beamer
8	trabant

table B

b.id	b.refID	b.color
1	1	grey
2	2	black
3	3	red
4	5	blue
5	6	green
6	7	black
7	9	silver
8	10	grey

note that b.refID is the only column with id's to remain intact.

What to do about this?

proposition:

store information about the relations:
dependency for table B: "b.refID depends on a.id"

when inserting the rows of table A keep track of the new id's:
(by requesting the new id)

changetable
old => new

1 => 1
2 => 2
3 => 3
5 => 4
6 => 5
7 => 6
9 => 7
10=> 8

and when inserting the rows of table B constantly check the changetable:
(very pseudo code with a hint of Java :)

if (there's a dependendy for table B) {

	dependency = dependencies.getDependencyForTable(B)
	field_to_check = dependency.getFieldToCheck()
	changetable    = dependency.getChangeTable()
}
	while (need to insert more rows) {
		sql = "insert into table b values("
		while (more fields in this row)
			if (field.name.equals(field_to_check)) {
				field.value = changetable.get(field.value);
			}
			sql += "'" + field.value + "'"
		}
		executeQuery(sql)
	}

further problems:

• a table could have MORE dependencies (references to more than one auto increment column)
• target db has already been filled for a part, so source and destination id's overlap (same problem, different point of view probably)

Hope someone can shed his/her light on this... point me in the right direction / to some good sites (couldn't find much) or even books.

After I've solved this problem, the next one comes: how to keep track of changes in the source databases and create "diff" files for a specific target database... <sigh> :-)

Ewald Börger,
student Applied Computer Science, Saxion Hogeschool Enschede, The Netherlands.

PS: this tool is *not* a school assignment :) Received on Wed Sep 19 2001 - 14:27:29 CEST