SQLAlchemy relationship between inherited tables throws primary key error

Question

I'm trying to set up a joined inheritance in SQLAlchemy which is working fine. My schema design requires a one-to-many relationship between two inherited table. My actual working example is quite complex but I was able to reproduce the issue with the SQLAlchemy joined inheritance tutorial code.

"""Joined-table (table-per-subclass) inheritance example."""

from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey
from sqlalchemy import inspect
from sqlalchemy import Integer
from sqlalchemy import or_
from sqlalchemy import String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session
from sqlalchemy.orm import with_polymorphic


Base = declarative_base()

class Resource(Base):
    __tablename__ = "resource"
    id = Column(Integer, primary_key=True)
    # Permissions and other common columns.
    # Left out for simplicity
    type = Column(String(50))

    __mapper_args__ = {
        "polymorphic_identity": "resource",
        "polymorphic_on": type,
    }

class ChildResource(Resource):
    __tablename__ = "child_resource"
    id = Column(ForeignKey("resource.id"), primary_key=True)
    name = Column(String(30))

    parent_id = Column(ForeignKey('parent_resource.id'), nullable=False)
    parent = relationship('ParentResource', back_populates='children', foreign_keys=parent_id)

    __mapper_args__ = {"polymorphic_identity": "child_resource"}

class ParentResource(Resource):
    __tablename__ = "parent_resource"
    id = Column(ForeignKey("resource.id"), primary_key=True)
    name = Column(String(30))

    children = relationship('ChildResource', back_populates='parent', foreign_keys='ChildResource.id')

    __mapper_args__ = {"polymorphic_identity": "parent_resource"}

if __name__ == "__main__":
    engine = create_engine("sqlite://", echo=True)
    Base.metadata.create_all(engine)

    session = Session(engine)

    res_child = ChildResource(
                name="My child shared resource",
            )

    res_parent = ParentResource(
                name="My parent shared resource"
            )

    res_parent.children.append(res_child)
    session.add(res_child)
    session.add(res_parent)

    session.commit()

So I have a ParentResource and a ChildResource. Both are inherited from a common Resource class (in real life the common base is necessary it contains much more columns). Between the ParentResource and the ChildResource there is a one-to-many relationship. The tables are created correctly in sqlite & postgres, but when I try add one parent and one child object to the session I've got the following error:

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: resource.id
[SQL: INSERT INTO resource (id, type) VALUES (?, ?)]
[parameters: (1, 'child_resource')]
(Background on this error at: http://sqlalche.me/e/gkpj)

When I check the SQLAlchemy echo I see the following.

2020-06-17 07:28:42,276 INFO sqlalchemy.engine.base.Engine ()
2020-06-17 07:28:42,276 INFO sqlalchemy.engine.base.Engine COMMIT
2020-06-17 07:28:42,280 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2020-06-17 07:28:42,280 INFO sqlalchemy.engine.base.Engine INSERT INTO resource (type) VALUES (?)
2020-06-17 07:28:42,280 INFO sqlalchemy.engine.base.Engine ('parent_resource',)
2020-06-17 07:28:42,281 INFO sqlalchemy.engine.base.Engine INSERT INTO parent_resource (id, name) VALUES (?, ?)
2020-06-17 07:28:42,281 INFO sqlalchemy.engine.base.Engine (1, 'My parent shared resource')
2020-06-17 07:28:42,281 INFO sqlalchemy.engine.base.Engine INSERT INTO resource (id, type) VALUES (?, ?)
2020-06-17 07:28:42,281 INFO sqlalchemy.engine.base.Engine (1, 'specific_resource_1')
2020-06-17 07:28:42,281 INFO sqlalchemy.engine.base.Engine ROLLBACK

It's looks like res_child and res_parent will get the same primary key which of course breaks the pk constraint. What am I doing wrong?

Answer 1

As above_c_level suggested the minimal solution is to change the primary key column name in the base class. The mistake what I made both the base class and the subclass had the "id" property which was overriden by the subclass. You can find below the working code sample.

"""Joined-table (table-per-subclass) inheritance example."""

from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey
from sqlalchemy import inspect
from sqlalchemy import Integer
from sqlalchemy import or_
from sqlalchemy import String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session
from sqlalchemy.orm import with_polymorphic


Base = declarative_base()

class Resource(Base):
    __tablename__ = "resource"
    resource_id = Column(Integer, primary_key=True)
    # Permissions and other common columns.
    # Left out for simplicity
    type = Column(String(50))

    __mapper_args__ = {
        "polymorphic_identity": "resource",
        "polymorphic_on": type,
    }

class ChildResource(Resource):
    __tablename__ = "child_resource"
    id = Column(ForeignKey("resource.resource_id"), primary_key=True)
    name = Column(String(30))

    parent_id = Column(ForeignKey('parent_resource.id'), nullable=False)
    parent = relationship('ParentResource', back_populates='children', foreign_keys=parent_id)

    __mapper_args__ = {"polymorphic_identity": "child_resource", "inherit_condition": id == Resource.resource_id}

class ParentResource(Resource):
    __tablename__ = "parent_resource"
    id = Column(ForeignKey("resource.resource_id"), primary_key=True)
    name = Column(String(30))

    children = relationship('ChildResource', back_populates='parent', foreign_keys='ChildResource.id')

    __mapper_args__ = {"polymorphic_identity": "parent_resource", "inherit_condition": id == Resource.resource_id}

if __name__ == "__main__":
    engine = create_engine("sqlite://", echo=True)
    Base.metadata.create_all(engine)

    session = Session(engine)

    res_child = ChildResource(
                name="My child shared resource",
            )

    res_parent = ParentResource(
                name="My parent shared resource"
            )

    res_parent.children.append(res_child)
    session.add(res_child)
    session.add(res_parent)

    session.commit()