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

Question

I have a fastapi 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.

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

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?

Well, you are attempting to insert a new record here with the same private key as the one you want to update instead of just updating the existing one. I don't understand why you are re-instantiating a `Record` instance from the initial query result instead of just modifying that, adding it back to the session and committing. Like [this](https://sqlmodel.tiangolo.com/tutorial/update/#commit-the-session). — Daniil Fajnberg, Apr 14 '23 at 09:27

score 0 · Answer 1 · answered Apr 10 '23 at 15:33

If you want to have code for update in this form I believe that you should first remove old entity and after that add new one (in the same session to protect you from deleting and not adding new one). But there are alternative ways of performing update (please refer to: How to update SQLAlchemy row entry?)

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

1 Answers1