For the sake of this example I have four tables:
- ModelType
- ModelTypeA
- ModelTypeB
- Model
I am trying to model the following relationship among these tables:

To do that I am defining the classes using SQL Alchemy v1.4 in the following way:
Base = declarative_base()
class ModelType(Base):
__tablename__ = "modeltype"
id = Column(Integer, primary_key=True)
algorithm = Column(String)
models = relationship("Model", back_populates="modeltype")
__mapper_args__ = {
"polymorphic_identity": "modeltype",
"polymorphic_on": algorithm
}
def __repr__(self):
return f"{self.__class__.__name__}({self.algorithm!r})"
class ModelTypeA(ModelType):
__tablename__ = "modeltypea"
id = Column(Integer, ForeignKey("modeltype.id"), primary_key=True)
parameter_a = Column(Integer)
__mapper_args__ = {
"polymorphic_identity": "Model Type A"
}
class ModelTypeB(ModelType):
__tablename__ = "modeltypeb"
id = Column(Integer, ForeignKey("modeltype.id"), primary_key=True)
parameter_a = Column(Integer)
__mapper_args__ = {
"polymorphic_identity": "Model Type B"
}
class Model(Base):
__tablename__ = "model"
id = Column(Integer, primary_key=True)
trainingtime = Column(Integer)
modelversionid = Column(Integer, ForeignKey("modeltype.id"))
modeltype = relationship("ModelType", back_populates="models")
def __repr__(self) -> str:
return f"Model(id={self.id!r}, trainingtime={self.trainingtime!r})"
I am able to create the tables with:
with Session(engine) as session:
Base.metadata.create_all(engine)
This emits the following create table sql statements:
CREATE TABLE modeltype (
id INTEGER NOT NULL AUTOINCREMENT,
algorithm VARCHAR,
PRIMARY KEY (id)
)
CREATE TABLE modeltypea (
id INTEGER NOT NULL,
parameter_a INTEGER,
PRIMARY KEY (id),
FOREIGN KEY(id) REFERENCES modeltype (id)
)
CREATE TABLE modeltypeb (
id INTEGER NOT NULL,
parameter_b INTEGER,
PRIMARY KEY (id),
FOREIGN KEY(id) REFERENCES modeltype (id)
)
CREATE TABLE model (
id INTEGER NOT NULL AUTOINCREMENT,
trainingtime INTEGER,
modelversionid INTEGER,
PRIMARY KEY (id),
FOREIGN KEY(modelversionid) REFERENCES modeltype (id)
)
This looks good to me. But now when I try to actually create a model with reference to a modeltype like so:
model_type_a = ModelTypeA(parameter_a=3)
model = Model(trainingtime=10, modeltype=model_type_a)
session.add(model)
session.commit()
I get the following warning:
SAWarning: Column 'modeltypea.id' is marked as a member of the primary key for table 'modeltypea', but has no Python-side or server-side default generator indicated, nor does it indicate 'autoincrement=True' or 'nullable=True', and no explicit value is passed. Primary key columns typically may not store NULL.
and then the following error:
sqlalchemy.exc.IntegrityError: (snowflake.connector.errors.IntegrityError) 100072 (22000): NULL result in a non-nullable column [SQL: INSERT INTO modeltypea (parameter_a) VALUES (%(parameter_a)s)] [parameters: {'parameter_a': 3}] (Background on this error at: https://sqlalche.me/e/14/gkpj)
So it seems like when I create an instance of the inherited class ModelTypeA, it is not creating a row in ModelType and thus it has no id in ModelType to refer to and is instead trying to use NULL. How am I supposed to add rows when I have this sort of inheritance?