I want to have a foreign key on a child model that is set to null when the parent model is deleted (this is default behaviour in sqlmodel when the parent model has a backreference to the child model but I don't have that).
In order to that one can use ForeignKey(..., ondelete='SET NULL') in sqlalchemy. To use this behaviour in sqlmodel I'm doing:
class Channel(SQLModel, ...):
...
dataset_id: int | None = Field(
sa_column=Column(Integer, ForeignKey('dataset.id', ondelete='SET NULL')),
)
But I'm getting the error:
sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Channel.dataset - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.
If I just use Field(foreign_key='dataset.id') for the dataset_id field then it works. I looked through the sqlmodel code -- If it sees the sa_column arg in the field defnition. Otherwise it builds the Column instance from the args provided and supplies the foreign_key arg as is to instantiate a ForeignKey instance. I removed ondelete in the call above to check if that was causing the problem since it was the only difference but that didn't work either. What could be the problem here?
