mysql - 无法删除或更新父行 : a foreign key constraint in grails with mysql

Question

我有 Subscriber 和 Contact 域，一个订阅者可以在 grails 应用程序中有多个联系人(一对多)。当我尝试从表联系人中删除数据时，它会抛出类似 DBCExceptionReporter Cannot delete or update a parent row: a foreign key constraint fails (vprocure5.subscriber_contact, CONSTRAINT 这样的错误FKC5D3AF49E9F29F5外键 (contact_id) 引用contact(id))。

根据错误消息，我无法删除父行，但实际上我正在尝试删除属于“订阅者”域的子项的联系人数据。如果我没记错的话，订阅者应该是父域，联系人应该是子域。

订阅者域

static hasMany= [contacts: Contact ]

联系域

static belongsTo = [Subscriber ]

ContactController.grooby

package com.vproc.member

import org.springframework.dao.DataIntegrityViolationException

class ContactController {

  def springSecurityService
    def subscriberService
  def imageUploadService
  def searchableService
  def autoCompleteService

  static allowedMethods = [save: "POST", update: "POST", delete: "POST"]


  def index() {
    redirect(action: "list", params: params)
  }

  def list() {

    Subscriber loggedinSubscriber = Subscriber.get( springSecurityService.principal.id )
    List<Contact>contactsList = new ArrayList<Contact>();
    loggedinSubscriber?.contacts.each { it ->
        contactsList.add(it)
    }
    [contactInstanceList:contactsList , contactInstanceTotal: contactsList.size() ]
  }

  def create() {
    [contactInstance: new Contact(params)]
  }

  def save() {

    if (params.birthday){
        params.birthday = (new SimpleDateFormat("MM/dd/yyyy")).parse(params.birthday)
    }
    def contactInstance = new Contact(params)
    Subscriber loggedinSubscriber = Subscriber.get( springSecurityService.principal.id )
    if (loggedinSubscriber == null)
      System.out.println("not able to save")

    else {
      if (!loggedinSubscriber.contacts){
        loggedinSubscriber.contacts = new ArrayList<Contact>();
      }
      loggedinSubscriber.contacts.add(contactInstance)

      if (!loggedinSubscriber.save(flush: true)) {
        flash.message = message(code: 'default.created.message', args: [message(code: 'contact.label', default: 'Contact'), contactInstance.id])
        render(view: "create", model: [contactInstance: contactInstance])
        return
      }
    }

    flash.message = message(code: 'default.created.message', args: [message(code: 'contact.label', default: 'Contact'), contactInstance.id])
    redirect(action: "list")
  }


   def ajaxDelete = {

      def contactInstance = Contact.get( params.id );
        contactInstance.tags.clear();
      println "=========================="
      if(contactInstance) {
        try {
          println "+++++++++++++++++++++++++"
          contactInstance.delete(flush:true)

          render "contact ${params.id} deleted"

        }

        catch(org.springframework.dao.DataIntegrityViolationException e) {
          render "contact ${params.id} could not be deleted"
        }

      }
      else {
        flash.message = "contact not found with id ${params.id}"
        render(action:list)
      }
    }

Contact.groovy

package com.vproc.member

import java.util.Date;

import com.vproc.common.Tag;
import com.vproc.enquiry.ContactType;
import grails.converters.JSON;

class Contact {

    String name
    String phoneNumber
    String emailAddress
    Gender gender
    String url
    String note
    byte[] image
    String address
    Date dateCreated
    Date lastUpdated
    ContactType contactType
    Date birthday

    static belongsTo = [Subscriber ]
    static hasMany = [tags:Tag ,   shares: SharedContact]
    static  constraints = {
        image nullable: true
        phoneNumber nullable: true
        url nullable :true
        address nullable :true
        gender nullable :true
        note nullable :true
        contactType nullable :true
        birthday nullable :true

    }

    static mapping = {
    tags cascade: "all-delete-orphan"
}


     //static searchable = [only: ['name', 'emailAddress']]
      static searchable = true

     static scaffold = true
    //static searchable = true
}

Subscriber.groovy

package com.vproc.member

import java.util.Date;



class Subscriber extends PartyRole{

    transient springSecurityService

    String username
    String password
    boolean enabled
    boolean accountExpired
    boolean accountLocked
    boolean passwordExpired
    StatusEnum status
    Date dateCreated
    Date lastUpdated
    List<Contact> contacts ;

    static belongsTo = [ customer: Customer]
    static hasMany = [scontacts: Contact]



    static mapping = {
        password column: '`password`'
    }

    Set<Role> getAuthorities() {
        SubscriberRole.findAllBySubscriber(this).collect { it.role } as Set
    }

    def beforeInsert() {
        encodePassword()
    }

    def beforeUpdate() {
        if (isDirty('password')) {
            encodePassword()
        }
    }

    protected void encodePassword() {
        password = springSecurityService.encodePassword(password)
    }
}

知道如何在这里删除联系人记录吗？

删除联系人 Controller 中的操作

def ajaxDelete = {
      Subscriber loggedinSubscriber = Subscriber.get( springSecurityService.principal.id )
      def contactInstance = Contact.get( params.id );
      contactInstance.tags.clear();
      loggedinSubscriber.removeFromContacts(contactInstance)
      println "=========================="
      if(contactInstance) {
        try {
          println "+++++++++++++++++++++++++"
          contactInstance.delete(flush:true)
          render "contact ${params.id} deleted"

        }

        catch(org.springframework.dao.DataIntegrityViolationException e) {
          render "contact ${params.id} could not be deleted"
        }

      }
      else {
        flash.message = "contact not found with id ${params.id}"
        render(action:list)
      }
    }

Answer 1

因为你已经映射了这样的关系:

static belongsTo = [Subscriber]

Grails 已经创建了一个名为 subscriber_contact 的连接表。您可以在错误消息中看到这一点:

delete or update a parent row: a foreign key constraint fails (vprocure5.subscriber_contact, CONSTRAINTFKC5D3AF49E9F29F5FOREIGN KEY (contact_id) REFERENCEScontact(id))

在这种情况下，您实际上需要使用 Subscriber 上的 removeFrom 方法来删​​除联系人。

subscriberInstance.removeFromContacts(contactInstance)

然后，GORM 将在内部处理从 subscriber_contact 表中删除该行。

可选地，您应该能够使用 map 符号来映射您的关系:

static belongsTo = [subscriber: Subscriber]

这应该不需要 Grails 创建连接表，您应该能够直接删除联系人，因为它只包含订阅者表的 FK。

我创建了一个 grails 应用程序，订阅者和联系人按照您拥有它们的方式建模。然后我写了下面的测试，它通过了:

@TestFor(Contact)
@Mock([Contact, Subscriber])
class ContactTests {

  void setUp() {
    def subscriber = new Subscriber(name: 's1')
    def contact = new Contact(name: 'c1')
    subscriber.addToContacts(contact)
    subscriber.save()
  }

  void testDeleteContactFromSubscriber() {

    assertEquals(Contact.list().size(), 1)

    def dbSubscriber = Subscriber.findByName('s1')
    def dbContact = Contact.findByName('c1')
    dbSubscriber.removeFromContacts(dbContact)
    dbContact.delete()

    assertEquals(Contact.list().size(), 0)
  }
}