tiangolo
How to define unique constraint in table columns
First Check
- [X] I added a very descriptive title to this issue.
- [X] I used the GitHub search to find a similar issue and didn't find it.
- [X] I searched the SQLModel documentation, with the integrated search.
- [X] I already searched in Google "How to X in SQLModel" and didn't find any information.
- [X] I already read and followed all the tutorial in the docs and didn't find an answer.
- [X] I already checked if it is not related to SQLModel but to Pydantic.
- [X] I already checked if it is not related to SQLModel but to SQLAlchemy.
Commit to Help
- [X] 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
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))
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
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 :)
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
@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 :)
@sgraaf : Thank you, that works fine! Where is that "trick" documented?
@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.
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
It looks like the Field column now supports the unique=True/False keyword argument? Merged in https://github.com/tiangolo/sqlmodel/pull/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
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 aCREATE 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.
Was it removed? I am running sqlmodel version 0.3.0 and it is saying that it is not there in Field.
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