> On Nov 17, 2014, at 1:55 PM, Guido Winkelmann > <[email protected]> wrote: > > > Am Montag, 17. November 2014 16:25:54 UTC+1 schrieb Michael Bayer: > >> On Nov 17, 2014, at 7:37 AM, Guido Winkelmann >> <[email protected] <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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
