IntegrityError: FOREIGN KEY constraint failed in django

Question

I have a django view that causes FOREIGN KEY constraint failed. This is coming from a related question

def return_book(request,pk):
    books = Books.objects.filter(school = request.user.school).get(id = pk)
    book = Issue.objects.get(book_id_id=pk,book_id__school_id = request.user.school.id)
    user = CustomUser.objects.get(id=request.user.id)
    Return.objects.create(borrowed_item_id=book.id,returner=user)
    Books.Addbook(books) 
    Issue.objects.get(book_id_id=pk).delete()
    return redirect("view_books")

The erro is returned at Issue.objects.get(book_id_id=pk).delete() I don't know the exact cause of this error. Could somebody explain to me what's going on hat causes the error> The Traceback is below.

Traceback (most recent call last):
  File "C:\Users\FR GULIK\AppData\Local\Programs\Python\Python38-32\lib\site-packages\django\core\handlers\exception.py", line 47, in inner
    response = get_response(request)
  File "C:\Users\FR GULIK\AppData\Local\Programs\Python\Python38-32\lib\site-packages\django\core\handlers\base.py", line 181, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "C:\Users\FR GULIK\AppData\Local\Programs\Python\Python38-32\lib\site-packages\django\contrib\auth\decorators.py", line 21, in _wrapped_view
    return view_func(request, *args, **kwargs)
  File "D:\Python\Django\test projects\library manage\lib_system\Library-System\libman\views.py", line 209, in return_book
    Issue.objects.get(book_id_id=pk).delete()#Borrower_id is also required in the filter
  File "C:\Users\FR GULIK\AppData\Local\Programs\Python\Python38-32\lib\site-packages\django\db\models\base.py", line 947, in delete
    return collector.delete()
  File "C:\Users\FR GULIK\AppData\Local\Programs\Python\Python38-32\lib\site-packages\django\db\models\deletion.py", line 396, in delete
    count = sql.DeleteQuery(model).delete_batch([instance.pk], self.using)
  File "C:\Users\FR GULIK\AppData\Local\Programs\Python\Python38-32\lib\site-packages\django\db\models\sql\subqueries.py", line 43, in delete_batch
    num_deleted += self.do_query(self.get_meta().db_table, self.where, using=using)
  File "C:\Users\FR GULIK\AppData\Local\Programs\Python\Python38-32\lib\site-packages\django\db\models\sql\subqueries.py", line 23, in do_query
    cursor = self.get_compiler(using).execute_sql(CURSOR)
  File "C:\Users\FR GULIK\AppData\Local\Programs\Python\Python38-32\lib\site-packages\django\db\models\sql\compiler.py", line 1156, in execute_sql
    cursor.execute(sql, params)
  File "C:\Users\FR GULIK\AppData\Local\Programs\Python\Python38-32\lib\site-packages\django\db\backends\utils.py", line 98, in execute
    return super().execute(sql, params)
  File "C:\Users\FR GULIK\AppData\Local\Programs\Python\Python38-32\lib\site-packages\django\db\backends\utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "C:\Users\FR GULIK\AppData\Local\Programs\Python\Python38-32\lib\site-packages\django\db\backends\utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "C:\Users\FR GULIK\AppData\Local\Programs\Python\Python38-32\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "C:\Users\FR GULIK\AppData\Local\Programs\Python\Python38-32\lib\site-packages\django\db\utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "C:\Users\FR GULIK\AppData\Local\Programs\Python\Python38-32\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "C:\Users\FR GULIK\AppData\Local\Programs\Python\Python38-32\lib\site-packages\django\db\backends\sqlite3\base.py", line 413, in execute
    return Database.Cursor.execute(self, query, params)
django.db.utils.IntegrityError: FOREIGN KEY constraint failed

class Issue(models.Model):
    borrower_id = models.ForeignKey(Student,on_delete=models.CASCADE)
    book_id = models.ForeignKey(Books,on_delete=models.CASCADE)
    issue_date = models.DateField(default=datetime.date.today)
    issuer = models.ForeignKey(CustomUser,on_delete=models.CASCADE)

class Return(models.Model):
    return_date = models.DateField(default=datetime.date.today)
    borrowed_item = models.ForeignKey(Issue,on_delete=models.DO_NOTHING)
    returner = models.ForeignKey(CustomUser,on_delete=models.DO_NOTHING)

Answer 1

This edge case is not directly relevant for the OP, but may help others that wind up here based on the title:

An IntegrityError: FOREIGN KEY constraint failed will also be raised when trying to delete some object x, if there is a table in your database that Django is unaware of, and this table has a reference to object x.

This can happen, for example, if you remove a model from your code without properly migrating the changes.

As a result, the table remains in the database, but Django does not know anything about it, so, when object x is deleted, Django does not know it should also delete the corresponding row from this "orphaned" table.

Answer 2

Do DO_NOTHING is typically not a good option since (most) databases will check referential integrity. This means that they guarantee that if a ForeignKey refers (in this case to an Issue), then that means that the table that stores the issues should have a record with the primary key the Return item refers to.

Often DO_NOTHING is used in combination with a certain trigger in the database system (that Django is not aware of).

Typically the most popular choices foron_delete are:

1. models.CASCADE: in that case it will remove all Returns related to the removed item;
2. models.PROTECT: in that case it will raise an error to prevent removing the Issue; and
3. models.SET_NULL: this is done on a NULLable field (so with null=Ture), in which case the impacted Return(s) will set the field to NULL/None.

Another option might be to "soft delete" records, for example with the django-soft-delete package [pypi]. In that case a boolean is added to the Issue that specifies if the item is removed. The package will also transparently filter the objects, such that you only retrieve records that are "alive".