0

I'm trying to create 2 tables using SQLAlchemy models that are identical in structure but distinct in purpose, the Whitelist and Blacklist which also reference a Magazine (not shown). They both fkey to an instance of a Campaign (say a political campaign) which in turn has an fkey to a Politician model (also not shown).

I'm picturing the Whitelist/Blacklist to be many:1 to the Campaign since there could be multiple white/blacklists for, but when running the migration I get sqlalchemy.exc.ProgrammingError: (ProgrammingError) there is no unique constraint matching given keys for referenced table "campaigns". There also needs to be a M:1 from Campaign to Politician.

Can someone please explain why this is causing a unique constraint error since the whitelist and blacklist are on separate tables? Also, how would I be able to make this relational schema work?

class Campaign(Base):
    __tablename__ = "campaigns"
    id = Column(Integer, primary_key=True, nullable=False)
    politician_id = Column(Integer, ForeignKey('politician.id'), nullable=False)
    description = Column(Text, nullable=True)


class Whitelist(Base):
    __tablename__ = "whitelist"
    id = Column(Integer, primary_key=True, nullable=False)
    campaign_id = Column(Integer, ForeignKey('campaign.id'), nullable=False)
    magazine_id = Column(Integer, ForeignKey('magazine.id'), nullable=False)


class Blacklist(Base):
    __tablename__ = "blacklist"
    id = Column(Integer, primary_key=True, nullable=False)
    campaign_id = Column(Integer, ForeignKey('campaign.id'), nullable=False)
    magazine_id = Column(Integer, ForeignKey('magazine.id'), nullable=False)
Chrispy
  • 1,300
  • 3
  • 11
  • 25

1 Answers1

0

It would seem that the relationship is indeed illegal because of the M:1 from the Whitelist/Blacklist to Campaign, while Campaign itself is a M:1 to Politician. I instead scrapped that approach and the models now look like:

class Campaign(Base):
    __tablename__ = "campaigns"
    id = Column(Integer, primary_key=True, nullable=False)
    politician_id = Column(Integer, ForeignKey('politician.id'), nullable=False)
    description = Column(Text, nullable=True)


class Listing(Base):
    __tablename__ = "listings"
    id = Column(Integer, primary_key=True, nullable=False)
    campaign_id = Column(Integer, ForeignKey('campaign.id'), nullable=False)
    magazine_id = Column(Integer, ForeignKey('magazine.id'), nullable=False)
    listing_type = Column(String, nullable=False)

The original approach was intended to fit a little nicer into Flask-Admin but this way is more performant since it will reduce the additional query and join necessary in the previous incarnation. For those that may be thinking of this type of approach to use with Flask-Admin, you could use the inline_model() to make the UI a little cleaner and easier to manage.

Chrispy
  • 1,300
  • 3
  • 11
  • 25