In table Case_Supplies_Media I have column SupplyID_XRef. It currently has a foreign key reference to Media_List. I need to change the reference to Supplies_DVD_List. I found some bits and pieces, but I'm not sure how to put the whole thing together in code. I'm using GRDB. Thanks in advance for the help.
func update_Foreign_Key()
{
let the_Version = ModelData.get_Current_Version()
if the_Version == 6
{
do {
try Database_GRDB.shared.databaseConnection!.write { db in
try db.execute(sql: "UPDATE Case_Supplies_Media SET SupplyID_XRef = :value", arguments: ["value": "Supplies_DVD_List"])
// FOREIGN KEY("SupplyID_XRef") REFERENCES "Media_List"
// change to this
// FOREIGN KEY("SupplyID_XRef") REFERENCES "Supplies_DVD_List"
// Set the version to 7
try db.execute(sql: "UPDATE My_Settings SET Version = :version WHERE SettingsID = :id", arguments: ["version": 7, "id": 1])
}
} catch {
print("Updating the Foreign_Key values failed! (AppDelegate) \(error)")
}
}
}
To change the destination table of a foreign key, you need to recreate the database table.
The procedure is precisely documented by SQLite at Making Other Kinds Of Table Schema Changes. It is rather long, so it is not copied here.
All steps can be executed through GRDB via SQL queries.
Your app does not use GRDB migrations (and this is fine). For apps that use GRDB migrations, the procedure is detailed in the Migrations GRDB guide.