PYTHON PEEWEE on_delete='CASCADE' doesn't work

Question

I have a table and it have a ForeignKeyField referencing another table. ON DELETE functionality is supported by initializing ForeignKeyField with an on_delete argument. Though it's not very clear what values on_delete can take, the documentation gives an example, e.g. 'CASCADE'. This being said, on_delete='CASCADE' seems to have no effect, as attempting to delete a row from one of the parent tables throws an error.

this is an example that does NOT work:

import peewee

db = peewee.SqliteDatabase("base.db")

class BaseModel(peewee.Model):
    class Meta:
        database = db

class Grades(BaseModel):
    year = peewee.IntegerField()
    division = peewee.CharField(max_length=1)

class Student(BaseModel):
    dni = peewee.IntegerField()
    name = peewee.CharField(max_length=40)
    surname = peewee.CharField(max_length=40)
    gender = peewee.CharField(max_length=1)
    grade = peewee.ForeignKeyField(Grades, on_delete="CASCADE")

what I expect is that when deleting a grade, the students of this grade are deleted

score 0 · Accepted Answer · answered Dec 12 '22 at 13:45

The CASCADE needs to be in place when the tables are created, and Sqlite must also be configured with PRAGMA foreign_keys=1, e.g.

db = SqliteDatabase('...', pragmas={'foreign_keys': 1})

When both these conditions are met, the Grades will be deleted when their corresponding student is deleted, e.g.:

db = SqliteDatabase(':memory:', pragmas={'foreign_keys': 1})                             

class Student(db.Model):
    name = TextField()

class Grade(db.Model):
    student = ForeignKeyField(Student, on_delete='CASCADE')                              
    value = TextField()

db.create_tables([Student, Grade])
s1, s2 = Student.create(name='s1'), Student.create(name='s2')                            
Grade.create(student=s1, value='A')
Grade.create(student=s2, value='F')
s1.delete_instance()
for g in Grade:
    print(g.value)

Prints:

PYTHON PEEWEE on_delete='CASCADE' doesn't work

1 Answers1