Inheritance with relations and unique constraintss

Question

I'm trying to achieve polymorphism as described in SQLAlchemy docs.

I don't really mind single table or joined table inheritance.

My constraints are

• Child classes have references to direct parent classes, and only direct parent classes, not any class in the hierarchy: child -> parent, grand-child -> child. (This kind of breaks polymorphism when you think of it, if polymorphism means that any child class can be substituted to classes above in the hierarchy.)

• Unique constraint on name (common attribute) + parent ID (defined in each subclass).

---

Joined table

This would allow me to define the relation from child to parent, but I don't know how to represent the unique constraint on ("name", "parent_id") in Child.

class Parent(Base):
    __tablename__ = "parents"
    __mapper_args__ = {
        "polymorphic_on": "type_",
        "polymorphic_identity": "parent",
    }

    id = sqla.Column(sqla.Integer, primary_key=True)
    name = sqla.Column(sqla.String(80), nullable=False)
    type_ = sqla.Column(sqla.String(50))


class Child(Parent):
    __tablename__ = "children"
    __mapper_args__ = {
        "polymorphic_identity": "child",
    }

    id = sqla.Column(sqla.ForeignKey("parents.id"), primary_key=True)
    parent_id = sqla.Column(sqla.ForeignKey("parents.id"), nullable=False)

From this question, I must use a single table to enforce the constraint at DB level.

Single table

Here, I can define the unique constraint. But I don't know how to restrict the relation to parent class to a specific type. E.g. ensure parent_id is a relation pointing to family table but only to rows of type parent.

class Parent(Base):
    __tablename__ = "family"
    __mapper_args__ = {
        "polymorphic_on": type_,
        "polymorphic_identity": "parent",
    }

    id = sqla.Column(sqla.Integer, primary_key=True)
    name = sqla.Column(sqla.String(80), nullable=False)


class Child(Parent):
    __table_args__ = (sqla.UniqueConstraint("parent_id", "name"),)
    __mapper_args__ = {
        "polymorphic_identity": "child",
    }

    parent_id = sqla.Column(sqla.ForeignKey("family.id"), nullable=False)

class OtherChild(Parent):
    __table_args__ = (sqla.UniqueConstraint("parent_id", "name"),)
    __mapper_args__ = {
        "polymorphic_identity": "other_child",
    }

    parent_id = sqla.Column(sqla.ForeignKey("family.id"), nullable=False)

How could I have both the relation and the unique constraint?

Answer 1

I came to the conclusion that the way around this is to duplicate name in the child tables.

class Parent(Base):
    __tablename__ = "parents"
    __mapper_args__ = {
        "polymorphic_on": "type_",
        "polymorphic_identity": "parent",
    }

    id = sqla.Column(sqla.Integer, primary_key=True)
    type_ = sqla.Column(sqla.String(50))


class Child(Parent):
    __tablename__ = "children"
    __table_args__ = (sqla.UniqueConstraint("parent_id", "name"),)
    __mapper_args__ = {
        "polymorphic_identity": "child",
    }

    id = sqla.Column(sqla.ForeignKey("parents.id"), primary_key=True)
    name = sqla.Column(sqla.String(80), nullable=False)
    parent_id = sqla.Column(sqla.ForeignKey("parents.id"), nullable=False)

class OtherChild(Parent):
    __tablename__ = "other_children"
    __table_args__ = (sqla.UniqueConstraint("parent_id", "name"),)
    __mapper_args__ = {
        "polymorphic_identity": "other_child",
    }

    id = sqla.Column(sqla.ForeignKey("parents.id"), primary_key=True)
    name = sqla.Column(sqla.String(80), nullable=False)
    parent_id = sqla.Column(sqla.ForeignKey("parents.id"), nullable=False)

My use case involves one parent table and multiple children tables, so this is duplication, but that's the way it is.

I should add that this is not a SQLAlchemy limitation/issue, but rather due to how the data is stored in DB tables. I don't think there can be a better way.