How to handle unique constraint for multiple columns containing null values using sqlachemy?

[ly...@mode.net]

Hi,

I find that `UniqueConstraint` from `sqlachemy` and `op.create_unique_constraint` by `alembic`, it cannot handle multiple columns that contain null values.

I saw we can solve using  SQL commands via some tricky ways like creating unique index and coalesce, do we have some ways using `sqlachemy` instead of directly using `SQL` to solve this issue?

Thanks!

Lydia

[Mike Bayer]

the UniqueConstraint object spits out "CREATE UNIQUE CONSTRAINT" or
equivalent DDL only. The "null values" issue is very different
across backends - in particular, the limitation that NULL values
aren't allowed is a peculiarity specific to Microsoft SQL Server.

Given that, there are ways to create DDL that does what you need, and
then if necessary, to have different kinds of DDL emit based on
different backends if you are looking for cross-database
compatibility. For intricate edge cases like this, it's much better
that SQLAlchemy gives the developer the tools to create exactly the
DDL sequence that is tailored towards their needs, rather than it
trying to pre-package a particular solution that probably won't work
the same way across different backends or otherwise be flexible
enough.


>
> Thanks!
>
> Lydia
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
> ---
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Mike Bayer]

I should add, please specify which backend you're using and what DDL
sequence you are looking to emit.