CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
binarysolo
binarysolo

Reputation: 355

SQLAlchemy many:many relationship, setting uniqueness of a table entry

I'm using SQLAlchemy with Postgres and declarative base and have something along the lines of this many-to-many relationship:

user_trait_association = Table('user_trait', Base.metadata,
    Column('user_id', Integer, ForeignKey('user.id')),
    Column('trait_id', Integer, ForeignKey('trait.id'))
)

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    trait = relationship('SomeTrait', backref='user')

class SomeTrait(Base):
    __tablename__ = 'sometrait'
    id = Column(Integer, primary_key=True)
    detail1 = Column(String)
    detail2 = Column(String)

My question: how do I go about setting it so that such that the traits are unique -- that is, say detail1 = eye color, detail2 = hair color... how do I write it such that Bob and Jim who both have black hair and brown eyes (combined as one unique trait of 2 details - say, Asian). Right now when I save to the database it generates many traits of the same details -- the table has many separate Asian(black hair, brown eye) entries, when what I want is to have everything lumped under one trait.

I'm not sure if I'm describing this clearly; let me know if I need to rephrase.

Upvotes: 0

Views: 83

Answers (1)

Audrius Kažukauskas
Audrius Kažukauskas

Reputation: 13543

If I understand you correctly, you want a UNIQUE constraint on a group of columns? If so, then something like this should do the trick:

class SomeTrait(Base):
    __tablename__ = 'sometrait'
    id = Column(Integer, primary_key=True)
    detail1 = Column(String)
    detail2 = Column(String)
    __table_args__ = (
        UniqueConstraint('detail1', 'detail2'),
    )

Upvotes: 2

Related Questions