I have a table on MS SQL Server database in the schema rlt with an ID column as:

create table rlt.RelatedWord
(
    Id  int identity
        constraint PK_RelatedWord_Id
        primary key
)

I modelled following the SQLModel tutorial and the SQLalchemy documentation the following class:

class RelatedWord(SQLModel, table=True):
    __tablename__ = "RelatedWords"
    metadata = MetaData(schema="rlt")
    id: int | None = Field(sa_column=Column(name="Id", primary_key=True, default=None))

new_rw = RelatedWord()

with Session(db_engine) as session:
    session.add(new_rw)
    sessionmit()

When trying to insert a new entry I get the following error:

Instance <RelatedWord at 0x1b5de003fc0> has a NULL identity key. If this is an auto-generated value, check that the database table allows generation of new primary key values, and that the mapped Column object is configured to expect these generated values.

When I set the Column to autoincrement=True I can no longer use it as an optional int. SQLModel documentation suggest that this is needed as when the object is created the attribute must be ´None´ and will be set after the commit.

How can I setup an ID column with autoincrement in SQLModel?

I have a table on MS SQL Server database in the schema rlt with an ID column as:

create table rlt.RelatedWord
(
    Id  int identity
        constraint PK_RelatedWord_Id
        primary key
)

I modelled following the SQLModel tutorial and the SQLalchemy documentation the following class:

class RelatedWord(SQLModel, table=True):
    __tablename__ = "RelatedWords"
    metadata = MetaData(schema="rlt")
    id: int | None = Field(sa_column=Column(name="Id", primary_key=True, default=None))

new_rw = RelatedWord()

with Session(db_engine) as session:
    session.add(new_rw)
    sessionmit()

When trying to insert a new entry I get the following error:

Instance <RelatedWord at 0x1b5de003fc0> has a NULL identity key. If this is an auto-generated value, check that the database table allows generation of new primary key values, and that the mapped Column object is configured to expect these generated values.

When I set the Column to autoincrement=True I can no longer use it as an optional int. SQLModel documentation suggest that this is needed as when the object is created the attribute must be ´None´ and will be set after the commit.

How can I setup an ID column with autoincrement in SQLModel?

• sql-server
• sqlalchemy
• fastapi
• sqlmodel

• 3 Have you read the SQLAlchemy documentation regarding Autoincrement, Sequences, Identity and their differences? If you want an identity column in the database have you tried using an Identity() argument in the column definition? – AlwaysLearning Commented Mar 5 at 21:17
• 1 Try id: int | None = Field(sa_column=Column("Id", Identity(), primary_key=True)) – Gord Thompson Commented Mar 5 at 23:37
• Ah, obviously. The error message threw me of road. Thank you. Could you add you comment as answer so I can close the question? – aggsol Commented Mar 6 at 8:26

1 Answer 1

The comment from Gord Thompson is the correct way:

id: int | None = Field(sa_column=Column("Id", Identity(), primary_key=True))

The problem is a basic misunderstanding: An autoincrement column of type int as a primary key is not an identity.