"UniqueViolation, duplicate key value violates unique constraint" on update

[saeedesmaili]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question 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 pgvector.sqlalchemy import Vector
from sqlalchemy import text

## model
class Record(SQLModel, table=True):
    id: UUID = Field(default_factory=uuid4, primary_key=True)
    text: str = Field(default=None)
    start: int = Field(default=None)
    vector: List[float] = Field(default=None, sa_column=Vector(1536))
    parent_id: UUID = Field(default=None, foreign_key="parent.id")


## controller
def update_record(session: Session, id: UUID, record: RecordUpdate):
    query = text("SELECT * FROM record WHERE id = :id")
    result = session.execute(query, {"id": id}).fetchone()
    if not result:
        return None

    db_record = Record.from_orm(result)
    if not db_record:
        return None

    for key, value in record.dict().items():
        if hasattr(db_record, key) and value is not None:
            setattr(db_record, key, value)
    session.add(db_record)
    session.commit()
    session.refresh(db_record)
    return db_record

Description

I have an edpoint for updating records in the database, but it doesn't seem to work as intended and I get the following error when I call the endpoint to update a record:

  File "/usr/src/app/app/api/controllers/records.py", line 137, in update_record_endpoint
    updated_record = update_record(session, id, record)
                         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/src/app/app/api/controllers/records.py", line 61, in update_record
    session.commit()
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 1431, in commit
    self._transaction.commit(_to_root=self.future)
... rest of the error ...
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1498, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "record_pkey"
DETAIL:  Key (id)=(bc2a4eeb-a8f0-4a6b-a976-91fb630b281b) already exists.

In another controller I'm using session.get(Parent, id) and the updating process works fine, but for this specific controller I'm using session.execute(text("query")) because of this issue and update doesn't work (violates unique constraint ). How can I fix this issue?

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.8

Python Version

3.9.13

Additional Context

No response

[nick-catalist]

I'm having the exact same problem right now. For me, it's only happening when I change things in the model's relationship. This is definitely buggy behavior, as this should not cause the model to be seen as new.

stmt = select(MyModel).where(MyModel.indexed_column == query_value)
existing = self.db.execute(stmt).scalar_one_or_none()
existing.relationship_to_things = [thing1, thing2, thing3]
self.db.commit()

gives unique key constraint violation on the primary key of existing. I seem to be able to delete from the relationship, but if I add any values at all, it gives a unique violation.

I've tried calling db.merge(existing) first as well, to no avail.

[nick-catalist]

See above, at least for me, this happens when I try to replace or add to a relationship for my existing row. To get around this, I did the following:

for hero in existing_team.heroes:
    session.delete(hero)

session.flush()
for hero in new_heroes:
    db_hero = Hero(
        name = hero.name,
        # etc.
    )
    session.add(db_hero)
session.commit()

[wangxin688]

I guest you should not use Record.from_orm which will return a pydantic model.

[simonryf]

I stumbled upon this thread and I have solved a similar issue with merge(). I'm not sure if this is really the same problem but I guess it doesn't hurt to mention it here. Note that merge() comes with its own drawbacks as written below under "Merge Tips".

basically replace add() with merge().
See: https://docs.sqlalchemy.org/en/20/orm/session_state_management.html#merging

[nick-catalist]

Could you give an example of how you're using merge? Specifically in the case that I shared above, merge did nothing to resolve the issue for me.