Implementing Unique if not Null Constraint with SQLAlchemy and MS SQL Server

Question

I want to enforce a unique constraint on a column but only if it is not null. This is the default behavior in SQLite, but not in MS SQL Server.

It seems the way to accomplish this in SQL Server is to use an index with a where clause, as described in this post. My question is how do I do that in SQLAlchemy and have it still work with SQLite.

I think this is approximately what I want:

class MyClass(Base):
    __tablename__ = 'myclass'
    __table_args__ = (Index('unique_index', <where clause?>, unique=True)
    my_column = Column(Integer) # The column I want unique if not null

where <where clause?> is some expression that puts the index on my_column where it is not NULL. It seems this is possible by reading the documentation for index, but I do not know what expression I would need. Help with this or a different approach would be much appreciated.

Currently `sqlalchemy` does not support this out of the box. But i think it is worth creating a [new issue](https://bitbucket.org/zzzeek/sqlalchemy/issues/new) on sqlalchemy bitbucket repository, and even better implementing it. I think it should be implemented similar to other RDBMS-specific parameters such as [`mssql_include` and `mssql_clustered`](http://docs.sqlalchemy.org/en/rel_1_0/dialects/mssql.html). — van, Apr 21 '16 at 19:34

score 0 · Answer 1 · answered Apr 20 '16 at 15:09

SQL Server solution using filtered index:

CREATE TABLE tab(col INT);
CREATE UNIQUE INDEX uq_tab_col ON tab(col) WHERE col IS NOT NULL;

INSERT INTO tab(col) VALUES (1),(2),(NULL),(NULL);

-- INSERT INTO tab(col) VALUES (1);
-- Cannot insert duplicate key row in object 'dbo.tab' with unique index 'uq_tab_col'.
-- The duplicate key value is (1).

SELECT * FROM tab;

LiveDemo

Daniel Butler · Answer 2 · 2020-02-17T18:02:48.250

For anyone else that finds this later. SQL Alchemy does now support this index documentation.

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 use this 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=

Implementing Unique if not Null Constraint with SQLAlchemy and MS SQL Server

2 Answers2