> On Nov 17, 2014, at 7:37 AM, Guido Winkelmann > <[email protected]> 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 [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.
