Django: How to set foreign key checks to 0

Question

Ok so i'm migrating database from sqlite to mysql , i had few errors but i already resolved them. Now i have problem with this option because i don't know how to disable it. I tried

DATABASES = {

 'default': {
    'ENGINE': 'django.db.backends.mysql',
    'NAME': 'slave',                     
    'USER': 'root',                     
    'PASSWORD': 'root',                 
    'OPTIONS': {
                "init_command": "SET foreign_key_checks = 0;",
           },
    'HOST': '',                    
    'PORT': '',                      
   }
}

But it doesn't works and i don't know why.

Ofc i use json files to migration

python manage.py dumpdata --indent 2 --natural > dump.json
python manage.py loaddata dump.json

When I'm loading data on begining i can see:

SET SQL_AUTO_IS_NULL = 0
SET foreign_key_checks=0

But after some time:

SELECT (1) AS `a` FROM `xxx` WHERE `xxx`.`id` = 8  LIMIT 1
SET foreign_key_checks=1

And then i see exception. Traceback isn't important because it is connected with foreignkeys you can read more here

http://coffeeonthekeyboard.com/django-fixtures-with-circular-foreign-keys-480/

I know that i need to disable this option.

I tried :

http://djangosaur.tumblr.com/post/7842592399/django-loaddata-mysql-foreign-key-constraints

But like i said it doesn't work.

Can someone help...

if the import is re-enabling FK checks, there's not much you can do short of hacking up the source file to REMOVE that FK setting change. — Marc B, Apr 03 '13 at 17:25

score 7 · Answer 1 · answered Jul 13 '13 at 04:02

7

you can put this at the end of your settings.py:

import sys
if 'loaddata' in sys.argv:
    # only change this for loaddata command.
    DATABASES['default']['OPTIONS'] = {
       "init_command": "SET foreign_key_checks = 0;",
    }

answered Jul 13 '13 at 04:02

dnozay

23,846
6
82
104

1

I'm getting `TypeError: 'init_command' is an invalid keyword argument for this function` – user2291758 Jan 09 '15 at 12:46
In case you get the ```'init_command' is an invalid keyword``` error, you can use ```db_constraint=False``` on your fields as a workaround, see here: http://stackoverflow.com/questions/8051556/django-how-to-prevent-database-foreign-key-constraint-creation – Joris Jun 30 '15 at 11:46
Looks like `init_command` is not supported by the `sqlite3` backend. The backend explicitly sets `PRAGMA foreign_keys = ON`, see [source](https://github.com/django/django/blob/stable/3.2.x/django/db/backends/sqlite3/base.py#L267). Also see SQLite [docs](https://www.sqlite.org/pragma.html#pragma_foreign_keys). – djvg Feb 25 '21 at 15:17
Also not support by postgres it seems - this solution is mysql only (I know that's what the OP asked, but I as others I'm sure came here for otehr backends...) – logicOnAbstractions Dec 02 '21 at 14:40

score 2 · Answer 2 · answered Dec 12 '22 at 14:51

In case, somebody else needs this for sqlite3, add the following code to your settings.py:

if 'loaddata' in sys.argv:

    # is database used sqlite3?
    if 'sqlite3' in DATABASES['default']['ENGINE']:
    # disable sqlite foreign key checks
        print("Loading data from fixtures - disabling foreign key checks")
        from django.db.backends.signals import connection_created
        def disable_foreign_keys(sender, connection, **kwargs):
            cursor = connection.cursor()
            cursor.execute('PRAGMA foreign_keys=OFF;')
        connection_created.connect(disable_foreign_keys)

Django: How to set foreign key checks to 0

2 Answers2