Agreed, your model is probably wrong.
The formal primary key should always be a surrogate key. Never anything else. [Strong words. Been database designer since the 1980’s. Important lessoned learned is this: everything is changeable, even when the users swear on their mothers’ graves that the value cannot be changed is is truly a natural key that can be taken as primary. It isn’t primary. Only surrogates can be primary.]
You’re doing open-heart surgery. Don’t mess with schema migration. You’re replacing the schema.
-
Unload your data into JSON files. Use Django’s own internal django-admin.py tools for this. You should create one unload file for each that will be changing and each table that depends on a key which is being created. Separate files make this slightly easier to do.
-
Drop the tables which you are going to change from the old schema.
Tables which depend on these tables will have their FK’s changed; you can either
update the rows in place or — it might be simpler — to delete and reinsert
these rows, also. -
Create the new schema. This will only create the tables which are changing.
-
Write scripts to read and reload the data with the new keys. These are short and very similar. Each script will use
json.load()to read objects from the source file; you will then create your schema objects from the JSON tuple-line objects that were built for you. You can then insert them into the database.You have two cases.
-
Tables with PK’s change changed will be inserted and will get new PK’s. These must be “cascaded” to other tables to assure that the other table’s FK’s get changed also.
-
Tables with FK’s that change will have to locate the row in the foreign table and update their FK reference.
-
Alternative.
-
Rename all your old tables.
-
Create the entire new schema.
-
Write SQL to migrate all the data from old schema to new schema. This will have to cleverly reassign keys as it goes.
-
Drop the renamed old tables.