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)
AFAIK SQLAlchemy's Index class does not support functional indexes, but
you can work around that via DDL events:
event.listen(mytable, "after_create", DDL("CREATE UNIQUE INDEX ..."))
Also, if you want to trim down index size, some databases (e.g.
PostgreSQL) support partial indexes, while others (older Oracle versions
I believe) simply do not index NULLs at all. Partial index example:
CREATE UNIQUE INDEX mytable_parent_id_un ON mytable (1) WHERE parent_id IS NULL
-Conor
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
