AmbiguousForeignKeysError or CircularDependencyError

[Boris Sabatier]

Hi,

I have a problem to do the model I need.
I have 3 types of object : Category, Picture and Video.

All of these types have some common part, like a name.
Also I want to be able to select all object with a name like "bar" (Category, Picture and Video)
So I use the Joined_table inheritance pattern. All work fine.

But I want to add : "they all can be child of a Category"
So I done this model :

class Item(Base):
    __tablename__ = 'item'
    uid = Column(Integer, primary_key=True)
    name = Column(String(50))
    item_type = Column(String(50))
    parent_id = Column(Integer, ForeignKey('category.uid'), nullable=True)
    __mapper_args__ = {
        'polymorphic_identity':'item',
        'polymorphic_on':item_type,
    }
###############################################################
class Picture(Item):
    __tablename__ = 'picture'
    uid = Column(Integer, ForeignKey('item.uid'), primary_key=True)
    __mapper_args__ = {
        'polymorphic_identity':'picture',
    }
###############################################################
class Video(Item):
    __tablename__ = 'picture'
    uid = Column(Integer, ForeignKey('item.uid'), primary_key=True)
    __mapper_args__ = {
        'polymorphic_identity':'video',
    }
###############################################################
class Category(Item):
    __tablename__ = 'category'
    uid = Column(Integer, ForeignKey('item.uid'), primary_key=True)
    __mapper_args__ = {
            'inherit_condition': (uid==Item.uid),
            'polymorphic_identity': 'category'
    }

First I add parent_id Column in the Item class. But when I do this, I have AmbiguousForeignKeysError exception:

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

After some research on internet (documentation, stack overflow and this maling list) I heard about the 'inherit_condition'.
So I add it in the __mapper_args__ of Category. But when I do that, I have a CircularDependencyError:

sqlalchemy.exc.CircularDependencyError: Circular dependency detected. Cycles:
{
    Table(
        'item',
        MetaData(bind=None),
        Column('uid', Integer(), table=<item>, primary_key=True, nullable=False),
        Column('name', String(length=50), table=<item>),
        Column('item_type', String(length=50), table=<item>),
        Column('parent_id', Integer(), ForeignKey('category.uid'), table=<item>),
        schema=None),
    Table(
        'category',
        MetaData(bind=None),
        Column('uid', Integer(), ForeignKey('item.uid'), table=<category>, primary_key=True, nullable=False),
        schema=None)
}
all edges:
{
    (
        Table(
            'item',
            MetaData(bind=None),
            Column('uid', Integer(), table=<item>, primary_key=True, nullable=False),
            Column('name', String(length=50), table=<item>),
            Column('item_type', String(length=50), table=<item>),
            Column('parent_id', Integer(), ForeignKey('category.uid'), table=<item>),
            schema=None),
        Table(
            'category',
            MetaData(bind=None),
            Column('uid', Integer(), ForeignKey('item.uid'), table=<category>, primary_key=True, nullable=False),
            schema=None)
    ),
    (
        Table(
            'item',
            MetaData(bind=None),
            Column('uid', Integer(), table=<item>, primary_key=True, nullable=False),
            Column('name', String(length=50), table=<item>),
            Column('item_type', String(length=50), table=<item>),
            Column('parent_id', Integer(), ForeignKey('category.uid'), table=<item>),
            schema=None),
        Table(
            'video',
            MetaData(bind=None),
            Column('uid', Integer(), ForeignKey('item.uid'), table=<video>, primary_key=True, nullable=False),
            schema=None)
    ),
    (
        Table(
            'item',
            MetaData(bind=None),
            Column('uid', Integer(), table=<item>, primary_key=True, nullable=False),
            Column('name', String(length=50), table=<item>),
            Column('item_type', String(length=50), table=<item>),
            Column('parent_id', Integer(), ForeignKey('category.uid'), table=<item>),
            schema=None),
        Table(
            'picture',
            MetaData(bind=None),
            Column('uid', Integer(), ForeignKey('item.uid'), table=<picture>, primary_key=True, nullable=False),
            schema=None)
    ),
    (
        Table(
            'category',
            MetaData(bind=None),
            Column('uid', Integer(), ForeignKey('item.uid'), table=<category>, primary_key=True, nullable=False),
            schema=None),
        Table(
            'item',
            MetaData(bind=None),
            Column('uid', Integer(), table=<item>, primary_key=True, nullable=False),
            Column('name', String(length=50), table=<item>),
            Column('item_type', String(length=50), table=<item>),
            Column('parent_id', Integer(), ForeignKey('category.uid'), table=<item>),
            schema=None)
    )
}

I joined the python file use to test some solutions.
Have you got an idea to solve my issue ?

Thanks a lot

Boris

[Simon King]

The problem is that you've got a foreign key from "category.uid" to
"item.uid", and also a foreign key from "item.parent_id" to
"category.uid". Normally, foreign keys are defined in the "CREATE
TABLE" sql statement, but that can't work when you have a circular
dependency, because whichever table you define first will try to refer
to another table which doesn't exist yet.

The solution is "use_alter", which is a flag you can pass when
defining your ForeignKey:

http://docs.sqlalchemy.org/en/rel_0_9/core/constraints.html#sqlalchemy.schema.ForeignKeyConstraint.params.use_alter

When it is set, SQLAlchemy will create the table *without* the foreign
key, then when all the tables have been defined it will run an "ALTER
TABLE" statement to create the foreign key.

Hope that helps,

Simon

[Boris SABATIER]

Thanks Simon it's work !!!

> --
> You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/2MJwMTuSt3w/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Jonathan Vanasco]

Just a quick warning on this pattern (which I use as well)...

When dealing with Edits/Updates and Database Migrations, you might run into issues where any mix of SqlAlchemy commands simply will not make the mapper happy.

A workaround is to ignore the session and do these operations using the core SqlAlchemy engine -- you can access the tables off each Object Class , and the database connection off the session object.  You run the risk of having different info in the session though, so need to avoid that.