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

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

1 answer

  • answered 2018-10-11 20:25 Shobi

    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 ?