Enums on MSSQL Need a Manual CHECK Constraint

SQL Server has no native enum type. I ran a small SQLModel + Alembic demo against MSSQL to see what actually happens when you model a Python Enum four different ways — what I expected vs what I got, and why the DB migration story is trickier than it looks.

Demo enum used throughout (names and values deliberately differ):

class Status(str, Enum):
    PASSED = "p"
    FAILED = "f"
    NEEDS_REVIEW = "r"

Stage 1: Plain SQLAlchemy Enum

class Evaluation(SQLModel, table=True):
    __tablename__ = "evaluation"

    id: int | None = Field(default=None, primary_key=True)
    status: Status | None = Field(
        default=None,
        # sa_column=Column(SAEnum(Status, name="status"), nullable=True),
    )

Expected: SQLAlchemy’s Enum type would give me a real DB-level enum, or at least a check constraint, restricting status to valid values.

What actually happened: Alembic autogenerate produced a revision using sa.Enum(...), but on MSSQL that just becomes a plain VARCHAR column. No check constraint, nothing. exec_sql("INSERT INTO evaluation (status) VALUES ('dummy')") succeeded — the database happily stored a value that isn’t even a valid Status member.

Stage 2: String-only column, no check

class Evaluation(SQLModel, table=True):
    __tablename__ = "evaluation"

    id: int | None = Field(default=None, primary_key=True)
    status: Status | None = Field(default=None, sa_type=String(32))

Expected: Same weak protection as Stage 1, just more honest about it.

What actually happened: Confirmed — inserting 'totally_invalid' directly via raw SQL worked with zero resistance. This makes it obvious the DB is not the enforcement point unless you add something explicit. Fine for a single trusted app, dangerous the moment another service or a raw SQL script touches the same table.

Stage 3: String-backed enum + named CheckConstraint in the model

class Evaluation(SQLModel, table=True):
    __tablename__ = "evaluation"
    __table_args__ = (
        enum_check_constraint(
            column_name="status",
            enum_class=Status,
            name="ck_evaluation_status_score",
        ),
    )

    id: int | None = Field(default=None, primary_key=True)
    status: Status | None = Field(
        default=None,
        sa_type=StringBackedEnum(enum_class=Status, length=32),
    )

Expected: Now that the model correctly declares a named CheckConstraint, Alembic autogenerate should produce a clean, applicable migration that creates the column and the constraint together.

What actually happened: The model metadata was correct, but the autogenerated migration referenced app.types.StringBackedEnum(...) without importing app — so the generated migration file was broken and not safe to apply as-is. Correct model code did not translate into a correct migration. This is the core lesson: autogenerate can’t be trusted blindly for custom/enum types on MSSQL.

Stage 4: Manual migration (the one that actually works)

Skipped autogenerate entirely and hand-wrote the migration:

op.create_table(
    "evaluation",
    sa.Column("id", sa.Integer(), nullable=False),
    sa.Column("status", sa.String(length=32), nullable=True),
    sa.CheckConstraint(sa.text("status IN ('f', 'p', 'r')"), name="ck_evaluation_status_score"),
    sa.PrimaryKeyConstraint("id"),
)

Expected: An explicit VARCHAR(32) column plus a named CHECK constraint should reject bad values at the DB layer, regardless of what inserts the row.

What actually happened: This is the only variant where INSERT INTO evaluation (status) VALUES ('totally_invalid') was rejected by the database itself. Valid inserts through create_evaluation(...) kept working normally.

Takeaways

1. SQL Server has no native enum type — everything ends up as VARCHAR.
2. Plain SQLAlchemy Enum on MSSQL does not give you a DB check, despite looking like it should.
3. String-only storage is too weak the moment more than one writer touches the table.
4. Model-level CheckConstraint + string-backed enum is the right model shape — but:
5. Alembic autogenerate is not the source of truth for enum migrations on MSSQL. Always review/hand-write the migration; don’t trust it blindly.