SQLAlchemy self-referential parent_id dilemna

I have a Category object like this:

def parent_default(context):
    ''' default parent id if the name is not root '''
    id_ = None
    if context.current_parameters['name'] != u'root' :
        id_ = 1
    return id_


class Category(Base):
    ''' Class representing a product category. '''
    __tablename__ = "CATEGORY"
    id = Column(Integer, primary_开发者_JS百科key=True)
    name = Column(Unicode(50), nullable=False)
    #self-referential mapper
    parent_id = Column(Integer, ForeignKey('CATEGORY.id'), default=parent_default)
    products = relationship("Product", backref="products")
    parent = relationship('Category', remote_side=[id], backref='sub_categories')

    __table_args__ = (
            UniqueConstraint('parent_id', 'name'),
        )

The problem I am having is that I am able to create two 'root' objects with a None 'parent_id', it seems the UniqueConstraint doesn't apply to a None 'parent_id'?. Ideally there would be only one object with a None parent_id. I must be missing something here.

Whether the tuple NULL, 'root' is unique varies a bit from DBMS to DBMS; this was added to the sql standard somewhat recently. In most cases, it is not unique, after all, NULL = NULL is not true.

You're already requiring that the root tuple have an id of 1, so you can safely remove the nullable=True from the parent ID; and then just set the root object to be its own parent.