Chapter 16: deleting an acronym with Foreign key

freddyf · May 19, 2018, 4:01pm

Hi!
In chapter 16, when I try to delete an acronym via the web app, I got the following error:

[ ERROR ] PostgreSQLDiagnosticResponse.ri_ReportViolation: ERROR: update or delete on table “acronyms” violates foreign key constraint “acronym_category_acronymID_fkey” on table “acronym_category” (Logger+LogError.swift:17)
[ DEBUG ] Possible causes for PostgreSQLDiagnosticResponse.ri_ReportViolation: update or delete on table “acronyms” violates foreign key constraint “acronym_category_acronymID_fkey” on table “acronym_category” (Logger+LogError.swift:23)

I don’t see why there should be an error: there is a foreign key on the AcronymCategoryPivot both for acronym id and category id, but that should not prevent me for deleting an acronym, since categories and acronyms are siblings.
In the other way, it seems natural to prevent deleting categories that are still used in acronyms, but not prevent deleting acronyms that still have a category.
Do you have the same error? Do you think it’s justified?

Thanks
Frederic

cellane · May 21, 2018, 12:45am

I had the same error or rather, illogical behaviour, so I searched for a bit for ways to solve this issue and I think I came up with one. If I change the AcronymCategoryPivot’s migration to look like this (line 5 is important), I get much more reasonable behaviour:

extension AcronymCategoryPivot: Migration {
    static func prepare(on connection: PostgreSQLConnection) -> Future<Void> {
        return Database.create(self, on: connection) { builder in
            try addProperties(to: builder)
            try builder.addReference(from: \.acronymID, to: \Acronym.id, actions: .update)
            try builder.addReference(from: \.categoryID, to: \Category.id)
        }
    }
}

During a few minutes of testing, it seemed that with this code, I can’t delete any category that has acronyms assigned to it, but I can freely delete acronyms that belong in a category (and the relevant entries in the pivot table would also get deleted).

0xtim · May 21, 2018, 1:39pm

@freddyf there isn’t any support for cascade deletes at the moment. So you can’t delete it since a category pivot is relying on it. You need to delete all the category pivots before you can delete the acronym. Cascade delete would solve that but it’s waiting to be implemented I believe

freddyf · May 21, 2018, 2:23pm

I understand that, technically, a pivot is relying on the acronym I want to delete, but conceptually, if I want to delete an acronym, I don’t want to delete all categories linked to this acronym. That makes no sense.
On the other hand, if I want to delete a category, I don’t want to delete all the acronyms linked to this category either, I just want to remove the category.
So “update” looks like a good solution to me.

cellane · May 22, 2018, 1:17am

Actually, I’m not sure why the action is called .update because if you inspect the table Fluent creates, you can clearly see that CASCADE is the action Fluent picked.

0xtim · July 11, 2018, 11:28am

@freddyf see the latest code for adding support for Cascade deletes in

github.com

raywenderlich/vapor-til/blob/main/Sources/App/Models/AcronymCategoryPivot.swift#L55


      
          /// Copyright (c) 2021 Razeware LLC
          ///
          /// Permission is hereby granted, free of charge, to any person obtaining a copy
          /// of this software and associated documentation files (the "Software"), to deal
          /// in the Software without restriction, including without limitation the rights
          /// to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
          /// copies of the Software, and to permit persons to whom the Software is
          /// furnished to do so, subject to the following conditions:
          ///
          /// The above copyright notice and this permission notice shall be included in
          /// all copies or substantial portions of the Software.
          ///
          /// Notwithstanding the foregoing, you may not use, copy, modify, merge, publish,
          /// distribute, sublicense, create a derivative work, and/or sell copies of the
          /// Software in any work that is designed, intended, or marketed for pedagogical or
          /// instructional purposes related to programming, coding, application development,
          /// or information technology.  Permission for such use, copying, modification,
          /// merger, publication, distribution, sublicensing, creation of derivative works,
          /// or sale is expressly withheld.
          ///
          /// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
          /// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
          /// FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
          /// AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
          /// LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
          /// OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
          /// THE SOFTWARE.
          
          
import Fluent
          import Foundation
          
          
final class AcronymCategoryPivot: Model {
            static let schema = "acronym-category-pivot"
            
            @ID
            var id: UUID?
            
            @Parent(key: "acronymID")
            var acronym: Acronym
            
            @Parent(key: "categoryID")
            var category: Category
            
            init() {}
            
            init(id: UUID? = nil, acronym: Acronym, category: Category) throws {
              self.id = id
              self.$acronym.id = try acronym.requireID()
              self.$category.id = try category.requireID()
            }
          }