> On Nov 17, 2014, at 7:37 AM, Guido Winkelmann
> <gu...@ambient-entertainment.de> wrote:
>
> Hi,
>
> How can I go about having a cross-platform compatible compound unique
> constraint over two columns where there can be only one record with NULL in
> one column and a given value in the other?
>
> I want something like this:
>
> UniqueConstraint("parent_id", "name")
>
> except "parent_id" is nullable, and I want to disallow creating multiple
> records with parent_id=NULL and the same name. The problem here is that some
> database management systems, PostgreSQL for example, will treat all NULL
> values as not-equal inside a unique constraint, which makes sense for a
> single column unique, but not really for a multi-column one. This will allow
> multiple records with the same name and parent_id=NULL again.
>
> In PostgreSQL, apparently I would need to create a unique index in addition
> to the constraint. I would really like to solve this without writing
> database-specific code, though.
>
> Does anybody have a good solution for that?
I’ve had an issue in this area before and decided just to forego it; I was
targeting PG and SQL Server, but I don’t remember exactly which NULL behavior I
was looking for. per the SQL standard I think PG’s behavior is correct. NULL
means, “unknown”, so two NULL values are never equivalent.
In any case this is more of a stack overflow question, I can show you how to
run different DDL on different backends* but you need to figure out what
specific uniques/indexes you want to make in each case.
* once you know your DDL just use a combination of events,
UniqueConstraint/Index and/or DDL(“exact sql”) to produce what you want, I’d
roll it into a single function that you can reuse, docs at
http://docs.sqlalchemy.org/en/rel_0_9/core/ddl.html#controlling-ddl-sequences
<http://docs.sqlalchemy.org/en/rel_0_9/core/ddl.html#controlling-ddl-sequences>.
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
