0

I've defined the following table:

class MatchesBlgScheduled(Base):
    __tablename__ = "matches_blg_scheduled"
    __table_args__ = (
        Index(
            'mbs_composite',
            "tour_id_mbs",
            "tournament_id_mbs",
            "round_id_mbs",
            "p1_id_mbs",
            "p2_id_mbs",
        ), {"schema": BELGARATH, "extend_existing": True}
    )

    match_id_mbs = Column(Integer, primary_key=True)
    tour_id_mbs = Column(Integer, index=True)
    tournament_id_mbs = Column(
        Integer, ForeignKey(f"{BELGARATH}.tournaments_blg.tournament_id_blg")
    )
    round_id_mbs = Column(Integer, index=True)
    p1_id_mbs = Column(
        Integer, ForeignKey(f"{BELGARATH}.players_blg.player_id_blg")
    )
    p2_id_mbs = Column(
        Integer, ForeignKey(f"{BELGARATH}.players_blg.player_id_blg")
    )

And built the following query to update it with records from another table:

    matches = dal.session.query(
        TodayATP.TOUR,
        TodayATP.ROUND,
        TodayATP.ID1,
        TodayATP.ID2,
    )
    for match in matches:
        match_dict = {
            "tour_id_mbs": 0,
            "tournament_id_mbs": match[0],
            "round_id_mbs": match[1],
            "p1_id_mbs": match[2],
            "p2_id_mbs": match[3],
        }
        dal.session.add(MatchesBlgScheduled(**match_dict))
    dal.session.commit()

However, I'm able to run this query multiple times and each time it keeps adding the same records into the table. I didn't think this should be possible as I have established a composite index?

Jossy
  • 589
  • 2
  • 12
  • 36
  • 1
    A composite index is not necessarily a *unique* composite index. – Gord Thompson Aug 09 '20 at 20:58
  • Ah. I got the impression from https://stackoverflow.com/a/10061143/11277108 that it was - there's a comment that seems to bear this out too. ```UniqueConstraint``` it is then! Thanks - happy to accept that as the answer – Jossy Aug 10 '20 at 06:05

0 Answers0