6

This my model of first Database DB1: 

     from django.db import models 

     class Company(models.Model): 

          name = models.CharField(max_length=100, null=True)
          address = models.TextField(max_length=200, null=True)
          website = models.CharField(max_length=200, null=True)
          conatct_no = models.CharField(max_length=20, null=True)
          email = models.EmailField(max_length=20, null=True)
          logo = models.FileField(upload_to='logo/', blank=True, null=True)
          created = models.DateTimeField('company created', auto_now_add=True)
          updated = models.DateTimeField('company updated', auto_now=True, null=True)
          def __unicode__(self):  # Python 3: def __str__(self):
                return self.name

Model of 2nd Database Db2: 

    from django.db import models

    from leavebuddymaster.models import Company

    class Department(models.Model): 
       company = models.ForeignKey(Company)
       department_name = models.CharField(max_length=50, null=True)
       created = models.DateTimeField('department created', auto_now_add=True)
       updated = models.DateTimeField('department updated', auto_now=True, null=True)
       def __unicode__(self):  # Python 3: def __str__(self):
            return self.department_name

Now when i open the Department table it gives me a error as:

      ProgrammingError at /admin/leavebuddyapp/department/
      (1146, "Table 'leavebuddy_master.leavebuddyapp_department' doesn't exist")

I have done all the settings in settings.py correctly for the two databases. Can you please guide me in the right direction. Thanx in advance.

Shivratna
  • 190
  • 2
  • 11
  • I haven't played around with multiple databases and Django yet but have you perhaps tried to use souths 'schemamigration' to see if it creates the table for your model? See http://south.readthedocs.org/en/latest/tutorial/part1.html – Hevlastka Apr 24 '14 at 13:11
  • Yeah it does create the tables in the databases separately. But i guess django currently doesn't support foreign key for cross database. – Shivratna Apr 24 '14 at 14:07

1 Answers1

17

You're correct, Django does not currently support foreign key relationships spanning multiple databases. From Cross-database relations [Edit 2020: Django version bump]:

If you have used a router to partition models to different databases, any foreign key and many-to-many relationships defined by those models must be internal to a single database.

This is because of referential integrity. In order to maintain a relationship between two objects, Django needs to know that the primary key of the related object is valid. If the primary key is stored on a separate database, it’s not possible to easily evaluate the validity of a primary key.

A solution I thought up that you could try (though it may present other problems):

from leavebuddymaster.models import Company

class Department(models.Model): 
    company_id = models.IntegerField()
    
    @property
    def company(self):
        return Company.objects.get(pk=self.company_id)

This allows you to refer to Department.company like you normally would in your example. Setting it would just be a matter of Department.company_id = Company.pk. Hope it helps, or at least inspires a better solution!

Community
  • 1
  • 1
pcoronel
  • 3,833
  • 22
  • 25
  • 1
    some other solutions and alternatives here: http://stackoverflow.com/questions/6830564/how-to-use-django-models-with-foreign-keys-in-different-dbs – Ronen Ness Oct 04 '15 at 12:28
  • 1
    thanks a lot, after a long time searching, I found this answer. There is another solutions for the issue, but i think this is simplest way by a creative idea! :)) – Mohammad Reza Dec 03 '19 at 16:57
  • i don't know why this solution do not work for me :( – MSepehr Apr 03 '20 at 13:34
  • does this solution allow to annotate fields with the django ORM syntax to access fields from the remote database? eg `Department.objects.annotate(company_name=F('company__name'))` – EnriqueH Jul 12 '20 at 21:48