> On Nov 17, 2014, at 1:55 PM, Guido Winkelmann
> <gu...@ambient-entertainment.de> wrote:
>
>
> Am Montag, 17. November 2014 16:25:54 UTC+1 schrieb Michael Bayer:
>
>> On Nov 17, 2014, at 7:37 AM, Guido Winkelmann
>> <gu...@ambient-entertainment.de <javascript:>> 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.
>
> If I wanted to add a manual check in Python for that, how would I go about
> that?
>
> Could I register an event handler for the "before_insert" event for this
> model?
>
> What would the event handler have to do to refuse an insert? Throw an
> exception?
you could use a before_insert event, though that’s expensive as you’d need to
query the whole table to check for dupes for every row.
I think organizing the kinds of indexes/constraints that apply to different
backends would be the best approach here.
--
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.
