To simplify the problem let’s say that I have a user database with user ID (Unique), Name and Gender and that rather than saving a new entry for gender anytime a user is added (and having no control on the handed input for gender) I want to have a gender table with “Male” “Female” “Other” and when a new user is created it is set to point to one of these 3 options in that table. I tried many different approaches but I always run into IntegrityError. I’m using a postgres db.

Thank you for any help.

class Parent(Base):
    __tablename__ = "parent_table"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    child_id: Mapped[int] = mapped_column(ForeignKey("child_table.id"))
    child: Mapped["Child"] = relationship()


class Child(Base):
    __tablename__ = "child_table"

    id: Mapped[str] = mapped_column(primary_key=True)

with Session(engine) as session:
    spongebob = Parent(
        name="spongebob",
        child=Child(id="male"),
    )
    sandy = Parent(
        name="sandy",
        child=Child(id="male"),
    )

Yields an IntegrityError as the child ID is the primary_key, so then how do I get both spongebob and sandy to point to male and how do I integrate that to a new user being added, in other words when a new user is added I have to pass male to the constructor somehow without it taking male and trying to write it into the child_table.