I don't manage to create an autoincremented id for sqlite database

[12rambau]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from typing import Optional

from sqlmodel import Field, SQLModel


class CropStage(SQLModel, table=True):
    """The values that described the crop calendar months stages.

    The dataSQLModel is using shortcuts to make it more readable. Each one describe a specific stage of the plant
    """

    __tablename__ = "crop_stage"

    id: Optional[int] = Field(default=None, primary_key=True)

    short: str = Field(primary_key=True)
    "2 letter contraction of the stage name"

    name: str
    "Full name of the calendar value"

    info: Optional[str]
    "Specific information on the crop stage"

from sqlmodel import create_engine, SQLModel, Session

sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)

SQLModel.metadata.create_all(engine)

# fill the table with all the value for crop stage
stages = [
    CropStage(short="pr", name="Pre-Planting"),
    CropStage(short="so", name="Sowing"),
]

with Session(engine) as session:
    for stage in stages:
        session.add(stage)
        print(stage)
    session.commit()

Description

It's the first time I use the lib so there is a very high chance the mistake is coming from my side. I followed the tutorial and slightly adapted it to my need. For the classCropStage, I would like to have 3 columns short, nameand info. As described in the documentation (https://sqlmodel.tiangolo.com/tutorial/automatic-id-none-refresh/), I set up the id column as:

id: Optional[int] = Field(default=None, primary_key=True)

The problem is raised when I try to commit at the end of the session as the database is complaining that the id is not set:

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint failed: crop_stage.id

I thought the autoincrement stage was part of sqlmodel, was I wrong ?

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.19

Python Version

3.10

Additional Context

No response

[12rambau]

I found the problem but I don't know if it should be considered as a bug or a misusage.
My class has 2 primary_key as I change y mind during development. Removing the field for short made it work.