sqlalchemy foreignkey relation with parent class

Question

I have the following database schema:

Parent table:

1. id - primary key identifier.
2. type - polymorphic_identity.
3. name - string data column.

Child table - inherits Parent:

1. id - primary key identifier.
2. parent_id - foreignkey to Parent.
3. category - string data column.

Summing up I have two tables. Table Child inherits from Parent and also have a foreignkey to it.

UPD: I really need both inheritance and foreignkey. This example is only a short demo which reproduces the problem.

I used declarative_base to declare the schema:

# -*- coding: utf-8 -*-

from sqlalchemy import Column, String, Integer, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Parent(Base):
  __tablename__ = "Parent"
  id = Column(Integer, primary_key=True)
  type = Column(String(250))

  name = Column(String(250))

  __mapper_args__ = {
    'polymorphic_identity':'Parent',
    'polymorphic_on':type
  }

class Child(Parent):
  __tablename__ = 'Child'
  id = Column(Integer, ForeignKey('Parent.id'), primary_key=True)

  parent_id = Column(ForeignKey("Parent.id"), nullable=True)
  category = Column(String(250))

  __mapper_args__ = {
    'polymorphic_identity':'Child',
  }

engine = create_engine('postgresql+psycopg2://joe:joe@localhost/alch')

session = sessionmaker()
session.configure(bind=engine)
Base.metadata.create_all(engine)

But when I run the code I get the following error:

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

I have tried to set relationship attribute myself for Parent or for Child separately and for both too. Tried to use primaryjoin and foreign_keys parameters of relationship. But the error was the same.

I'm totally confused about this error. I need help.

Answer 1

I found the solution here.

SQLAlchemy needs a hint in this situation: a inherit_condition field in Child's __mapper_args__ does the trick.

# -*- coding: utf-8 -*-

from sqlalchemy import Column, String, Integer, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Parent(Base):
  __tablename__ = "Parent"
  id = Column(Integer, primary_key=True)
  type = Column(String(250))

  name = Column(String(250))

  __mapper_args__ = {
    'polymorphic_identity':'Parent',
    'polymorphic_on':type
  }

class Child(Parent):
  __tablename__ = 'Child'
  id = Column(Integer, ForeignKey('Parent.id'), primary_key=True)

  parent_id = Column(ForeignKey("Parent.id"), nullable=True)
  category = Column(String(250))

  parent = relationship(Parent, foreign_keys=[parent_id])

  __mapper_args__ = {
    'polymorphic_identity':'Child',
    'inherit_condition': id == Parent.id, 
  }

engine = create_engine('postgresql+psycopg2://joe:joe@localhost/alch')

session = sessionmaker()
session.configure(bind=engine)
Base.metadata.create_all(engine)

Answer 2

Have you tried removing the Foreign Key for the Child id field?

id = Column(Integer, ForeignKey('Parent.id'), primary_key=True)
parent_id = Column(ForeignKey("Parent.id"), nullable=True)

You need something like this:

id = Column(Integer, auto_increment=True, primary_key=True)
parent_id = Column(ForeignKey("Parent.id"), nullable=True)