Django Foreign key in another schema

Question

I have a MySQL database with 2 shemas (A & B).
My django app can read & write into A.
It can just read from B.

My app managed all the tables in A.
B already contains some data in a table (b).

I want to add a one to one field between A and B.
Something like this :

class SchemaBTableB(models.Model):
    class Meta:
        managed = False
        db_schema = 'B'
        db_table = 'b'

    [...]

class SchemaATableA(models.Model):
    class Meta:
        db_schema = 'A'
        db_table = 'a'

    id = models.OneToOneField(
        SchemaBTableB,
        on_delete=models.DO_NOTHING,
        primary_key=True
    )

    [...]

Unfortunately, db_schema does not exist.
Does someone know a solution ?

Answer 1

I have been tried to use two databases to simulate your case, and find out the solution below:

1. Scenario:

1. database schema1, which is managed by django (read & write)
2. database schema2, which is NOT managed by django

2. Steps:

1. create migrations python manage.py makemigrations for your models
2. Generate SQL for your migration: python manage.py sqlmigrate app 0001. (suppose the generated migration file name is 0001_initial.py from step 1)

The sql for this migration should look like this:

CREATE TABLE `user_info` (`id_id` integer NOT NULL PRIMARY KEY, `name` varchar(20) NOT NULL);
ALTER TABLE `user_info` ADD CONSTRAINT `user_info_id_id_e8dc4652_fk_schema2.user_extra_info_id` FOREIGN KEY (`id_id`) REFERENCES `user_extra_info` (`id`);
COMMIT;

If you run the above sql directly, you will be ended up with error like this:

django.db.utils.OperationalError: (1824, "Failed to open the referenced table 'user_extra_info'")

That's because django assumes that all your migration steps are executed in the same database. So it can not find out the user_extra_info in schema1 database.

3. Following Steps:

3. Explicit specify the database schema2 for table user_extra_info:

   ALTER TABLE `user_info` ADD CONSTRAINT `user_info_id_id_e8dc4652_fk_schema2.user_extra_info_id` FOREIGN KEY (`id_id`) REFERENCES schema2.user_extra_info (`id`);
   

4. Manually run the revised sql in schema1 database.

5. Tell django I have run the migration myself: python manage.py migrate --fake

6. Done!!

---

Source code For your reference:

models.py

from django.db import models


class UserExtraInfo(models.Model):
    # table in schema2, not managed by django
    name = models.CharField('name', max_length=20)

    class Meta:
        managed = False
        db_table = 'user_extra_info'


class UserInfo(models.Model):
    # table in schema1, managed by django
    id = models.OneToOneField(
        UserExtraInfo,
        on_delete=models.CASCADE,
        primary_key=True
    )
    name = models.CharField('user name', max_length=20)

    class Meta:
        db_table = 'user_info'

settings.py

# Database
# https://docs.djangoproject.com/en/2.1/ref/settings/#databases

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'schema1',
        'USER': 'USER',
        'PASSWORD': 'PASSWORD',
        'HOST': 'localhost',
        'PORT': 3306,
    },
    'extra': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'schema2',
        'USER': 'USER',
        'PASSWORD': 'PASSWORD',
        'HOST': 'localhost',
        'PORT': 3306,
    }
}

DATABASE_ROUTERS = ['two_schemas.router.DBRouter']

router.py

class DBRouter(object):
    """
    A router to control all database operations on models in the
    auth application.
    """
    def db_for_read(self, model, **hints):
        """
        Attempts to read auth models go to auth_db.
        """
        if model._meta.db_table == 'user_extra_info':
            # specify the db for `user_extra_info` table
            return 'extra'
        if model._meta.app_label == 'app':
            return 'default'
        return None

    def db_for_write(self, model, **hints):
        """
        Attempts to write auth models go to auth_db.
        """
        if model._meta.db_table == 'user_extra_info':
            # specify the db for `user_extra_info` table
            return 'extra'
        if model._meta.app_label == 'app':
            return 'default'
        return None

    def allow_relation(self, obj1, obj2, **hints):
        """
        Relations between objects are allowed if both objects are
        in the primary/replica pool.
        """
        db_list = ('default', 'extra')
        if obj1._state.db in db_list and obj2._state.db in db_list:
            return True
        return None

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        """
        Make sure the auth app only appears in the 'auth_db'
        database.
        """
        if app_label == 'app':
            return db == 'default'
        return None

Answer 2

There is a difference between a OneToOne and ForeignKey: since you mentioned FK specifically - I'll assume you want to actually make a foreign-key, which is done like this: ForeignKey(SomeModel, unique=True)

class ObjectTableA(models.Model):

    id = models.ForeignKey(
        ObjectTableB,
        on_delete=models.DO_NOTHING,
        unique=True,
    )

    [...]


class ObjectTableB(models.Model):
    class Meta:
        managed = False
        db_table = 'b'

    [...]

The difference between OneToOne and ForeignKey is like this:

With regards to a OneToOne, conceptually, this is similar to a ForeignKey with unique=True, but the "reverse" side of the relation will directly return a single object.

In contrast to the OneToOneField "reverse" relation, a ForeignKey "reverse" relation returns a QuerySet.

Since your app manages TableA, get rid of the table_schema and db_table.

In your TableB model, you may want to get rid of the table_schema

Edit: since you're sure you want a OneToOne you should just be able to do something like this:

id = models.OneToOneField(
ObjectB, 
on_delete=models.DO_NOTHING,
)

Since your ObjectA is managed by Django, just link it up OneToOne to your ObjectB.