How can I create unique constraint on table that is used in Many-to-Many Through?

[nikelborm]

I am trying to use such code:

class Ticker(ormar.Model):
  class Meta(BaseMeta):
    tablename = "ticker"
  id: int = ormar.Integer(name="ticker_id", nullable=False, primary_key=True, autoincrement=True)

class TickerToTickerCollection(ormar.Model):
  class Meta(BaseMeta):
    tablename = "ticker_to_ticker_collection"
    constraints = [ormar.UniqueColumns("ticker_id", "ticker_collection_id", name='uc_ticker_to_ticker_collection_pair')]

class TickerCollection(ormar.Model):
  class Meta(BaseMeta):
    tablename = "ticker_collection"

  id: int = ormar.Integer(name="ticker_collection_id", nullable=False, primary_key=True, autoincrement=True)
  tickers: Optional[List[Ticker]] = ormar.ManyToMany(
    to=Ticker,
    through=TickerToTickerCollection,
    related_name="tickerCollections",
    through_relation_name="ticker_collection_id",
    through_reverse_relation_name="ticker_id",
  )

It fails because of ormar.UniqueColumns constraint I am trying to apply.
If I remove it, the error disappears.
But it is a necessary requirement for this constraint to exist.
It is typical to many-to-many relation.

I tried many times with different variations, tried to use some hacks, but nothing helps me. It always throws either this error:

Traceback (most recent call last):
  File "/home/nikel/.local/bin/alembic", line 8, in <module>
    sys.exit(main())
  File "/home/nikel/.local/lib/python3.10/site-packages/alembic/config.py", line 590, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/home/nikel/.local/lib/python3.10/site-packages/alembic/config.py", line 584, in main
    self.run_cmd(cfg, options)
  File "/home/nikel/.local/lib/python3.10/site-packages/alembic/config.py", line 561, in run_cmd
    fn(
  File "/home/nikel/.local/lib/python3.10/site-packages/alembic/command.py", line 229, in revision
    script_directory.run_env()
  File "/home/nikel/.local/lib/python3.10/site-packages/alembic/script/base.py", line 569, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/home/nikel/.local/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 94, in load_python_file
    module = load_module_py(module_id, path)
  File "/home/nikel/.local/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 110, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
  File "<frozen importlib._bootstrap_external>", line 883, in exec_module
  File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
  File "/home/nikel/tda-dashboards/database_experiment/alembic/env.py", line 35, in <module>
    from database_experiment.models.models import metadata
  File "/home/nikel/tda-dashboards/database_experiment/alembic/../../database_experiment/models/models.py", line 136, in <module>
    class TickerToTickerCollection(ormar.Model):
  File "/usr/local/lib/python3.10/dist-packages/ormar/models/metaclass.py", line 609, in __new__
    populate_meta_sqlalchemy_table_if_required(new_model.Meta)
  File "/usr/local/lib/python3.10/dist-packages/ormar/models/helpers/sqlalchemy.py", line 294, in populate_meta_sqlalchemy_table_if_required
    table = sqlalchemy.Table(
  File "<string>", line 2, in __new__
  File "/home/nikel/.local/lib/python3.10/site-packages/sqlalchemy/util/deprecations.py", line 309, in warned
    return fn(*args, **kwargs)
  File "/home/nikel/.local/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 615, in __new__
    with util.safe_reraise():
  File "/home/nikel/.local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/home/nikel/.local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/home/nikel/.local/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 611, in __new__
    table._init(name, metadata, *args, **kw)
  File "/home/nikel/.local/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 697, in _init
    self._init_items(
  File "/home/nikel/.local/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 144, in _init_items
    spwd(self, **kw)
  File "/home/nikel/.local/lib/python3.10/site-packages/sqlalchemy/sql/base.py", line 1047, in _set_parent_with_dispatch
    self._set_parent(parent, **kw)
  File "/home/nikel/.local/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 3462, in _set_parent
    ColumnCollectionMixin._set_parent(self, table)
  File "/home/nikel/.local/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 3419, in _set_parent
    for col in self._col_expressions(table):
  File "/home/nikel/.local/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 3413, in _col_expressions
    return [
  File "/home/nikel/.local/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 3414, in <listcomp>
    table.c[col] if isinstance(col, util.string_types) else col
  File "/home/nikel/.local/lib/python3.10/site-packages/sqlalchemy/sql/base.py", line 1214, in __getitem__
    return self._index[key]
KeyError: 'ticker_id'

or similar ones.
I tried to pass constraints array to ormar.ManyToMany
I tried to use forwardref in different combinations
I tried to use original unique constraint from sqlalchemy
I tried to add these 2 columns from constraint to Meta of TickerToTickerCollection
and few others

It does not work

[teonat]

I believe this is relevant for an issue I've recently run into as well.
Because there is no unique constraint on through-models for many-to-many fields, Ormar allows us to add duplicate entries for a many-to-many field.

This, however, creates problems down the line, like the following example:

from typing import Optional

import ormar
import sqlalchemy

from app.core.db import database


class BaseMeta(ormar.ModelMeta):
    database = database
    metadata = sqlalchemy.MetaData()


class AppUser(ormar.Model):
    class Meta(BaseMeta):
        tablename = "app_users"

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=256)


class AppGroup(ormar.Model):
    class Meta(BaseMeta):
        tablename = "app_groups"

    id: int = ormar.Integer(primary_key=True)
    name: str = ormar.String(max_length=256)
    description: Optional[str] = ormar.String(max_length=256, nullable=True)
    members: Optional[list[AppUser]] = ormar.ManyToMany(AppUser, related_name='member_users')

If I create a user and a group, and add this user twice to the members-field of the AppGroup-model, this works just fine.
However, if i try to remove this user again, I get ormar.exceptions.MultipleMatches

import pytest

from app.core.db import database
from app.models.access import AppUser, AppGroup


@pytest.mark.asyncio
async def test_db():
    async with database:
        app_user = await AppUser.objects.create(name='User added twice to the same group')

        app_group = await AppGroup.objects.create(name='Group with default through model')
        await app_group.members.add(app_user)
        await app_group.members.add(app_user)
        await app_group.members.remove(app_user)

I use Alembic for handling migrations, and the default migrations file for the above looks something like this:

"""Add app-access

Revision ID: de9f6a3b6363
Revises: 3a81a18d3296
Create Date: 2022-11-07 09:22:47.615231

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'de9f6a3b6363'
down_revision = '3a81a18d3296'
branch_labels = None
depends_on = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('app_groups',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=256), nullable=False),
    sa.Column('description', sa.String(length=256), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('app_users',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=256), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('appgroups_appusers',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('appuser', sa.Integer(), nullable=True),
    sa.Column('appgroup', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['appgroup'], ['app_groups.id'], name='fk_appgroups_appusers_app_groups_appgroup_id', onupdate='CASCADE', ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['appuser'], ['app_users.id'], name='fk_appgroups_appusers_app_users_appuser_id', onupdate='CASCADE', ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id'),
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('appgroups_appusers')
    op.drop_table('app_users')
    op.drop_table('app_groups')
    # ### end Alembic commands ###

In the migration file I can add a unique constraint to the autogenerated through-table appgroups_appusers manually, like sa.UniqueConstraint('appuser', 'appgroup', name='uc_app_user_group'), and this works.
I will now get the error asyncpg.exceptions.UniqueViolationError: duplicate key value violates unique constraint "uc_app_user_group" when attempting to add the same user twice.
I feel like maybe this is something that should be handled by Ormar by default though? Especially since, like in my example above, I can't easily remove duplicates when first added to a relation.