插入或更新表时违反外键约束的完整性错误

Question

我一直在为这个问题苦恼。我在用Django导入CSV数据，但这不是我遇到的问题。

在插入数据时出现这个错误：

IntegrityError at /import-user-profile/
insert or update on table "association_userprofile_club" violates foreign key constraint "association_userprofile_club_club_id_fkey"
DETAIL:  Key (club_id)=(0) is not present in table "association_club".

我在使用postgreSQL，不确定这是否是原因。

这是相关的模型：

class UserProfile(models.Model):  
    user = models.OneToOneField(User)  
    membership_number = models.CharField(max_length=30,help_text='Membership number.')
    club = models.ManyToManyField(Club)
    date_joined = models.DateField(blank=True)
    street_address = models.CharField(max_length=100,help_text='Max 100 characters.',blank=True)
    suburb = models.CharField(max_length=40,help_text='Max 40 characters.',blank=True)
    postcode = models.CharField(max_length=10,help_text='Max 6 characters.',blank=True)
    state = models.CharField(max_length=30,help_text='Max 4 characters.',blank=True)
    home_phone = models.CharField(max_length=30,help_text='Max 30 characters.',blank=True)
    mobile_phone = models.CharField(max_length=30,help_text='Max 30 characters.',blank=True)
    ticketed_steward = models.CharField(max_length=1, choices=[('0', 'No'), ('1', 'Yes')],blank=True)
    ticketed_time_keeper = models.CharField(max_length=1, choices=[('0', 'No'), ('1', 'Yes')],blank=True)
    AFA_judge = models.CharField(max_length=1, choices=[('0', 'No'), ('1', 'Yes')],blank=True)
    associate_members = models.ManyToManyField(User,blank=True, related_name='+')

    def __str__(self):  
          return "%s's profile" % self.membership_number

还有

class Club(models.Model):
    club_name = models.CharField(max_length=50,help_text='Name of the club, MAX 50 characters.')
    club_coordinater = models.CharField(max_length=50,blank=True)
    contact_email = models.EmailField(blank=True)
    contact_phone = models.CharField(max_length=30,blank=True)
    contact_fax = models.CharField(max_length=30,blank=True)
    address_location = models.CharField(max_length=50,blank=True)
    address_suburb = models.CharField(max_length=50,blank=True)
    address_postcode = models.CharField(max_length=50,blank=True)
    address_state = models.CharField(max_length=4,help_text='State the club is in, MAX 4 characters.',blank=True)
    incorporated = models.CharField(max_length=3, choices=[('0', 'No'), ('1', 'Yes')],blank=True)
    old_id = models.CharField(max_length=20,blank=True)
    website = models.URLField(blank=True)
    joined = models.DateField(blank=True)

    def __unicode__(self):
        return self.club_name

这应该是可以工作的，因为俱乐部的数据是正确返回的。

def import_user_profile(request):
    if request.user.is_authenticated():
        file = database_loc + "tblHandlers.csv"
        data = ""
        with open(file, 'rb') as f:
            reader = csv.reader(f)
            for row in reader:
                if row[0] != "pkMemberNumber":
                    #0:pkMemberNumber 1:Club 2:fldContactOpen 3:fldLastName 4:fldFirstName 5:fldStreet 6:fldSuburb 7:fldState 8:fldPostcode 9:fldHome 10:fldMobile 11:fldEmail 12:fldJunior 13:fldNewsletter 14:fldNotes 15:dtejoined 16:dteExpiry 17:dateRenewed 18:fldFinMem1 19:fldFinMem2 20:fldDOB 21:fldHanderAge 22:fldStewTicket 23:fldTimeTicket 24:fldSnrTimeTicket
                    user = User.objects.get(username=row[0]+row[4]+row[3])

                    try:
                        club = Club.objects.get(club_name=str(row[1]))
                        club.save()
                    except:
                        club = Club.objects.get(club_name="No Club")
                        club.save()

                    try:
                        profile = UserProfile.objects.get(user=user)
                    except:
                        profile = UserProfile(user=user)

                    #profile.user=user
                    profile.membership_number=row[0]
                    thing = str(club.id)
                    profile.club=thing
                    profile.date_joined=date_re(row[15])
                    profile.street_address=row[5]
                    profile.suburb=row[6]
                    profile.postcode=row[8]
                    profile.state=row[7]
                    profile.home_phone=row[9][0:30]
                    profile.mobile_phone=row[10]
                    profile.ticketed_steward=row[22]
                    profile.ticketed_time_keeper=row[23]
                    profile.AFA_judge="0"
                    profile.save()

                    user_receipts = UserReceipts(user_id=profile.id,recept="",date_paid=date_re(row[17]),membership_valid_to=date_re(row[16]))  
        html = "<html><body>" + data + "</body></html>"
        return HttpResponse(html)

Django说问题出现在这里：

profile.club=thing 

如果能帮我解决这个问题，我会感激不尽！谢谢

Answer 1

当出现这个错误时，通常是因为你在为模型创建一个或多个外键字段时犯了错误。

首先，确保你的模型外键字段指向正确的表。

比如，如果你有以下这个模型：

class ModelA(models.Model):
    ...
    model_b = models.ForeignKey(ModelB, ...)
    model_c = models.ForeignKey(ModelB, ...) # <-- wrong model in foreign key
    ...

你可能不小心在两个外键上都使用了ModelB，但实际上应该使用ModelC：

class ModelA(models.Model):
    ...
    model_b = models.ForeignKey(ModelB, ...)
    model_c = models.ForeignKey(ModelC, ...) # <-- correct model in foreign key
    ...

否则，当你尝试创建一个模型A的新实例时：

>>> ModelA.objects.create(model_b_id=<int>, model_c_id=<int>)

因为model_c_id是为ModelB设置一个id的值，这可能会导致错误，因为你在寻找一个存在于ModelC表中的外键，而这个外键不一定在ModelB表中存在。

Answer 2

 try:
     club = Club.objects.get(club_name=str(row[1]))
     club.save() # <-- You don't need to save. You haven't changed anything on the club.
 except:
     club = Club.objects.get(club_name="No Club")
     club.save() # <-- You don't need to save. You haven't changed anything on the club.

Django中定义的模型ID是数字，所以不要用 thing = str(club.id) 这种方式。

另外，使用 pk 属性比用 id 更好，因为 pk 总是指向真正的主键名称。这意味着如果你以后更改了模型的主键，就不需要在代码中到处修改 id 的使用。

你需要把 profile.club=thing 改成 profile.club.add(club.pk)，因为你的 club 属性是定义为 models.ManyToManyField(Club)，这表示它可以包含多个 Club 对象，而不是只有一个。

或者……你也可以把它改成使用 models.ForeignKey(Club)，这样和属性名（club）更一致（但只有在用户一次只能是一个俱乐部的成员时才这样做）。