From eec0040c4bcd650993bb058ebdf61ab94171fda4 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Wed, 2 Apr 2025 13:30:13 +0200 Subject: Add support for NOT ENFORCED in foreign key constraints This expands the NOT ENFORCED constraint flag, previously only supported for CHECK constraints (commit ca87c415e2f), to foreign key constraints. Normally, when a foreign key constraint is created on a table, action and check triggers are added to maintain data integrity. With this patch, if a constraint is marked as NOT ENFORCED, integrity checks are no longer required, making these triggers unnecessary. Consequently, when creating a NOT ENFORCED foreign key constraint, triggers will not be created, and the constraint will be marked as NOT VALID. Similarly, if an existing foreign key constraint is changed to NOT ENFORCED, the associated triggers will be dropped, and the constraint will also be marked as NOT VALID. Conversely, if a NOT ENFORCED foreign key constraint is changed to ENFORCED, the necessary triggers will be created, and the will be changed to VALID by performing necessary validation. Since not-enforced foreign key constraints have no triggers, the shortcut used for example in psql and pg_dump to skip looking for foreign keys if the relation is known not to have triggers no longer applies. (It already didn't work for partitioned tables.) Author: Amul Sul Reviewed-by: Joel Jacobson Reviewed-by: Andrew Dunstan Reviewed-by: Peter Eisentraut Reviewed-by: jian he Reviewed-by: Alvaro Herrera Reviewed-by: Ashutosh Bapat Reviewed-by: Isaac Morland Reviewed-by: Alexandra Wang Tested-by: Triveni N Discussion: https://www.postgresql.org/message-id/flat/CAAJ_b962c5AcYW9KUt_R_ER5qs3fUGbe4az-SP-vuwPS-w-AGA@mail.gmail.com --- doc/src/sgml/catalogs.sgml | 1 - doc/src/sgml/ref/alter_table.sgml | 5 +++-- doc/src/sgml/ref/create_table.sgml | 2 +- 3 files changed, 4 insertions(+), 4 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index fb050635551..4558f940aaf 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -2620,7 +2620,6 @@ SCRAM-SHA-256$<iteration count>:&l Is the constraint enforced? - Currently, can be false only for CHECK constraints diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 11d1bc7dbe1..ece438f0075 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -58,7 +58,7 @@ ALTER TABLE [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET COMPRESSION compression_method ADD table_constraint [ NOT VALID ] ADD table_constraint_using_index - ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] + ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ] ALTER CONSTRAINT constraint_name [ INHERIT | NO INHERIT ] VALIDATE CONSTRAINT constraint_name DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ] @@ -589,7 +589,8 @@ WITH ( MODULUS numeric_literal, REM This form validates a foreign key or check constraint that was previously created as NOT VALID, by scanning the table to ensure there are no rows for which the constraint is not - satisfied. Nothing happens if the constraint is already marked valid. + satisfied. If the constraint is not enforced, an error is thrown. + Nothing happens if the constraint is already marked valid. (See below for an explanation of the usefulness of this command.) diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index e5c034d724e..4a41b2f5530 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1409,7 +1409,7 @@ WITH ( MODULUS numeric_literal, REM - This is currently only supported for CHECK + This is currently only supported for foreign key and CHECK constraints. -- cgit v1.2.3