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

[YuriiMotov]

Now Field(unique=True) works thanks to @raphaelgibson.

But I think it would be nice to document the way how to create unique constraints including unique constraints on multiple columns