This guide will help you fix your broken django database and eliminate any null value in column violates not-null constraint errors.
Most importantly, it will help you do so in django, using only the django shell. This is really useful when you don’t have access to psql or an equivalent program that can connect to the database using the django db shell.
If you are only interested in how to execute sql in django, please read my article How To Run / Execute Raw SQL Queries In Django.
Otherwise, please keep reading.
The Problem – A Broken Django Database
Django’s migration system is great.
However, sometimes, due the order of migrations or columns not being removed when a migration is unapplied, you can end up with extra fields in your database tables that you don’t want.
And even worse, in cases when those fields are not nullable, you can end up with a messed up database that breaks your application.
In this case, you will probably see an error like the following:
1 null value in column 'my_column' violates not-null constraint
And now, because of this error, we can’t insert a record into our database.
How can we fix this?
Fix Your Django Database By Deleting Columns
For this example, assume that we have a model called books inside of a django app called booksapp.
The Book model looks like this:
book_title = models.CharField(max_length=300)
number_of_pages = models.IntegerField(blank=True, null=True,)
The error we are currently getting looks like this:
1 null value in column 'number_of_words' violates not-null constraint
We are getting this error because we used to have a non-nullable field named number_of_words on our model that somehow was not properly deleted from the database.
Let’s see how we can fix this.
We will start by going into the django shell.
1 python manage.py shell
Let’s now import connection from "django.db" so we can connect to our database.
1 from django.db import connection
In this case, we want to modify the books table in order to drop the number_of_words column.
Ok, now let’s define the query that we want to run.
1 query = "ALTER TABLE booksapp_books DROP COLUMN number_of_words;"
Now that we have our query defined, we can run the following bit of code to execute our sql query.
with connection.cursor() as cursor:
Now, let’s confirm that our column has been deleted by trying to select that field from our table.
1 query2 = "select number_of_words from bookapp_books;"
And here’s the output:
Traceback (most recent call last):
File '/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py', line 82, in _execute
psycopg2.errors.UndefinedColumn: column 'number_of_words' does not exist
LINE 1: select number_of_words from bookapp_books
Awesome, this means that the column no longer exists.
We did it! Our database should now work properly.
Why This is Useful
Some people may be wondering why anyone would do this in the django shell when there are many other ways to do this.
Yes, you can do the same thing through psql, python manage.py dbshell, or some other cli program.
However, sometimes, you might not be able to access one of these other cli programs for a number of reasons. Perhaps your server is simply not setup in a way where you can use one of these programs. Or maybe your server is not well documented and you simply don’t know how.
No problem! You can use the Django shell instead. As long as you know how to enter the django shell, you should have no problems cleaning up your database.
I hope this was helpful.
If you enjoy reading useful django content like this, check out my Django 2 Upgrade Tutorial where i document solutions to common problems that you may encounter when upgrading to Django 2.