Repository navigation
How to define unique constraint in table columns #82
Description
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
Activity
obassett commented on Sep 7, 2021
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 commented on Sep 8, 2021
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 commented on Sep 19, 2021
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
The file: product.py with the definition of Product is
Of course the relationship with ProductType and ProductTagLink are defined in another files and schemas :)
Hope this example helps :)
StefnirKristjansson commented on Nov 25, 2021
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: strsgraaf commented on Jan 5, 2022
Why not simply add sa_column_kwargs={"unique": True} to Field()?
shifqu commented on Feb 4, 2022
@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 commented on Apr 24, 2022
@sgraaf : Thank you, that works fine! Where is that "trick" documented?
sgraaf commented on Apr 24, 2022
@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 commented on Apr 26, 2022
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 commented on Jan 28, 2023
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 commented on Jan 28, 2023
quillan86 commented on Oct 3, 2023
raphaelgibson commented on Oct 3, 2023
wamiqraza commented on May 6, 2024
raffaelemancuso commented on Jun 2, 2025
YuriiMotov commented on Aug 14, 2025
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
📝 Add documentation for unique and check constraints