Integrity Error when creating multiple records with Flask SqlAlchemy-CodePudding

I have a function that creates some new DB entries in Flask app with SQL Alchemy

def add_volunteer_client_record(volunteer_id, **kwargs):
    try:
        volunteer_client = VolunteerClient(volunteer_id=volunteer_id, **kwargs)

        volunteer_report_action_items = VolunteerReportActionItems(volunteer_client_id = volunteer_client.id)

        db_session.add(volunteer_client) 
        db_session.add(volunteer_report_action_items)
        db_session.commit()

        return volunteer_client
    except IntegrityError as e:
        db_session.rollback()
        message = "Integrity error occurred"
        raise BadRequestError(messages={'volunteer_client': [message]})

volunteer_client gets created fine but when volunteer_report_action_items is added to the session I received an IntegrityError and I can not quite understand why.

My Models

class VolunteerClient(Base):
    __tablename__ = 'volunteer_client'

    id = Column(Integer, primary_key=True, autoincrement=True, index=True)
    volunteer_id = Column(Integer, ForeignKey('provider_user.user_id', onupdate='CASCADE', ondelete='RESTRICT'), unique=True)
    client_id = Column(Integer, ForeignKey('user.id', onupdate='CASCADE', ondelete='RESTRICT'), unique=True)

class VolunteerReportActionItems(Base):
    __tablename__ = 'volunteer_report_action_items'

    id = Column(Integer, primary_key=True, autoincrement=True, index=True)
    volunteer_client_id = Column(Integer, ForeignKey('volunteer_client.id', onupdate='CASCADE', ondelete='RESTRICT'))

SQL

CREATE TABLE IF NOT EXISTS public.volunteer_client
(
  id serial PRIMARY KEY,
  volunteer_id integer NOT NULL,
  client_id integer NOT NULL,
  created_by text,
  created_at timestamp NOT NULL DEFAULT now(),
  updated_by text,
  updated_at timestamp NOT NULL DEFAULT now(),
  UNIQUE(volunteer_id, client_id),
  CONSTRAINT fk_volunteer_client_volunteer FOREIGN KEY (volunteer_id)
      REFERENCES public.user (id) MATCH SIMPLE
      ON UPDATE CASCADE
      ON DELETE RESTRICT,
  CONSTRAINT fk_volunteer_client_client FOREIGN KEY (client_id)
      REFERENCES public.user (id) MATCH SIMPLE
      ON UPDATE CASCADE
      ON DELETE RESTRICT
);

ALTER TABLE public.volunteer_client OWNER to navigate;

CREATE TABLE IF NOT EXISTS public.volunteer_report_action_items
(
  id serial PRIMARY KEY,
  volunteer_client_id integer NOT NULL,
  created_by text,
  created_at timestamp NOT NULL DEFAULT now(),
  updated_by text,
  updated_at timestamp NOT NULL DEFAULT now(),
  CONSTRAINT fk_volunteer_report_action_items_volunteer_client FOREIGN KEY (volunteer_client_id)
      REFERENCES public.volunteer_client (id) MATCH SIMPLE
      ON UPDATE CASCADE
      ON DELETE RESTRICT
);

ALTER TABLE public.volunteer_report_action_items OWNER to navigate;

Any help or advice here would be great. Thanks!

CodePudding user response：

You need to flush the VolunteerClient to be able to access the VolunteerClient.id

def add_volunteer_client_record(volunteer_id, **kwargs):
    try:
        volunteer_client = VolunteerClient(volunteer_id=volunteer_id, **kwargs)
        db_session.add(volunteer_client)
        db_session.flush() 
        volunteer_report_action_items = VolunteerReportActionItems(volunteer_client_id = volunteer_client.id)
        db_session.add(volunteer_report_action_items)
        db_session.commit()
    ...