Don't do unique=True with index=True PostgreSQL

[Badsnus]

Describe the bug
When defining a column with both unique=True and index=True in SQLAlchemy, the unique constraint seems to be ignored during migration with Alembic. The resulting table does not have a unique constraint on the column, leading to errors when creating foreign key relationships.

Expected behavior
The column should have a unique constraint, and an index should be created when both unique=True and index=True are specified. PostgreSQL should enforce the uniqueness of the column values.

To Reproduce

# base.py
from datetime import datetime

import sqlalchemy as sa
from sqlalchemy import inspect
from sqlalchemy.orm import declarative_base

Base = declarative_base()


class DataFieldsMixin:
    created_at = sa.Column(sa.DateTime, default=datetime.utcnow)
    updated_at = sa.Column(sa.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    def to_dict(self):
        return {c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs}


class BaseMixin(DataFieldsMixin):
    id = sa.Column(sa.Integer, primary_key=True, unique=True)


# models.py
from sqlalchemy import Column, BigInteger, ForeignKey
from sqlalchemy.orm import relationship
from base import BaseMixin, Base

class User(BaseMixin, Base):
    __tablename__ = 'users'

    telegram_id = Column(BigInteger, unique=True, index=True)
    referral_telegram_id = Column(
        BigInteger,
        ForeignKey('users.telegram_id', ondelete='RESTRICT'),
        nullable=True,
    )
    referral = relationship(
        'User',
        remote_side='User.telegram_id',
        back_populates='referrals',
    )
    referrals = relationship('User', back_populates='referral')

Error
when I do alembic upgrade head:

# Copy error here. Please include the full stack trace.
db-1   | 2024-07-30 09:45:04.534 UTC [49] ERROR:  there is no unique constraint matching given keys for referenced table "users"
db-1   | 2024-07-30 09:45:04.534 UTC [49] STATEMENT:  
db-1   |        CREATE TABLE users (
db-1   |                telegram_id BIGINT, 
db-1   |                referral_telegram_id BIGINT, 
db-1   |                PRIMARY KEY (id), 
db-1   |                FOREIGN KEY(referral_telegram_id) REFERENCES users (telegram_id) ON DELETE RESTRICT, 
db-1   |                UNIQUE (id)
db-1   |        )

Versions

OS: macOs
Python: Python3.11
Alembic: 1.13.2
SQLAlchemy: 2.0.31
Database: PostgreSQL 12.0 on aarch64-unknown-linux-musl, compiled by gcc (Alpine 8.3.0) 8.3.0, 64-bit
DBAPI: psycopg2

[CaselIT]

Hi,

What is the migration file that's run? How was it created?

[Badsnus]

autogeneration
alembic revision --autogenerate -m "squash migrations"

# alembic/env.py
from models.base import Base

target_metadata = Base.metadata

[CaselIT]

can you also copy the code of the generated migration?

[Badsnus]

there is more field, but I guess its ok

"""add multiplier boolean field to user

Revision ID: 4331370d3bc8
Revises: 
Create Date: 2024-07-30 15:25:39.063423

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = '4331370d3bc8'
down_revision: Union[str, None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('users',
    sa.Column('telegram_id', sa.BigInteger(), nullable=True),
    sa.Column('referral_telegram_id', sa.BigInteger(), nullable=True),
    sa.Column('points_count', sa.Integer(), nullable=True),
    sa.Column('points_count_for_referral', sa.Integer(), nullable=True),
    sa.Column('energy', sa.Integer(), nullable=True),
    sa.Column('last_energy_update', sa.DateTime(), nullable=True),
    sa.Column('energy_limit', sa.Integer(), nullable=True),
    sa.Column('energy_recharging_speed', sa.Integer(), nullable=True),
    sa.Column('tap_cost', sa.Integer(), nullable=True),
    sa.Column('is_auto_tap_on', sa.Boolean(), nullable=True),
    sa.Column('last_auto_tap_claim_date', sa.DateTime(), nullable=True),
    sa.Column('multiplier', sa.Boolean(), nullable=True),
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('created_at', sa.DateTime(), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.ForeignKeyConstraint(['referral_telegram_id'], ['users.telegram_id'], ondelete='RESTRICT'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('id')
    )
    op.create_index(op.f('ix_users_telegram_id'), 'users', ['telegram_id'], unique=True)
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f('ix_users_telegram_id'), table_name='users')
    op.drop_table('users')
    # ### end Alembic commands ###

if u want to see FULL user model:

class User(BaseMixin, Base):
    __tablename__ = 'users'

    telegram_id = sa.Column(sa.BigInteger, unique=True, index=True)

    referral_telegram_id = sa.Column(
        sa.BigInteger,
        sa.ForeignKey('users.telegram_id', ondelete='RESTRICT'),
        nullable=True,
    )
    referral = sa.orm.relationship(
        'User',
        remote_side='User.telegram_id',
        back_populates='referrals',
    )
    referrals = sa.orm.relationship('User', back_populates='referral')

    points_count = sa.Column(sa.Integer, default=0)
    points_count_for_referral = sa.Column(sa.Integer, default=0)

    energy = sa.Column(sa.Integer, default=200)
    last_energy_update = sa.Column(sa.DateTime, default=datetime.utcnow)

    energy_limit = sa.Column(sa.Integer, default=200)
    energy_recharging_speed = sa.Column(sa.Integer, default=1)
    tap_cost = sa.Column(sa.Integer, default=1)

    is_auto_tap_on = sa.Column(sa.Boolean, default=False)
    last_auto_tap_claim_date = sa.Column(sa.DateTime, default=datetime.utcnow)

    multiplier = sa.Column(sa.Boolean, default=False)

    user_leagues = relationship('UserLeague', back_populates='user')
    user_missions = sa.orm.relationship('UserMission', back_populates='user')
    user_tasks = sa.orm.relationship('UserTask', back_populates='user')

[CaselIT]

It seems to be working as expected. It's generating an unique index.
If I remember correctly unique indexes and unique constraints are a bit strange, since they are basically the same thing

[Badsnus]

My Postgres is not starting with it)))

I got exception
db-1 | 2024-07-30 09:45:04.534 UTC [49] ERROR: there is no unique constraint matching given keys for referenced table "users"

[CaselIT]

You probably need to move the creating of the fk constraint out of the table creation, using https://alembic.sqlalchemy.org/en/latest/ops.html#alembic.operations.Operations.create_foreign_key

[CaselIT]

I'm not sure if there is an option to force fk to be created after the table. @zzzeek do you remember if there is an option?

[zzzeek]

I looked at the top of the issue to try to understand the problem. I would definitely not do this:

 id = sa.Column(sa.Integer, primary_key=True, unique=True)

primary key cols are unique already. remove unique=True.

then the CREATE TABLE, I dont understand that

CREATE TABLE users (
db-1   |                telegram_id BIGINT, 
db-1   |                referral_telegram_id BIGINT, 
db-1   |                PRIMARY KEY (id), 
db-1   |                FOREIGN KEY(referral_telegram_id) REFERENCES users (telegram_id) ON DELETE RESTRICT, 
db-1   |                UNIQUE (id)
db-1   |        )

where's the "id" column declared? it actually rendered a CREATE TABLE and didn't include the "id" column? that's not what it does on my end, I get the "id":

CREATE TABLE users (
	telegram_id BIGINT, 
	referral_telegram_id BIGINT, 
	id SERIAL NOT NULL, 
	created_at TIMESTAMP WITHOUT TIME ZONE, 
	updated_at TIMESTAMP WITHOUT TIME ZONE, 
	PRIMARY KEY (id), 
	FOREIGN KEY(referral_telegram_id) REFERENCES users (telegram_id) ON DELETE RESTRICT, 
	UNIQUE (id)
)

my own PG gives me a better message, OK it's the telegram_id. You need to add the FK after the fact for that, and no alembic has no automated way to figure out an unusual case like this.

[CaselIT]

ok, maybe I remembered wrongly regarding this setting, or I mixed it up with something else

[nimaxin]

There's no need to manually create indexes on unique columns; doing so would just duplicate the automatically-created index. source

Just remove index=True and alembic will work as expected.