Why Django is not creating Foreign Key constraint on MySQL?

Question

I'm not new to Python nor Django, but this is the first time I'm creating a completely new big project from scratch, and also the first time I'm actually creating the models for the whole database and I'm kinda confused here.

Does Django does not really create the ForeignKey constraints on the Database to check if ID exists? It is just a logical python thing that works only when the server is running? Or is it a problem that happens on MySQL?

Just to be clear what I'm talking about, the first thing I noticed because as a Laravel developer on PHP side, I'm used to always check the database diagram that PhpStorm/PyCharm generates by connecting to the database, and on Laravel migrated tables, we can see the arrows pointing to the respective foreign key tables relationships, but on the Django created database there is not a single arrow on the database diagram generated by the JetBrains IDE. So I went testing.

For example, I have the following models:

    class Series(models.Model):

        class Meta:
            app_label = 'core'
            db_table = 'km_series'
            verbose_name_plural = 'series'  # added this to avoid plural being "seriess"

        name = models.CharField(max_length=200)
        description = models.TextField(default=None, blank=True, null=True)
        cover_img = models.CharField(max_length=100, default=None, blank=True, null=True)
        on_going = models.BooleanField(default=False)
        date_added = models.DateTimeField(auto_now_add=True)
        date_updated = models.DateTimeField(auto_now=True)

        def __str__(self):
            return "{} - ID #{}".format(self.name, self.id)


    class Chapter(models.Model):
    

        class Meta:
            app_label = 'core'
            db_table = 'km_chapter'

        series = models.ForeignKey(Series, on_delete=models.CASCADE)
        number = models.IntegerField(validators=[MinValueValidator(0)])
        name = models.CharField(max_length=150, default=None, blank=True, null=True)
        date_added = models.DateTimeField(auto_now_add=True)
        date_updated = models.DateTimeField(auto_now=True)

        def __str__(self):
            return "#{} - {}".format(self.number, self.name)

I have more than 15 models created already using models.ForeignKey along other fields. I just tried creating a new row on MySQL using the python manage.py shell.

$ python manage.py shell
>>> from core.models import *
>>> Series

>>> one = Series.objects.create(name='Test')
>>> one

>>> one.id
1
>>> chapter = Chapter.objects.create(number=1)
MySQLdb.OperationalError: (1048, "Column 'series_id' cannot be null")
>>> chapter = Chapter.objects.create(number=1, series_id=2)
>>> chapter

>>> chapter_1 = Chapter.objects.create(number=1, series=one)
>>> chapter_1

>>> chapter = Chapter.objects.create(number=1, series_id=25)

There is only one ID on database, where the ID is "1"

So how can I be able to add any ID when manually assigning, and not passing the whole instantiated object as foreign_key value?

Why Django allows me to set the ID to non-existing IDs on the database? Shouldn't this result in an error? Am I missing something on my models? Why there is no constraint and validations for this kind of thing?

Dkage · Answer

After digging a little on why MySQL would have a problem with FK contraints, and after using the python manage.py dbshell command to check the table creation as I was recommended doing on a comment, by using SHOW CREATE TABLE core_chapter; , I discovered the problem.

For some reason that I don't really know why, my Database was created using MyISAM, which is a MySQL storage engine that does not support FK constraints. I had to change the default to InnoDB, as it works with this types of validations for relationships between tables.

On my my.cnf file which holds the confs for MySQL, I enforced InnoDB as default by adding the default-storage-engine property

[mysqld]
default-storage-engine = InnoDB

And on Django settings, I also added the DATABASE "OPTIONS", as stated on the Django documentation for databases, so my DATABASES value was changed to:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'database_name',
        'USER': 'database_user',
        'PASSWORD': '****',
        'HOST': 'localhost',
        'PORT': '3306',
        'OPTIONS': {'init_command': 'SET default_storage_engine=INNODB'},
    }
}

After changing all that, dropping all database tables and then calling the python manage.py migrate again, the constraints were created as expected, and now my PyCharm generated database diagram shows all the arrows showing the relationships flawlessly.

Why Django is not creating Foreign Key constraint on MySQL?

1 Answers