CODEWITHSUNDEEP
pythondjangodjango-models
Alex Stewart
Alex Stewart

Reputation: 748

IntegrityError at. insert or update on table violates foreign key constraint

I have been doing my head in with this one. I am importing data in django using CSV, but that isn't my problem.

Get this error when inserting data:

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".

Using postgreSQL, not sure if thats the reason.

Here is the relevant models:

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

and

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

This should be working as club is being properly returned in.

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 says the issue occurs at this:

profile.club=thing

Some help with this would save my sanity! Thanks

Upvotes: 1

Views: 6861

Answers (2)

Lucas Miguel
Lucas Miguel

Reputation: 562

When this error happens, what is tends to be related to is that you made a mistake when creating one or more foreign key fields for your model.

First of all make sure your model foreign key fields are pointing to the right table.

For instance, if you have the following model:

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

You might be accidentally using ModelB on both foreign keys, where you should instead be using ModelC:

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

Otherwise, when you try to create a new model instance of model A:

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

Since model_c_id is setting a value for an id for ModelB, this may throw an error because you are looking for a foreign key that exists in ModelC table, and that does not necessarely exists for the ModelB table.

Upvotes: 0

Adri&#225;n
Adri&#225;n

Reputation: 6255

 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.

And model ids declared by django are numeric, so don't use thing = str(club.id).

Also, using the attribute pk is preferable over id because pk will always point to the real primary key name. This means if you ever change the primary key of your models, you won't need to change all the id accesses in your code.

You need to change profile.club=thing to profile.club.add(club.pk) because your club attribute is declared as a models.ManyToManyField(Club) which is a sequence of Club objects, not a single one.

Or... you may change it and use a models.ForeignKey(Club) which is more coherent with the attribute name (club) (do this only in case that your users can only be members of one club at the same time)

Upvotes: 2

Related Questions