IntegrityError on loadcampaignbrowserexpenditures

[aboutaaron]

Error:

IntegrityError: (1452, 'Cannot add or update a child row: a foreign key constraint fails (calaccess_2015.calaccess_campaign_browser_expenditure, CONSTRAINT candidate_committee_id_refs_id_3ef1f89b FOREIGN KEY (candidate_committee_id) REFERENCES `calaccess_campaign_browser_co)')

[aboutaaron]

This could possibly be due to the database engine. InnoDB vs MyISAM. Investigating some more

[aboutaaron]

Solved by adding init_command value to OPTIONS key in DATABASES. See: http://stackoverflow.com/a/9699805/868724

Not sure how often this could happen, but it may be worth adding to docs @palewire

DATABASES = {
'default': {
    ...         
    'OPTIONS': {
         "init_command": "SET foreign_key_checks = 0;",
    },
 }
}

[rkiddy]

This is a MyISAM vs InnoDB issue in the sense that MyISAM does not support foreign keys, and therefore does not support foreign key constraints, and InnoDB does.

But are you intentionally creating your tables with these constraints? If they are there for a reason, then turning off the checking should be considered somewhat carefully.

I do not know much, yet, about everything that django does for you. Is code in the models like this, below (from filers.py), creating foreign key constraints in the tables?

    @models.permalink
    def get_absolute_url(self):
        return ('committee_detail', [str(self.pk)])

If so, is there are way to signal that the link is optional? Or is something else in the model causing django to create the constraints?

[aboutaaron]

I think I ran into this error because I was working with Django migrations. I'm not entirely sure what happened, but I started seeing this error AFTER I started using db migrations with the app. One theory I have is that | may have created the initial db in myISAM and then Django migrations used InnoDB. I'm not super knowledge about the difference between the two though or how Django migrations actually edit the schema so your guess is as good as mine.

[rkiddy]

I believe I see where the problem is. In the expenditures.py file, you have this right up near the top:

    committee = models.ForeignKey(
        'Committee',
        related_name='expenditures_to'
    )

So, I think that you either need to add a "null=True" parameter to that, so that the constraint does not get created, or else you have to find the place in the data where you have an expenditure which is not connected to a committee.

I do not know your preference, but since the data may always be messy, I would remove the constraint and then check for the condition after the data goes in. But then I use database constraints far less than I might and I always lean towards doing this kind of thing in my business logic, so I guess there it is. Sometimes a free suggestion is worth what you paid for it.

[aboutaaron]

Ahh that makes total sense.