SQLAlchemy and mssql : how to create unique constraint with multiple null value

Question

I am working with SQLAlchemy and MS SQL server and I would like to create a unique constraint that allows multiple NULL value.

I know that MS SQL server does not ignore the null value and considers it as violation for the UNIQUE KEY. I also know how to fix it with SQL code (see here)

But is there a way to do the same thing with SQLAlchemy directly ?

Here is my code :

class Referential(db.Model):
     __tablename__ = "REFERENTIAL"
     id = db.Column("ID", Integer, primary_key=True, autoincrement=True)
     name = db.Column("NAME", String(100), index=True, unique=True, nullable=False)
     internal_code = db.Column("INTERNAL_CODE", String(50), unique=True, index=True)

Thanks in advance

Daniel Butler · Answer 1 · 2020-02-17T18:03:41.290

MSSQL's implementation when it comes to allowing nulls in a unique column is a little odd.

import sqlalchemy as sa

sa.Table(
    sa.Column('column', sa.String(50), nullable=True),
    sa.Index('uq_column_allows_nulls', mssql_where=sa.text('column IS NOT NULL'),
)

If you are planning on using alembic like I was this is the code:

import sqlalchemy as sa
import alembic as op

op.create_index(
    name='uq_column_name',
    table_name='table',
    columns=['column'],
    mssql_where=sa.text('column IS NOT NULL'),
)

This uses the sql expression text for sqlalchemy and create_index's dialect_expression key word arguments mssql_where=

SQLAlchemy and mssql : how to create unique constraint with multiple null value

1 Answers1