[PYTHON] [Error handling] peewee.IntegrityError 1451 occurs in peewee

Introduction

When I wrote an API to delete a row in the User table with peewee, which is an ORM mapper of python, the following error occurred.

peewee.IntegrityError: (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`atnow_database`.`task`, CONSTRAINT `task_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`))')

Cause

The ORM model of peewee used this time was as follows.

import peewee
from code.models.base import Base


class User(Base):
    username = peewee.CharField(unique=True, index=True)
    is_active = peewee.BooleanField(default=True)
    hashed_user_token = peewee.CharField()

from code.models.base import Base
from code.models.user import User


class Task(Base):
    name = peewee.CharField(max_length=30, index=True)
    description = peewee.CharField(max_length=300, default='')

    #Foreign key
    user = peewee.ForeignKeyField(User, backref='tasks')

In such a model design, I was trying to delete the User line.

Here, the table on the Task side has User as a foreign key. Also, the User side knows it and knows that it is externally referenced.

Therefore, if you write without specifying anything, the foreign key constraint is automatically added to the schema of the SQL table.

Therefore, if you try to delete only the User line, the related Tasks will be left behind, so in order to prevent that, the deletion was not allowed and a 1451 error was issued.

If you think from the Task side, you will be in trouble if the referenced User disappears without permission.

counter-measure

It was small written in the official documentation.

You can specify the action when it is deleted as a character string. If you use CASCADE, the Task associated with deleting User can be deleted automatically, so no error will occur.

class Task(Base):
    name = peewee.CharField(max_length=30, index=True)
    description = peewee.CharField(max_length=300, default='')

    #Foreign key
    user = peewee.ForeignKeyField(User, backref='tasks', on_delete='CASCADE')