SQLAlchemy 2.0 composite unique constraints and indexes

<p>I need to enforce a composite unique constraint across multiple columns in SQLAlchemy (e.g., one vote per user per trace, unique domain reputation per user per tag). I also need a partial unique index for conditional uniqueness.</p>

<p>Define constraints at the table level using <code>__table_args__</code>:</p> <div class="highlight"><pre><span></span><code><span class="kn">from</span><span class="w"> </span><span class="nn">sqlalchemy</span><span class="w"> </span><span class="kn">import</span> <span class="n">UniqueConstraint</span><span class="p">,</span> <span class="n">Index</span><span class="p">,</span> <span class="n">CheckConstraint</span> <span class="kn">from</span><span class="w"> </span><span class="nn">sqlalchemy.orm</span><span class="w"> </span><span class="kn">import</span> <span class="n">Mapped</span><span class="p">,</span> <span class="n">mapped_column</span> <span class="k">class</span><span class="w"> </span><span class="nc">Vote</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span> <span class="n">__tablename__</span> <span class="o">=</span> <span class="s1">'votes'</span> <span class="n">__table_args__</span> <span class="o">=</span> <span class="p">(</span> <span class="c1"># One vote per user per trace:</span> <span class="n">UniqueConstraint</span><span class="p">(</span><span class="s1">'voter_id'</span><span class="p">,</span> <span class="s1">'trace_id'</span><span class="p">,</span> <span class="n">name</span><span class="o">=</span><span class="s1">'uq_votes_voter_trace'</span><span class="p">),</span> <span class="c1"># Index for fast lookup:</span> <span class="n">Index</span><span class="p">(</span><span class="s1">'ix_votes_trace_id'</span><span class="p">,</span> <span class="s1">'trace_id'</span><span class="p">),</span> <span class="c1"># Partial index — only for confirmed votes:</span> <span class="n">Index</span><span class="p">(</span> <span class="s1">'ix_votes_confirmed'</span><span class="p">,</span> <span class="s1">'trace_id'</span><span class="p">,</span> <span class="n">postgresql_where</span><span class="o">=</span><span class="s1">'vote_type = </span><span class="se">\'</span><span class="s1">confirmed</span><span class="se">\'</span><span class="s1">'</span><span class="p">,</span> <span class="p">),</span> <span class="p">)</span> <span class="nb">id</span><span class="p">:</span> <span class="n">Mapped</span><span class="p">[</span><span class="n">uuid</span><span class="o">.</span><span class="n">UUID</span><span class="p">]</span> <span class="o">=</span> <span class="n">mapped_column</span><span class="p">(</span><span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">,</span> <span class="n">default</span><span class="o">=</span><span class="n">uuid</span><span class="o">.</span><span class="n">uuid4</span><span class="p">)</span> <span class="n">voter_id</span><span class="p">:</span> <span class="n">Mapped</span><span class="p">[</span><span class="n">uuid</span><span class="o">.</span><span class="n">UUID</span><span class="p">]</span> <span class="o">=</span> <span class="n">mapped_column</span><span class="p">(</span><span class="n">ForeignKey</span><span class="p">(</span><span class="s1">'users.id'</span><span class="p">))</span> <span class="n">trace_id</span><span class="p">:</span> <span class="n">Mapped</span><span class="p">[</span><span class="n">uuid</span><span class="o">.</span><span class="n">UUID</span><span class="p">]</span> <span class="o">=</span> <span class="n">mapped_column</span><span class="p">(</span><span class="n">ForeignKey</span><span class="p">(</span><span class="s1">'traces.id'</span><span class="p">))</span> <span class="n">vote_type</span><span class="p">:</span> <span class="n">Mapped</span><span class="p">[</span><span class="nb">str</span><span class="p">]</span> <span class="o">=</span> <span class="n">mapped_column</span><span class="p">(</span><span class="n">String</span><span class="p">(</span><span class="mi">20</span><span class="p">))</span> <span class="c1"># Handling IntegrityError for duplicate votes:</span> <span class="kn">from</span><span class="w"> </span><span class="nn">sqlalchemy.exc</span><span class="w"> </span><span class="kn">import</span> <span class="n">IntegrityError</span> <span class="k">try</span><span class="p">:</span> <span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">vote</span><span class="p">)</span> <span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">commit</span><span class="p">()</span> <span class="k">except</span> <span class="n">IntegrityError</span> <span class="k">as</span> <span class="n">e</span><span class="p">:</span> <span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">rollback</span><span class="p">()</span> <span class="k">if</span> <span class="s1">'uq_votes_voter_trace'</span> <span class="ow">in</span> <span class="nb">str</span><span class="p">(</span><span class="n">e</span><span class="o">.</span><span class="n">orig</span><span class="p">):</span> <span class="k">raise</span> <span class="n">HTTPException</span><span class="p">(</span><span class="mi">409</span><span class="p">,</span> <span class="s1">'You have already voted on this trace'</span><span class="p">)</span> <span class="k">raise</span> </code></pre></div> <p>Key points: - <code>__table_args__</code> must be a tuple — add a trailing comma if only one item - Name constraints explicitly (<code>name=...</code>) for meaningful error messages - <code>postgresql_where=</code> creates partial indexes (PostgreSQL-specific) - Catch <code>IntegrityError</code> by constraint name to distinguish different violations</p>