peewee.IntegrityError: (1452, 'Cannot add or update a child row: a foreign key constraint fails (test_peewee_tree.node, CONSTRAINT node_ibfk_1 FOREIGN KEY (parent_id) REFERENCES node (id))') db = MySQLDatabase

[slukin1]

peewee.IntegrityError: (1452, 'Cannot add or update a child row: a foreign key constraint fails (test_peewee_tree.node, CONSTRAINT node_ibfk_1 FOREIGN KEY (parent_id) REFERENCES node (id))')
db = MySQLDatabase

[coleifer]

Uhh, did you make the parent foreign key nullable? This looks like a data integrity error rather than peewee.

[slukin1]

Yes, parent foreign ey made nullable:

mysql_db = MySQLDatabase('test_peewee_tree')
class BaseModel(Model):
class Meta:
        database = mysql_db
class Node(BaseModel):
    label = CharField()
    parent = ForeignKeyField('self', default=0)
    left = IntegerField(default=0)
    right = IntegerField(default=1)
    depth = IntegerField(default=0)
def get_parent(self, level=1):
        parent = self
        for i in range(level):
            parent = parent.parent
        return parent
if __name__ == '__main__':

    Node.create_table()

    n1 = Node(label='root')
    n2 = Node(label='a')
    n3 = Node(label='b')
    n4 = Node(label='c')
    n1.append(n2)
    n1.append(n3)
    n2.append(n4)

[slukin1]

Same peewee.IntegrityError: (1452....
if changed to
parent = ForeignKeyField('self', default=0, null = True)
or
parent = ForeignKeyField('self', db_column='parent_id', default=0, null = True)

[slukin1]

No error peewee.IntegrityError: (1452...
with peewee version 2.2.4 and parent = ForeignKeyField('self', null = True)

[slukin1]

With parent = ForeignKeyField('self', default=None, null=True) works well if peewee version 2.2.4
but with peewee version 2.6.4 error:
row = self.select().where(self.pk_expr()).first()
AttributeError: 'Node' object has no attribute 'pk_expr'

[coleifer]

It's very difficult to read your comments as you have not bothered to format the code. I edited one of your comments so you can see how to do it.

For what it's worth, 2.2.4 is quite old (about a year and a half). Where is the row = self.select().where(self.pk_expr()) coming from?

[coleifer]

I use hierarchical models in a couple places as well as in the test suite. The right way to do it is

parent = ForeignKeyField('self', null=True)

If you aren't re-creating your tables every time you change the field then just changing the Python field deifnition won't alter the underlying schema which may have a not-null constraint.

[slukin1]

For peewee version 2.6.4:
self.pk_expr() => self._pk_expr() # pk -> _pk