How do I turn off foreign keys or triggers in Amazon RDS for PostgreSQL or Aurora PostgreSQL-Compatible?

Short description

PostgreSQL doesn't provide a direct function to turn on or off foreign key constraints. When you create a foreign key on a table in PostgreSQL, PostgreSQL creates an internal trigger to check for data integrity.

To turn off foreign keys or triggers, take one of the following actions:

• Change session_replication_role to replica.
• Drop the foreign key constraint on the table.

Note: For RDS for PostgreSQL or Aurora PostgreSQL-Compatible, you can't use the ALTER TABLE 'tablename' DISABLE TRIGGER ALL command.

Resolution

Note: If you don't turn on the trigger, then you might affect the integrity of the constraint. Also, the session_replication_role configuration variable might affect the mechanism that activates the trigger. When you turn on triggers for an origin or local replication role, the triggers initiate. For more information, see session_replication_role (enum) on the PostgreSQL website.

Change session_replication_role to replica

To turn off the foreign key constraints in RDS for PostgreSQL or Aurora PostgreSQL-Compatible, change the session_replication_role parameter from origin to replica. When you change the parameter to replica, you allow the pg_restore or pgloader to insert or update the data on the table. If you use the pgadmin or PostgreSQL session, then make sure that you don't initiate the system triggers that internally enforce referential.

To change session_replication_role to replica, take one of the following actions.

Run the SET PostgreSQL command

For PostgreSQL 15 or later, run the following command to grant users or roles access to set the session_replication_role parameter:

GRANT SET ON PARAMETER session_replication_role TO your-user;

Note: Replace your-user with the user or role to grant access to. If you use PostgreSQL 14 or earlier, then you can't run the preceding command and must use rds_superuser permissions.

To set session_replication_role to replica, run the following command:

postgres=> set session_replication_role = replica;

Note: The change is valid only for the current session.

Modify the parameter group of your DB instance

Modify the session_replication_role session parameter in the corresponding parameter group of your RDS or Aurora DB instance.

Note: After you complete the operation, change back the session_replication_role parameter to origin. If you don't change session_replication_role to origin, then you might experience referential integrity issues in your other tables.

Drop the foreign key constraint on the table

To turn off a trigger without superuser permissions, run the following command to drop the foreign key constraint on the table:

Alter table table_name drop constraint key_name;

Note: Replace table_name with the table name from your database and key_name with the key name from your table. For more information, see ALTER TABLE on the PostgreSQl website.

Related information

How do I turn on functions, procedures, and triggers for my Amazon RDS for MySQL DB instance?

Using a PostgreSQL database as a target for AWS Database Migration Service

session_replication_role 'replica' behavior on the PostgreSQL website