From 1e08905842fbfa9301374f10571116bc5f0f7b8a Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Fri, 29 Nov 2024 08:52:28 +0100 Subject: [PATCH] doc: Improve description of referential actions Some of the differences between NO ACTION and RESTRICT were not explained fully. Discussion: https://www.postgresql.org/message-id/ea5b2777-266a-46fa-852f-6fca6ec480ad@eisentraut.org --- doc/src/sgml/ddl.sgml | 41 ++++++++++++++++++++++++------ doc/src/sgml/ref/create_table.sgml | 20 ++++++++------- 2 files changed, 44 insertions(+), 17 deletions(-) diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 08155b156a5..fe3425e08ff 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1233,16 +1233,32 @@ CREATE TABLE order_items ( - Restricting and cascading deletes are the two most common options. - RESTRICT prevents deletion of a - referenced row. NO ACTION means that if any - referencing rows still exist when the constraint is checked, an error - is raised; this is the default behavior if you do not specify anything. - (The essential difference between these two choices is that - NO ACTION allows the check to be deferred until - later in the transaction, whereas RESTRICT does not.) + The default ON DELETE action is ON DELETE NO + ACTION; this does not need to be specified. This means that the + deletion in the referenced table is allowed to proceed. But the + foreign-key constraint is still required to be satisfied, so this + operation will usually result in an error. But checking of foreign-key + constraints can also be deferred to later in the transaction (not covered + in this chapter). In that case, the NO ACTION setting + would allow other commands to fix the situation before the + constraint is checked, for example by inserting another suitable row into + the referenced table or by deleting the now-dangling rows from the + referencing table. + + + + RESTRICT is a stricter setting than NO + ACTION. It prevents deletion of a referenced row. + RESTRICT does not allow the check to be deferred until + later in the transaction. + + + CASCADE specifies that when a referenced row is deleted, row(s) referencing it should be automatically deleted as well. + + + There are two other options: SET NULL and SET DEFAULT. These cause the referencing column(s) in the referencing row(s) @@ -1312,6 +1328,15 @@ CREATE TABLE posts ( NULL and SET DEFAULT. In this case, CASCADE means that the updated values of the referenced column(s) should be copied into the referencing row(s). + There is also a noticeable difference between ON UPDATE NO + ACTION (the default) and NO UPDATE RESTRICT. + The former will allow the update to proceed and the foreign-key constraint + will be checked against the state after the update. The latter will + prevent the update to run even if the state after the update would still + satisfy the constraint. This prevents updating a referenced row to a + value that is distinct but compares as equal (for example, a character + string with a different case variant, if a character string type with a + case-insensitive collation is used). diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 1a1adc5ae87..70fa929caa4 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1248,17 +1248,16 @@ WITH ( MODULUS numeric_literal, REM clause specifies the action to perform when a referenced column in the referenced table is being updated to a new value. If the row is updated, but the referenced column is not actually - changed, no action is done. Referential actions other than the - NO ACTION check cannot be deferred, even if - the constraint is declared deferrable. There are the following possible - actions for each clause: + changed, no action is done. Referential actions are executed as part of + the data changing command, even if the constraint is deferred. There + are the following possible actions for each clause: NO ACTION - Produce an error indicating that the deletion or update + Produce an error if the deletion or update would create a foreign key constraint violation. If the constraint is deferred, this error will be produced at constraint check time if there still @@ -1271,10 +1270,13 @@ WITH ( MODULUS numeric_literal, REM RESTRICT - Produce an error indicating that the deletion or update - would create a foreign key constraint violation. - This is the same as NO ACTION except that - the check is not deferrable. + Produce an error if a row to be deleted or updated matches a row in + the referencing table. This prevents the action even if the state + after the action would not violate the foreign key constraint. In + particular, it prevents updates of referenced rows to values that + are distinct but compare as equal. (But it does not prevent + no-op updates that update a column to the same + value.) -- 2.30.2