PYTHON PEEWEE on_delete='CASCADE' doesn't work-CodePudding

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

CodePudding user response：

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:

CodePudding user response：

The on_delete argument in the ForeignKeyField constructor specifies what should happen to the rows in the table that contains the foreign key when a row in the parent table is deleted. The possible values for on_delete are:

CASCADE: This option will delete any rows in the table with the foreign key that reference the deleted row in the parent table.

SET NULL: This option will set the foreign key in the rows in the table with the foreign key to NULL when the row in the parent table is deleted.

RESTRICT: This option will prevent the deletion of the row in the parent table if there are any rows in the table with the foreign key that reference it.

SET DEFAULT: This option will set the foreign key in the rows in the table with the foreign key to the default value specified in the foreign key definition when the row in the parent table is deleted.

NO ACTION: This option will do nothing to the rows in the table with the foreign key when the row in the parent table is deleted.

In your code, you are using the CASCADE option for on_delete, which means that any rows in the Student table that reference the deleted row in the Grades table should be deleted. However, this is not happening because you are not actually deleting any rows from the Grades table.