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')

Answer 1

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 ?

Answer 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'