Django Postgres circular manytomany reference - Insert or update on table violates foreign key constraint

Question

BLUF: How can I get poc.poc_employer.add(new_supplier) to use new_supplier object id?

Background: In development on my local machine using sqlite3 I had no issues with the following code. Once deployed to Postgres on Heroku is when I first ran into this issue.

The issue involves two models that reference each other. First is Company which is used to represent a user's company as well as the user's supplier companies. The second is POC which stores info for the user's POC within a specific supplier organization.

Models:

class Company(models.Model):
    ...
    suppliers = models.ManyToManyField('self', db_index=True, symmetrical=True, related_name = "the_suppliers", blank = True)
    POC = models.ManyToManyField('Supplier_POC', db_index=True, symmetrical=True, blank = True)
    ...

    def __str__(self):
        return self.company_name


class Supplier_POC(models.Model):
    poc_employer = models.ManyToManyField('Company', symmetrical=True, blank = True)
    poc_name = models.CharField(blank=True, null=True, max_length=200)
    poc_phone = models.CharField(blank=True, null=True, max_length=200)
    poc_email = models.EmailField(blank=True, null=True, max_length=200)

The user story involves someone adding a supplier to their company's approved suppliers list by inputting the supplier name, poc name, poc phone, and poc email onto a form and submitting. Then the view either gets or creates the supplier object, adds it to the user's company object, creates the POC object, and adds the supplier to the POC object as poc_employer. In the code below, the supplier does not already exist and must be created.

View:

#Create and add supplier to Company table as non-member
new_supplier = Company(company_name = supplier, company_domain = domain, member = False)
new_supplier.save()


#Add new supplier to user company's supplier list.  No issues with this.
new_supplier =  Company.objects.get(company_name__iexact = supplier)
company.suppliers.add(new_supplier)



#Save POC to poc table.  No issues with this.
if phone != ""  :
    poc = Supplier_POC( poc_name=name, poc_phone = phone, poc_email = email )

else:
    poc = Supplier_POC( poc_name=name, poc_phone = "", poc_email = email )
    poc.save()

#Add new_supplier to poc object.  Here's the trouble spot
try:
    poc.poc_employer.add(new_supplier)
except Exception as e:
    print('error trying to add supplier to poc object')
    print(e)

The Error from Heroku logs:

insert or update on table "supplyhelix_supplier_poc_poc_employer" violates foreign key constraint "supplyhelix_company_id_19a0767e7b462d_fk_supplyhelix_company_id"

DETAIL: Key (company_id)=(11) is not present in table "supplyhelix_company".

For other similar questions I've seen on Stackoverflow, the solution was simply that the object being added hadn't been created and/or saved. As you can see in the view, the object is both created and saved and it is successfully added to the user's company object prior to the error when trying to add to the POC object.

I have printed out the id of the new_supplier object a few times to compare the the Error Detail above and they do not match. For example, the specific test that produced the error above, new_supplier.id was 14 but the add was apparently looking for id=11 which I had deleted from the database previously.

Not sure why but from what I can tell, instead of simply using the id of the passed supplier object, poc.poc_employer.add(new_supplier) is simply using an auto-incremented id that increments by 1 each time it tries to add a supplier object to the poc.

I know this is long. Thank you very much for reading this far! I really appreciate your time and any feedback/assistance you might have for me.

Answer 1

I'd think about redoing your models. It looks like you are using the Company model to be both a Company and a Supplier. I understand that a Supplier is a company, but if you are treating them differently, it might be best to split them apart. Potentially you can have Company be a base model, and have Suppliers and Employers be inherited from it.

As far as your models go, I would consider writing them as:

class Company(models.Model):
    company_name = ...
    ...

class Employer(Company):
    suppliers = models.ManyToManyField('Supplier', db_index=True, related_name="employers", blank=True)


class Supplier(Company):
    ...

class POC(models.Model):
    name = models.CharField(blank=True, null=True, max_length=200)
    phone = models.CharField(blank=True, null=True, max_length=200)
    email = models.EmailField(blank=True, null=True, max_length=200)
    ...

class SupplierPOC(POC):
    supplier = models.ForeignKeyField('Supplier', blank=True)

For your view, I would consider writing it like:

#Create and add supplier to Company table as non-member
new_supplier = Supplier(company_name=supplier_name, company_domain=domain, member=False)
new_supplier.save()

#Add new supplier to user company's supplier list. (I wouldn't use names. Instead use id's.)
new_supplier = Supplier.objects.get(company__id=supplier.id)
employer.suppliers.add(new_supplier)

#Save POC to poc table.
poc = SupplierPOC(supplier=new_supplier, name=name, phone=phone, email=email)
poc.save()

Disregarding all that, you don't have a save in your if phone != "" : block which means that the the poc won't get saved or added.

Answer 2

First of all, thank you to Curtis Olson and knelson for your time and suggestions. I really appreciate it! Working through both suggestions kept me moving forward in the face of great frustration. Also, I apologize for not posting sooner. My in-laws are in town and I foolishly expected more time to work on this than I actually had.

Alright, so here are the changes I made:

I made the poc_employer field of Supplier_POC a foreign key to Company.

poc_employer = models.ForeignKey('Company', blank = True, null = True)

Then in the view I set the id for the supplier being added to poc_employer explicitly following an example I found here:

Django: Set foreign key using integer?

poc = Supplier_POC( poc_name=name, poc_phone = phone, poc_email = email )
poc.poc_employer_id = new_supplier.id
poc.save()  #knelson, thanks for the catch on the save.

This is working both locally in sqlite3 and on heroku Postgres. Not sure if I'd have been able to do something similar if I'd kept poc_employer a manytomany field, but may look into it later.

Thanks again!