How to define unique constraint in table columns about sqlmodel HOT 14 OPEN

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

from sqlmodel.

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

from sqlmodel.

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

from sqlmodel.

@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.

from sqlmodel.

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))

from sqlmodel.

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 :)

from sqlmodel.

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

from sqlmodel.

@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 :)

from sqlmodel.

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

from sqlmodel.

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

from sqlmodel.

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.

from sqlmodel.

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

from sqlmodel.

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

from sqlmodel.

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

from sqlmodel.