sql server - Alembic MSSQL Unique Constraint Allow Nulls

Question

Problem to Solve

Using MSSQL I'd like to have a column that is unique and accepts nulls.

Issues

1. Add two rows of data into a column that allows nulls with the unique constraint like in the implementation below gives the following error:

   Violation of UNIQUE KEY constraint 'UQ_...'. Cannot insert duplicate key in 
   object 'TABLE'. The duplicate key value is (<NULL>). (2627) (SQLExecDirectW)"
   

2. Downgrading the column causes constraint issues tied to the reference column. The constraint is automatically uniquely named so its a pain to programmatically remove.

Current Implementation

The alembic operation is:

from alembic import op
import sqlalchemy as sa

#...

def upgrade():
    op.add_column(
        'TABLE', sa.Column('reference', sa.Integer(), nullable=True, unique=True),
    )


def downgrade():
    op.drop_column('TABLE', 'reference')

question from:https://stackoverflow.com/questions/66051383/alembic-mssql-unique-constraint-allow-nulls

Answer 1

The solution to allowing nulls in a unique field is:

1. Don't create the unique constraint when defining the column

op.add_column(
        'TABLE', sa.Column('reference', sa.Integer(), nullable=True), # No unique=True
    )

2. Create a unique index manually

op.create_index(
    'uq_reference_allow_nulls', table_name='TABLE', columns=['reference'],
    mssql_where=sa.text('reference IS NOT NULL'), unique=True,
)

3. Remove the index when downgrading.

op.drop_index('uq_reference_allow_nulls', table_name='TABLE')

This also solves the problem of having a randomized unique constraint on the table because the unique parameter is removed. All together the alembic revision looks like this:

from alembic import op
import sqlalchemy as sa

#...

def upgrade():
    op.add_column(
        'TABLE', sa.Column('reference', sa.Integer(), nullable=True),  # Do not include unique here
    )
    op.create_index(
        'uq_reference_allow_nulls', table_name='TABLE', columns=['reference'],
        mssql_where=sa.text('reference IS NOT NULL'), unique=True,
    )


def downgrade():
    op.drop_index('uq_reference_allow_nulls', table_name='TABLE')
    op.drop_column('TABLE', 'reference')