I would like to normalize an existing database with its data, because there is one (external) key over many tables to refer to an external entity (3rd-party-system). As we now need to have that one as an entity directly in our database, I am asking my, how to do this.
I update the DDL for that database and created an update-script as well (for the DDL).
But what I the best way to migrate the data?
All table, that used the old varchar-key have now a not nullable foreign key to that new entity. How do I initialize this new field. Is this a step after updating the DDL (is this possible for not-null-fields?)? Or has this to be done while migrating the DDL (and how)?
Example:
Table NewEntity
+ Key (autogenerated)
+ SomeInfo
+ OldKeyThatWasJustAVarchar
Table Existing_1
- Key (autogenerated)
- SomeInfo_1
- SomeInfo_n
- OldKeyThatWasJustAVarchar ('logical' Reference)
+ NewForeignKeyToNewTable
Table Existing_2
- Key (autogenerated)
- ExampleFieldAnalogOtherTables
- OldKeyThatWasJustAVarchar ('logical' Reference)
+ NewForeignKeyToNewTable
The contents of all OldKeyThatWasJustAVarchar are unice, so an simple SQL would offer me the needed ID of NewEntity:
SELECT Key FROM NewEntity nw, Existing_n existing
WHERE nw.OldKeyThatWasJustAVarChar = existing.OldKeyThatWasJustAVarChar;
Well first of all making it an entity doesn't require you to change keys. The new table's natural key is OldKeyThatWasJustAVarchar, so you can simply use this as primary key. Then all you'd have to do would be to create foreign keys on the already existing references.
If you want to stick with your ID concept though, then don't forget to have a unique constraint on the natural key (OldKeyThatWasJustAVarchar that is). As to the process: You can simply create the new table and the NewForeignKeyToNewTable columns in the existing tables. Make these columns nullable. Create the foreign keys. Then fill the new table, then fill the NewForeignKeyToNewTable columns. Then make your new columns not-nullable. That's it.