How we can set unique constraint in sql model?

[faris404]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue 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

pass

Description

How we can set unique constraint in sql model?

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

3.8.10

Additional Context

No response

[obassett]

To use unique you need to add the sa_column field bits and implement like SQL Alchemy does.

from typing import Optional
import datetime
from sqlmodel import SQLModel, Field, Column, VARCHAR
from pydantic import EmailStr

class UserBase(SQLModel):
    username: EmailStr = Field(sa_column=Column("username", VARCHAR, unique=True))
    full_name: Optional[str] = None
    disabled: Optional[bool] = None
    lastLoggedInUTC: Optional[datetime.datetime] = None
    userCreationDateUTC: datetime.datetime = datetime.datetime.utcnow()

So in my case I wanted username to be a unique email address - so set it to a field set the column name, data type and added unique=True.

[faris404]

@obassett It Works!! Thank you for your valuable time😊

[raulolteanu-sudo]

Related to this, how can you add Table Level Constraints to SQL Model ?
The equivalent of this in SQLModel:

students = Table(
        'students', meta,
         Column('id', Integer, primary_key=True, autoincrement=False),
         CheckConstraint('id > 2', name='check1'),            <----- this bit
    ) ```

[rochacbruno]

There is an easy way

Field(sa_column_kwargs={"unique": True})

[rochacbruno]

@raulolteanu-sudo you can write your custom data type with the validation as in https://pydantic-docs.helpmanual.io/usage/types/#custom-data-types

But note that this works in the programming level and not on SQL level.

[danielbraun89]

There is an easy way

Field(sa_column_kwargs={"unique": True})

sa_column_kwargs is amazing, havnt seen it anywhere in the docs. Thanks!

[falkben]

There is an easy way

Field(sa_column_kwargs={"unique": True})

Just wanted to report that for me this did not work until I explicitly set index=False for the column. E.g.:

Field(index=False, sa_column_kwargs={"unique": True})

The other option in which you create the Column by hand with the sa_column param works, as did specifying __table_args__ = (UniqueConstraint("appid"),) as a class attribute for the table.

I think there's some logic in SQLAlchemy that dictates that when a column has an index the index actually gains the unique constraint. Can see more in the sqlalchemy Column docstring

Since all columns currently are created with an index by default in sqlmodel (see #11), we have to disable the index to see the unique constraint applied directly to the column.