On 03/14/2012 06:37 AM, Daniel Nouri wrote: > 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?
Oops, PostgreSQL requires extra parens: CREATE UNIQUE INDEX nodes_parent_id_un ON nodes ((CASE WHEN parent_id IS NULL THEN 1 ELSE NULL END)) I did not see a way to create functional indexes in SQLite. You can probably achieve the same effect with triggers. -Conor -- 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.
