Adding an onDelete behavior to an existing reference foreign key constraint

📚 Official Books Server Side Swift with Vapor
1

I am trying to delete a user that has already created a couple of acronyms and I receive the following error:

[ ERROR ] PostgreSQLError.server.error.ri_ReportViolation: update or delete on table "User" violates foreign key constraint "fk:Acronym.userID+User.id" on table "Acronym" (/Users/michael/Downloads/Server_Side_Swift_with_Vapor_v1/26-deploying-with-heroku/final/TILApp/.build/checkouts/vapor.git-4466155092359105859/Sources/Vapor/Logging/Logger+LogError.swift:17)
[ DEBUG ] Possible causes for PostgreSQLError.server.error.ri_ReportViolation: Key (id)=(f61645ff-84ba-4106-b0a6-99bdf8e33912) is still referenced from table "Acronym". (/Users/michael/Downloads/Server_Side_Swift_with_Vapor_v1/26-deploying-with-heroku/final/TILApp/.build/checkouts/vapor.git-4466155092359105859/Sources/Vapor/Logging/Logger+LogError.swift:23)

The Acronym class has the following migration:

extension Acronym: Migration {
  static func prepare(on connection: PostgreSQLConnection) -> Future<Void> {
    return Database.create(self, on: connection) { builder in
      try addProperties(to: builder)
      builder.reference(from: \.userID, to: \User.id)
    }
  }
}

I would like to create a migration to modify the existing foreign key reference to cascade on delete. I attempted the code like so:

import FluentPostgreSQL
import Vapor

struct AddCascadingDeleteToAcronym: Migration {
  
  typealias Database = PostgreSQLDatabase
  
  static func prepare(on connection: PostgreSQLConnection) -> Future<Void> {
    return Database.update(Acronym.self, on: connection) { builder in
      builder.reference(from: \.userID, to: \User.id, onDelete: .cascade)
    }
  }
  
  static func revert(on connection: PostgreSQLConnection) -> Future<Void> {
    return Database.update(Acronym.self, on: connection) { builder in
      builder.reference(from: \.userID, to: \User.id)
    }
  }
}

But when I run the application, I receive the following error:

Thread 1: Fatal error: Error raised at top level: ⚠️ PostgreSQL Error: constraint "fk:Acronym.userID+User.id" for relation "Acronym" already exists
- id: PostgreSQLError.server.error.ATExecAddConstraint

I have data in the PostgreSQL database that I would not wish to purge. What is the proper way to modify an existing constraint in the migration?

1 Like
2

@0xtim Can you please help with this when you get a chance? Thank you - much appreciated! :]

3

It’s fine. I got it figured out.

There isn’t a method for modifying references. I just need to delete the existing one and create a new one like so:

import FluentPostgreSQL
import Vapor

struct AddCascadingDeleteToAcronym: Migration {
  
  typealias Database = PostgreSQLDatabase
  
  static func prepare(on connection: PostgreSQLConnection) -> Future<Void> {
    return Database.update(Acronym.self, on: connection) { builder in
      builder.deleteReference(from: \.userID, to: \User.id)
      builder.reference(from: \.userID, to: \User.id, onDelete: .cascade)
    }
  }
  
  static func revert(on connection: PostgreSQLConnection) -> Future<Void> {
    return Database.update(Acronym.self, on: connection) { builder in
      builder.deleteReference(from: \.userID, to: \User.id)
      builder.reference(from: \.userID, to: \User.id)
    }
  }
}
1 Like
4

@michaeltansg glad you got it sorted!