Ambiguous foreign key in self-referencing table [SQLAlchemy/Alembic]

Question

I have a model classes defined like this. The idea is that that Person will hold general information about people as well as reference to it's "sub-classes": Woman and Men as parents. Woman and Men will hold information specific for that gender.

I'm using Alembic to generate the migration but I'm getting an error:

sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between 'person' and 'woman'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly.

Note that Woman inherits from Person and therefore there is also the reference from woman to person.

Nothing I tried works:

• Remove line mother = relationship('Woman') with the idea that SQL alchemy doesn't understand that relationship
• Adding mother_id = Column(Integer, ForeignKey('woman.id'), nullable=True)
• Adding mother = relationship('Woman', primaryjoin='Person.mother_id==Mother.id')

• Specifying onclause isn't possible (or I don't know how) since the join is auto-generated

  class Person(Base):
      __tablename__ = 'person'
  
      id = Column(Integer, primary_key=True)
      gender = Column(String(5))
      name = Column(String(32), nullable=False)
      surname = Column(String(32), nullable=False)
      age = Column(Float, primary_key=True)
  
      mother_id = Column(Integer, ForeignKey('woman.id'), nullable=True)
      mother = relationship('Woman')
  
      _mapper_args__ = {
          'polymorphic_on': gender
      }
  
  
  class Woman(Person):
      __tablename__ = 'woman'
      __mapper_args__ = {
          'polymorphic_identity': 'woman'
      }
  
      id = Column(Integer, ForeignKey('person.id'), primary_key=True)
  

Full Trace:

$ alembic revision --autogenerate -m "mother"
Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.4/bin/alembic", line 11, in <module>
    sys.exit(main())
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/alembic/config.py", line 479, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/alembic/config.py", line 473, in main
    self.run_cmd(cfg, options)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/alembic/config.py", line 456, in run_cmd
    **dict((k, getattr(options, k)) for k in kwarg)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/alembic/command.py", line 117, in revision
    script_directory.run_env()
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/alembic/script/base.py", line 416, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/alembic/util/pyfiles.py", line 93, in load_python_file
    module = load_module_py(module_id, path)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/alembic/util/compat.py", line 68, in load_module_py
    module_id, path).load_module(module_id)
  File "<frozen importlib._bootstrap>", line 539, in _check_name_wrapper
  File "<frozen importlib._bootstrap>", line 1614, in load_module
  File "<frozen importlib._bootstrap>", line 596, in _load_module_shim
  File "<frozen importlib._bootstrap>", line 1220, in load
  File "<frozen importlib._bootstrap>", line 1200, in _load_unlocked
  File "<frozen importlib._bootstrap>", line 1129, in _exec
  File "<frozen importlib._bootstrap>", line 1471, in exec_module
  File "<frozen importlib._bootstrap>", line 321, in _call_with_frames_removed
  File "migrations/env.py", line 24, in <module>
    import api.model
  File "/Users/jakubt/Projects/hex/api/model.py", line 31, in <module>
    class Woman(Person):
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/ext/declarative/api.py", line 64, in __init__
    _as_declarative(cls, classname, cls.__dict__)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/ext/declarative/base.py", line 88, in _as_declarative
    _MapperConfig.setup_mapping(cls, classname, dict_)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/ext/declarative/base.py", line 103, in setup_mapping
    cfg_cls(cls_, classname, dict_)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/ext/declarative/base.py", line 135, in __init__
    self._early_mapping()
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/ext/declarative/base.py", line 138, in _early_mapping
    self.map()
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/ext/declarative/base.py", line 530, in map
    **self.mapper_args
  File "<string>", line 2, in mapper
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/orm/mapper.py", line 671, in __init__
    self._configure_inheritance()
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/orm/mapper.py", line 978, in _configure_inheritance
    self.local_table)
  File "<string>", line 2, in join_condition
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/sql/selectable.py", line 965, in _join_condition
    a, b, constraints, consider_as_foreign_keys)
  File "/Library/Frameworks/Python.framework/Versions/3.4/lib/python3.4/site-packages/sqlalchemy/sql/selectable.py", line 1055, in _joincond_trim_constraints
    "join explicitly." % (a.description, b.description))
sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between 'person' and 'woman'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly.

Answer 1

What I see as issue here is that you are referencing foreign key to foreign key.

mother_id -> foreign_key(woman.id)
woman.id -> foreign_key(person.id)

And you create circular dependancy. You cannot create that dependency in any single creation statement in any SQL dialect. You'll first need to create person table, then woman table, then add foreign key (mother_id) from first to second table (note that you cannot create women table first as it has foreign key to person table).

I don't think alembic is smart enough to do this, and I think that what you want to achieve is not good thing to do.

Here is what I suggest:

woman.id will always have same value for one record as person.id, so first foreign key should reference to person table instead of mother table.

Change definition of mother_id and mother:

mother_id = Column(Integer, ForeignKey('person.id'), nullable=True)
mother = relationship('Person', primaryjoin='Person.mother_id==Person.id')

Tip: you don't need second table if you're storing just id (just remove __tablename__ from second class)

And check in code for gender when adding mother/father.