How do i temporarily disable db integrity constraints in django - postgresql

Question

I am writing a Django command to seed an existing table,

I need to truncate the table before seeding, but there are foreign key constraints on that table.

because of that, I am getting django.db.utils.IntegrityError while truncating the table,

How do I turn the Foreign Key Checks off temporarily in Django?

I saw SET FOREIGN KEY CHECK = 0 but don't know where to put them :(

The Django Command class:

class Command(BaseCommand):
help = "Command to seed the aws regions"
regions = [
    {
        'name': 'Us East (N. Virginia)',
        'region': 'us-east-1',
    },
    {
        'name': 'US West (Oregon)',
        'region': 'us-west-2',
    },
    {
        'name': 'EU (Ireland)',
        'region': 'eu-west-1',
    },
]
def handle(self, *args, **options):
    self.stdout.write('seeding regions...')

    AwsRegions.objects.all().delete() # this is where i get errors

    for name, region in self.regions:
        self.stdout.write(region)
        AwsRegions.objects.create(name, region)


    self.stdout.write('done seeding regions')

`SET FOREIGN KEY CHECK = 0` seams like a MySQL query to me.. — Raymond Nijland, Oct 11 '18 at 19:30
Well the PostgreSQL docs give a hint "The firing of **triggers** that are declared as **"constraint triggers"** is also controlled by this setting " https://www.postgresql.org/docs/9.1/static/sql-set-constraints.html so i assume you can disable that "constraint" trigger with `ALTER TABLE table DISABLE TRIGGER ALL;` and to actived it again `ALTER TABLE table ENABLE TRIGGER ALL;` — Raymond Nijland, Oct 11 '18 at 19:33

score 8 · Accepted Answer · edited Sep 12 '21 at 21:59

Got the solution.

I had to disable the Triggers on the table to stop the foreign key constraint check.

Disable Triggers

def disable_triggers(self):
    with connection.cursor() as cursor:
        cursor.execute('ALTER TABLE "Table Name" DISABLE TRIGGER ALL;')

Enable Triggers

def enable_triggers(self):
    with connection.cursor() as cursor:
        cursor.execute('ALTER TABLE "Table Name" ENABLE TRIGGER ALL;')

Important Notes:

According to this doc link, you can pass a list as the second argument to the execute() method (eg: you might want to pass the table name dynamically), but this will automatically escape the variables and you might end up forming a syntactically wrong PostgreSQL query (which took a lot of my time to fix it)
Make sure you turn the triggers back on properly
If you are getting a Permission denied error Then you might want to check the DB user permissions, I just turned on superuser permissions from PgAdmin, which was ok for me. and everything back to work. How to do it ?

Nice solution, however it appears to be using raw SQL. Do you by any change know a solution that uses the Django ORM so it would be DBMS agnostic? — Erik Kalkoken, Sep 11 '19 at 09:26
Well. I wasn't able to find an ORM solution, But what I did was to include this in the script. — Shobi, Sep 11 '19 at 13:50

score 2 · Answer 2 · answered Oct 15 '18 at 23:04

2

To disable triggers for all tables (useful when you need to stop it for multiple tables):

SET session_replication_role TO 'replica'

And to restore:

SET session_replication_role TO 'origin'

answered Oct 15 '18 at 23:04

Roman Tkachuk

3,096
1
16
15

score 1 · Answer 3 · answered Feb 15 '22 at 14:55

1

from django.db import connection
with connection.constraint_checks_disabled():
    do_stuff()

Credit goes to https://stackoverflow.com/a/11926432/2558400

answered Feb 15 '22 at 14:55

Ramast

7,157
3
32
32

How do i temporarily disable db integrity constraints in django - postgresql

3 Answers3

Linked