SQLAlchemy: IntegrityError when trying to fill polymorphic table with duplicate entries

Question

I have the below code where I'm using sqlalchemy to create a databse structure. When a person either changes their profession, location or salary, I would like to fill in the questionnaires table with their new profession, location or salary. Depending on what has changed the type in questionnaires should be either profession, location or salary and the value is the corresponding value.

I also have a table addresses (with addresses and locations, there is a one to one relationship between address and location) and a table professions. Those two tables should not have any duplicates (hence the unique=True) but the questionnaires table can have duplicates.

When I try to fill in some values into these tables I get an IntegrityError when trying to commit location_instance as location2 already exists in the addresses table. This is as I would like to have it. If I try to commit questionnaire_location though I also get an IntegrityError which should not be (as there can and should be duplicates in questionnaires). How can I make this work? Maybe there is also another option than using IntegrityError?

What I would like is to have duplicates in questionnaires but not in addressesand also not in professions.

import dotenv
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.exc import IntegrityError


########### models ###########
Base = declarative_base()

class Profession(Base):
    __tablename__ = "professions"

    id                = Column(Integer, primary_key=True, index=True)
    profession        = Column(String, nullable=True, index=True, unique=True)

    def __repr__(self):
        return f"""<Profession(
          profession={self.profession}
          )>"""
    
class Address(Base):
    __tablename__ = "addresses"

    id        = Column(Integer, primary_key=True, index=True)
    address     = Column(String, nullable=True, index=True, unique=True)
    location  = Column(String, nullable=True, index=True, unique=True)

    def __repr__(self):
        return f"""<Address(
          address={self.address}, 
          location={self.location})>"""
        
class Questionnaire(Base):
    __tablename__ = "questionnaires"

    id                  = Column(Integer, primary_key=True, index=True)
    current_address          = Column(Integer, ForeignKey("addresses.id"),  nullable=False, index=True)
    type = Column(String, nullable=False)  #'location', 'profession', 'salary'
    value = Column(String, nullable=False)  #value corresponding to the type

    __mapper_args__ = {
        'polymorphic_identity': 'questionnaire',
        'polymorphic_on': type
    }

    def __repr__(self):
        return f"""<Questionnaire(
          current_address={self.current_address}, 
          type={self.type}, 
          value={self.value})>"""
    
class ProfessionQuestionnaire(Questionnaire):
    __mapper_args__ = {'polymorphic_identity': 'profession'}

    profession_id = Column(Integer, ForeignKey('professions.id'))

    profession = relationship("Profession")

class LocationQuestionnaire(Questionnaire):
    __mapper_args__ = {'polymorphic_identity': 'location'}

    location_id = Column(Integer, ForeignKey('addresses.id'))

    location = relationship("Address", foreign_keys=[location_id])



########### create empty tables ###########
dotenv.load_dotenv()
SQLALCHEMY_DATABASE_URL = "sqlite:///./dwh/database.db"

engine = create_engine(
    SQLALCHEMY_DATABASE_URL, 
    connect_args={"check_same_thread": False},
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base.metadata.create_all(bind=engine) 
db = SessionLocal()  



########### fill tables ###########
###professions table
for profession in ['teacher', 'actor', 'waiter', 'home maker']:
    to_add = Profession(profession=profession)
    db.add(to_add)
    try:
        db.commit()
    except IntegrityError as e:
        db.rollback()
        print(f"IntegrityError: {e}")    

###addresses table
for address, location in zip(['address1', 'address2', 'address3', 'address4'], ['location1', 'location2', 'location3', 'location4']):
    to_add = Address(address=address, location=location)
    db.add(to_add)
    try:
        db.commit()
    except IntegrityError as e:
        db.rollback()
        print(f"IntegrityError: {e}")    

###questionnaires table
#profession
profession = 'real estate agent'
profession_instance = Profession(profession=profession)
questionnaire_profession = ProfessionQuestionnaire(current_address='address1', value=profession, profession=profession_instance)

#location
location = 'location2'
location_instance = Address(location=location)
questionnaire_location = LocationQuestionnaire(current_address='address1', value=location, location=location_instance)

#salary
questionnaire_salary = Questionnaire(current_address='address1', type='salary', value='200')

for to_add in [profession_instance, location_instance, questionnaire_profession, questionnaire_location, questionnaire_salary]:
    db.add(to_add) 
    try:
        db.commit()
    except IntegrityError as e:
        db.rollback()
        print(f"IntegrityError: {e}")