SQLAlchemy Unique Relationship
<p>I am creating a couple of SQLAlchemy tables like this:</p>

<pre class="lang-py prettyprint-override"><code>association_table = Table(
    'association', Base.metadata,
    Column('snp_id', Integer, ForeignKey('snps.id')),
    Column('pheno_id', Integer, ForeignKey('phenos.id'))
)

class SNP(Base):
    __tablename__ = 'snps'

    id = Column(Integer, primary_key=True)
    name = Column(String, unique=True)
    phenotypes = relationship("Pheno",
                            secondary=association_table,
                            back_populates="snps")

    def __init__(self, name, phenos):
        self.name = name
        self.phenos = [Pheno(i) for i in line[13].split(';')]

class Pheno(Base):
    __tablename__ = 'phenos'

    id       = Column(Integer, primary_key=True, autoincrement=True,
                    index=True)
    category = Column(String, index=True, unique=True)
    snps     = relationship("SNP",
                            secondary=association_table,
                            back_populates="phenotypes")

    def __init__(self, category):
        self.category = category
</code></pre>

<p>This is a many-to-many relationship, millions of SNPs will have the same pheno categories, and they can each have multiple pheno categories. I want the pheno categories to be unique, but I want to have a full association between the two tables. I am sure this is simple, but I am struggling with a good way to do it, other than tracking all of the Pheno objects in a dictionary and linking them manually that way.</p>

<p>To build this database I am just iterating over a tab delimited file that is many millions of lines long, so I want to do this the most efficient way possible.</p>

<p>Obviously, just trying to run something like the above code fails with this error:</p>

<p><code>sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: pheno.category [SQL: 'INSERT INTO pheno (category) VALUES (?)'] [parameters: ('Arthritis',)]</code></p>