NotNullViolationError: Enable foreign key to be None while saving object to DB

Question

Having two objects PLaylist and PlaylistEmbedding both use DB to setup their ID and the embedding is part of playlist

class Playlist(SQLModel, table=True):
    playlist_id: int | None = Field(default=None, primary_key=True)
    embedding_id: int = Field(foreign_key="playlistembedding.embedding_id")

class PlaylistEmbedding(SQLModel, table=True):
    embedding_id: int | None = Field(default=None, primary_key=True)
    playlist_id: int = Field(foreign_key="playlist.playlist_id")
    embedding: list[float] = Field(sa_column=Column(Vector()))

I wanted to save both objects to the DB:

    async with get_async_session() as session:
        session.add(playlist)
        await session.flush()
        assert playlist and playlist.playlist_id

        playlist.embedding_id = await get_embedding_for_playlist(playlist)
        await session.merge(playlist)

        await session.commit()

But keep getting error:

sqlalchemy.exc.IntegrityError: (sqlalchemy.dialects.postgresql.asyncpg.IntegrityError) <class 'asyncpg.exceptions.NotNullViolationError'>: null value in column "embedding_id" of relation "playlist" violates not-null constraint

• How to save those two objects in one-to-one relation with their IDs defined by DB?
• What to do to save first Playlist object -> get its ID from DB -> save PlaylistEmbedding object with the returned from db ID of playlist_id-> update the playlist object with the ID of saved PlaylistEmbedding object?

I tried replacing flush with commit but no change

Answer 1

1. You don't need foreign keys on both sides
2. Create relationship playlist_embedding: "PlaylistEmbedding" = Relationship() to have the ability to insert both linked objects in one time
3. When you create objects, link them using relationship field (playlist_embedding)

from typing import Optional
from pgvector.sqlalchemy import Vector
from sqlmodel import SQLModel, Field, Column, Relationship
from sqlalchemy import create_engine, exc
from sqlalchemy.orm import sessionmaker


class Playlist(SQLModel, table=True):
    playlist_id: int | None = Field(default=None, primary_key=True)
    embedding_id: int = Field(default=None, foreign_key="playlistembedding.embedding_id")

    playlist_embedding: "PlaylistEmbedding" = Relationship()


class PlaylistEmbedding(SQLModel, table=True):
    embedding_id: int | None = Field(default=None, primary_key=True)
    embedding: list[float] = Field(sa_column=Column(Vector()))


engine = create_engine("sqlite://")
SQLModel.metadata.create_all(engine)
session_maker = sessionmaker(bind=engine)


with session_maker() as session:

    # Successful case
    emb = PlaylistEmbedding(embedding=[0.1])
    pl = Playlist(playlist_embedding=emb)

    session.add(pl)
    session.commit()
    session.refresh(pl)

    print(f"{pl.playlist_id=}, {pl.embedding_id=}")


    # And it will still get exception if you don't specify embedding_id field
    emb = PlaylistEmbedding(embedding=[0.1])
    pl = Playlist()
    session.add(pl)
    try:
        session.commit()
    except exc.IntegrityError as e:
        print("`embedding_id` is still required field")

Output:

pl.playlist_id=1, pl.embedding_id=1
`embedding_id` is still required field