On Tue, Mar 13, 2012 at 5:03 PM, Conor <conor.edward.da...@gmail.com> wrote: > On 03/13/2012 09:21 AM, Daniel Nouri wrote: > > I have a node with a parent_id, which may be None (for the root node). > Can I make a SQL table constraint that says: 'there may only be one > node with the parent_id of None' (while it's fine if many nodes share > a parent_id that's not None)? > > Thanks, > Daniel > > You can use a functional unique index that takes advantage of multiple NULLs > in being allowed in a unique index (beware: older MS SQL versions did not > follow this behavior): > > CREATE UNIQUE INDEX mytable_parent_id_un ON mytable (CASE WHEN parent_id IS > NULL THEN 1 ELSE NULL END)
Thanks very much. This looks like what I want. I tried this using the event/DDL, but with both SQLite and Postgres, I'm getting this error: OperationalError: (OperationalError) near "CASE": syntax error u'CREATE UNIQUE INDEX nodes_parent_id_un ON nodes (CASE WHEN parent_id IS NULL THEN 1 ELSE NULL END)' () Did I get the syntax wrong? Daniel -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
