How to define unique constraint in table columns

[raphaelgibson]

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

class User(SQLModel, table=True):
    user_uuid: UUID = Field(default=uuid4, primary_key=True)
    name: str
    email: str
    password: str
    balance: float = Field(default=0.0)
    income: float = Field(default=0.0)

Description

Hi, guys!

I want to define something like:
email: str = Field(unique=True)

But Field does not have unique param, like SQLAlchemy Column have:
Column(unique=True)

I've searched in Docs, Google and GitHub, but I found nothing about unique constraint.

Thanks for your attention!

Operating System

Windows

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

3.9.4

Additional Context

No response

[obassett]

see #65 - it has the how to do it.

Basically you specify something like - email: EmailStr = Field(sa_column=Column("email", VARCHAR, unique=True)) the = Field(sa_column=Column("username", VARCHAR, unique=True))

[raphaelgibson]

see #65 - it has the how to do it.

Basically you specify something like - email: EmailStr = Field(sa_column=Column("email", VARCHAR, unique=True)) the = Field(sa_column=Column("username", VARCHAR, unique=True))

Thanks very much for your help, @obassett! Nevertheless, i have opened a Pull Request to use Unique constraint directly by the sqlmodel whithout using sa_column param.
PR: #83

[JeyDi]

I had the same question, so just for documentation I put my complete example :)

Hope that can help someone.

So I had to define a list of product with an integer primary key id and a unique name for the products.
The class: BaseProduct is the default definition of the Product.
The class: Product is the DB model.

The file Product.py with the BaseProduct definition is

from sqlmodel import SQLModel
class ProductBase(SQLModel):
    name: str
    description: str
    price: float
    available: bool

The file: product.py with the definition of Product is

from typing import Optional
from sqlalchemy import String
from sqlalchemy.sql.schema import Column
from sqlmodel import Field
from app.src.schemas.entities import ProductBase


class Product(ProductBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
   # name is unique
    name: str = Field(sa_column=Column("name", String, unique=True))

Of course the relationship with ProductType and ProductTagLink are defined in another files and schemas :)

Hope this example helps :)

[StefnirKristjansson]

Found a solution that I think is more elegant using table_args

from typing import Optional

from sqlmodel import Field, SQLModel, UniqueConstraint

class users(SQLModel, table=True):
    __table_args__ = (UniqueConstraint("external_id"),)
    id: Optional[int] = Field(default=None, primary_key=True)
    email: str

[sgraaf]

Why not simply add sa_column_kwargs={"unique": True} to Field()?

[shifqu]

@StefnirKristjansson not sure that the usage of table_args is more elegant, but it is certainly what I was looking for. This way you can define an actual UniqueConstraint that spans multiple fields (or columns, whichever terminology you prefer).

@sgraaf 's solution feels the most elegant one for single field constraints.

edit: However, both my statements are pure personal preference, no solid grounds on why they would be more or less elegant :)

[Coding-Crashkurse]

@sgraaf : Thank you, that works fine! Where is that "trick" documented?

[sgraaf]

@Data-Mastery Honestly?... Nowhere! I had to dive (deep) into the source code of SQLModel to find it.

While the docs are really good in some aspects (very heavy on examples / guides / tutorials), the lack of a comprehensive API reference is very unfortunate.

[oldfielj-ansto]

This isn't documented as it is a feature from SQLAlchemy, but you can define unique constraints at the model level using "table_args".

from sqlmodel import SQLModel, Field
from sqlalchemy import UniqueConstraint


class Employee(SQLModel, table=True):
    """Employee Model"""

    __table_args__ = (UniqueConstraint("employee_id"),)

    employee_id: int = Field(
        title="Employee ID",
    )
    firstname: str = Field(
        title="First Name",
    )
    lastname: str = Field(
        title="Last Name",
    )

So the above code would add a constraint to prevent duplicate entries in "employee_id".

I've only tested this on a PostgreSQL database, but it should work fine for others.

The benefit of doing it this way is you avoid having to override the column definition at the field level.

https://docs.sqlalchemy.org/en/14/orm/declarative_tables.html

[epicwhale]

It looks like the Field column now supports the unique=True/False keyword argument? Merged in #83

I was able to successfully use code: str = Field(index=True, unique=True) and it generated a CREATE UNIQUE INDEX IF NOT EXISTS ix_venue_code ... in the output schema

[raphaelgibson]

It looks like the Field column now supports the unique=True/False keyword argument? Merged in #83

I was able to successfully use code: str = Field(index=True, unique=True) and it generated a CREATE UNIQUE INDEX IF NOT EXISTS ix_venue_code ... in the output schema

Yes, I have sent a PR with this feature after ask this question here.

[quillan86]

Was it removed? I am running sqlmodel version 0.3.0 and it is saying that it is not there in Field.

[raphaelgibson]

Was it removed? I am running sqlmodel version 0.3.0 and it is saying that it is not there in Field.

Version 0.3.0? The latest version is 0.0.8, and the support for unique constraint was added on version 0.0.7

[wamiqraza]

Unique constraints can be created anonymously on a single column using the unique keyword on Column. Explicitly named unique constraints and/or those with multiple columns are created via the UniqueConstraint table-level construct.

from sqlalchemy import UniqueConstraint

metadata_obj = MetaData()
mytable = Table(
    "mytable",
    metadata_obj,
    # per-column anonymous unique constraint
    Column("col1", Integer, unique=True),
    Column("col2", Integer),
    Column("col3", Integer),
    # explicit/composite unique constraint.  'name' is optional.
    UniqueConstraint("col2", "col3", name="uix_1"),
)

Source