Generatate composite unique constraints

[cacomop]

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

from typing import Optional
import datetime
from sqlmodel import SQLModel, Field

class User(SQLModel):
    id: int = Field(primary_key=True)
    name: str = Field(unique=True)
    lastname: str = Field(unique=True)

Description

• create a User model
• create a unique constraint on (name, latname)

Operating System

Windows

Operating System Details

No response

SQLModel Version

0.0.3

Python Version

3.8.6

Additional Context

No response

[tadejsv]

Do this

from typing import Optional
import datetime
from sqlalchemy import UniqueConstraint
from sqlmodel import SQLModel, Field

class User(SQLModel):
    __table_args__ = (UniqueConstraint("name", "lastname"), )

    id: int = Field(primary_key=True)
    name: str = Field(unique=True)
    lastname: str = Field(unique=True)

UPD YuriiMotov: you probably don't need Field(unique=True) anymore. And don't forget teble=True:

from typing import Optional
import datetime
from sqlalchemy import UniqueConstraint
from sqlmodel import SQLModel, Field

class User(SQLModel, table=True):
    __table_args__ = (UniqueConstraint("name", "lastname"), )

    id: int = Field(primary_key=True)
    name: str
    lastname: str

[cacomop]

it works! thank you a lot!

where did you find the solution?

[tadejsv]

Basically, the SQLModel can be treated as a subclass of SQLAlchemy's declarative Base, so everything that works there should work here as well.

Specifically, for __table_args__, see here: https://docs.sqlalchemy.org/en/14/orm/declarative_tables.html#orm-declarative-table-configuration

[Trophime]

It seems to be no longer working with 0.0.4 version:

Traceback (most recent call last):
  File "unique.py", line 6, in <module>
    class User(SQLModel):
  File "unique.py", line 10, in User
    name: str = Field(unique=True)
TypeError: Field() got an unexpected keyword argument 'unique'

@tadejsv can you confirm or not, please?

[northtree]

It seems to be no longer working with 0.0.4 version:

Traceback (most recent call last):
  File "unique.py", line 6, in <module>
    class User(SQLModel):
  File "unique.py", line 10, in User
    name: str = Field(unique=True)
TypeError: Field() got an unexpected keyword argument 'unique'

@tadejsv can you confirm or not, please?

@Trophime This one works for 0.0.4.

    name: str = Field(sa_column_kwargs={"unique": True})
    lastname: str = Field(sa_column_kwargs={"unique": True})

[DanLipsitt]

@northtree I think this would result in two independent constraints rather than a single composite constraint. In other words, it would only allow one record with a given first or last name rather than a given first and last name.

[northtree]

@northtree I think this would result in two independent constraints rather than a single composite constraint. In other words, it would only allow one record with a given first or last name rather than a given first and last name.

If only one single composite unique constraint needed, you just have to define via __table_args

from typing import Optional
import datetime
from sqlalchemy import UniqueConstraint
from sqlmodel import SQLModel, Field

class User(SQLModel):
    __table_args__ = (UniqueConstraint("name", "lastname"), )

    id: int = Field(primary_key=True)
    name: str
    lastname: str

[productdevbook]

[clstaudt]

Whatever the reason for the change may be, I think that Field(unique=True) is far more intuitive, readable and pythonic than Field(sa_column_kwargs={"unique": True}).