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

3 minute read
0

I want to turn off foreign key checks for my Amazon Relational Database Services (Amazon RDS) for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition DB instance.

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 key constraints, you must have superuser permissions to delete the internal triggers that PostgreSQL creates.

When you run the following command that turns off all triggers on a table, you receive a permission denied error:

ALTER TABLE TestTable DISABLE TRIGGER ALL;
Error report -
ERROR: permission denied: "RI_ConstraintTrigger_c_9999181" is a system trigger

Because RDS for PostgreSQL and Aurora PostgreSQL-Compatible Edition are managed services, they don't allow all superuser permissions, even to the primary user. The ALTER TABLE 'tablename' DISABLE TRIGGER ALL; command isn't allowed.

Resolution

Note: If the trigger isn't turned on, then the integrity of the constraint might be affected. Also, the session_replication_role configuration variable can affect the mechanism that activates the trigger. When you turn on triggers for a replication role that's origin or local, the triggers initiate. For more information, see session_replication_role (enum) on the PostreSQL website. 

Change session_replication_role to replica

To turn off the foreign key constraints in RDS for PostgreSQL or Aurora PostgreSQL-Compatible Edition, change the session_replication_role parameter from origin to replica. When you change this parameter to replica, you allow the pg_restore or pgloader to insert or update the data on the table. 

To change session_replication_role to replica, use one of these options:

  • If you use the pgadmin or PostgreSQL session, then run the following command so that the system triggers that internally enforce referential aren't initiated:
postgres=> set session_replication_role = replica;

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

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

When you change session_replication_role to replica, you can't run the ALTER TABLE 'table_name' DISABLE TRIGGER ALL; command. The operation fails, but the command still stops the trigger at either the session or instance level.

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;

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 PostreSQL website

AWS OFFICIAL
AWS OFFICIALUpdated 6 months ago
2 Comments

Is the following still the recommended workflow in 2024. AWS DMS workflow recommends disabling foreign keys when splitting large tables over multiple dms tasks, which requires foreign keys to be disabled to ensure consistency. If the following is correct, how do you enable the foreign keys after dms full or cdc has completed?

Peter
replied 20 days ago

Thank you for your comment. We'll review and update the Knowledge Center article as needed.

profile pictureAWS
MODERATOR
replied 20 days ago