Question 411890 in IOS Swift Question posted in
The official documentation can be found here.

Ios swift – Alter the reference to foreign key of an existing table with GRDB

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)")
            }
        }
    }
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Here's what I came up with. If anybody sees something that needs to be tweaked, please let me know ASAP. This is going into a released app so I need to get it shipped as soon as I can.

     func update_Foreign_Key()
    {
        let the_Version = ModelData.get_Current_Version()
        var theArray = [get_Case_XRef]()
        
        if the_Version == 6
        {
            do {
                try Database_GRDB.shared.databaseConnection!.read { db in
                    theArray = try get_Case_XRef.fetchAll(db, sql: "SELECT * FROM Case_Supplies_Media")
                }
            } catch {
                print("Getting the values from table Case_Supplies_Media failed! (AppDelegate) (error)")
            }
            
            do {
                try Database_GRDB.shared.databaseConnection!.write { db in
                    try db.create(table: "Case_Supplies_Media_x") { t in
                        
                        t.column("SupplyID_XRef", .integer) .primaryKey() .notNull() .references("Supplies_DVD_List")
                        t.column("CaseID_Supply_XRef", .integer) .notNull() .references("My_Cases")
                        t.column("Supply_Count", .integer)
                        t.column("Supply_Type", .text)
                    }
                    
                    // insert into Case_Supplies_Media_x
                    for theKey in theArray
                    {
                        try db.execute(sql: "INSERT INTO Case_Supplies_Media_x (SupplyID_XRef, CaseID_Supply_XRef, Supply_Count, Supply_Type) VALUES (?, ?, ?, ?)",
                                       arguments: [theKey.SupplyID_XRef, theKey.CaseID_Supply_XRef, theKey.Supply_Count, theKey.Supply_Type])
                    }
                    
                    // Drop table Case_Supplies_Media
                    try db.drop(table: "Case_Supplies_Media")
                    
                    // Rename table Case_Supplies_Media_x to Case_Supplies_Media
                    try db.execute(sql: "ALTER TABLE Case_Supplies_Media_x RENAME TO Case_Supplies_Media")
                    
                    // 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)")
            }
        }
    }

  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search