Flask-SQLAlchemy create multi-column UniqueConstraint with multiple nullable columns-CodePudding

I have a model in my flask project:

class Location(db.Model):
    __tablename__ = 'location'

    id = db.Column(db.Integer, primary_key=True)
    longitude = db.Column(db.Float, nullable=False)
    latitude = db.Column(db.Float, nullable=False)
    address_1 = db.Column(db.String(50))
    address_2 = db.Column(db.String(8))
    country_id = db.Column(db.Integer, db.ForeignKey('country.id', ondelete='SET NULL'))
    city_id = db.Column(db.Integer, db.ForeignKey('city.id', ondelete='SET NULL'))

I need to ensure uniqueness of location via applying unique constraint on all columns except an id. I've tried this one:

__table_args__ = (
        db.UniqueConstraint(
            longitude,
            latitude,
            address_1,
            address_2,
            country_id,
            city_id,
            name="unique_loc"),
    )

... but I still can save duplicates because of nullable `address_1', 'address_2' (in more details). I've investigated a bit and found a possible solution:

CREATE UNIQUE INDEX unique_loc ON location
(longitude, latitude, COALESCE(address_1, ''), COALESCE(address_2, ''), country_id, city_id);

Is it possible to write this constraint either via ORM or raw query? The database I use - postgres 13. Any help would be appreciated.

CodePudding user response：

You can use Index (with unique=True option) and func.coalesce:

   from sqlalchemy import func
   ...   

   __table_args__ = (
        db.Index(
            "unique_loc",
            longitude,
            latitude,
            func.coalesce(address_1, ''),
            func.coalesce(address_2, ''),
            country_id,
            city_id,
            unique=True
        ),
    )

CodePudding user response：

So, I've found out how to create an index with raw query. All you need is generate new migration with flask db migrate and modify both def upgrade() and 'def downgrade`:

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    ...
    op.execute(f"CREATE UNIQUE INDEX unique_loc ON location "
               f"(longitude, latitude, COALESCE(address_1, '-1'), "
               f"COALESCE(address_2, '-1'), country_id, city_id);")
    ...
    # ### end Alembic commands ###

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    ...
    op.drop_index('unique_loc', table_name='location')
    ...
    # ### end Alembic commands ###