SQLAlchemy Adjacency List - Constrain parent_id not equal to ID

user_680268 0
Viewed 82

I'm implementing an Adjacency List in SQL Alchemy which I have working. It's the basic example here of Node. I have it working.

class Node(Base):
    __tablename__ = 'node'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('node.id'))
    data = Column(String(50))
    children = relationship("Node")

But, I want to enforce a constraint where the parent_id != id. That is, a row can not be its own parent. I am not sure how to enforce this. Do I need to use a @validates or is there a DB constraint I can set up on the column(s).

sqlalchemy constraints python database
1 Answers

You could use either @validates or a db constraint. The constraint would look like this:

import sqlalchemy as sa

class Node(Base):
    __tablename__ = 'node'
    id = sa.Column(sa.Integer, primary_key=True)
    parent_id = sa.Column(sa.Integer, sa.ForeignKey('node.id'))
    data = sa.Column(sa.String(50))
    children = orm.relationship("Node")

    __table_args__ = (sa.CheckConstraint('parent_id != id'),)
user_53209060
Related