diff options
Diffstat (limited to 'doc')
| -rw-r--r-- | doc/src/sgml/catalogs.sgml | 12 | ||||
| -rw-r--r-- | doc/src/sgml/ddl.sgml | 33 | ||||
| -rw-r--r-- | doc/src/sgml/ref/alter_table.sgml | 2 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_table.sgml | 24 |
4 files changed, 64 insertions, 7 deletions
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index c1d11be73f7..025db987633 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -2710,6 +2710,18 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l <row> <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>confdelsetcols</structfield> <type>int2[]</type> + (references <link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.<structfield>attnum</structfield>) + </para> + <para> + If a foreign key with a <literal>SET NULL</literal> or <literal>SET + DEFAULT</literal> delete action, the columns that will be updated. + If null, all of the referencing columns will be updated. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> <structfield>conexclop</structfield> <type>oid[]</type> (references <link linkend="catalog-pg-operator"><structname>pg_operator</structname></link>.<structfield>oid</structfield>) </para> diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 642ea2a70d0..64d90306522 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1084,9 +1084,40 @@ CREATE TABLE order_items ( </para> <para> + The actions <literal>SET NULL</literal> and <literal>SET DEFAULT</literal> + can take a column list to specify which columns to set. Normally, all + columns of the foreign-key constraint are set; setting only a subset is + useful in some special cases. Consider the following example: +<programlisting> +CREATE TABLE tenants ( + tenant_id integer PRIMARY KEY +); + +CREATE TABLE users ( + tenant_id integer REFERENCES tenants ON DELETE CASCADE, + user_id integer NOT NULL, + PRIMARY KEY (tenant_id, user_id) +); + +CREATE TABLE posts ( + tenant_id integer REFERENCES tenants ON DELETE CASCADE, + post_id integer NOT NULL, + author_id integer, + PRIMARY KEY (tenant_id, post_id), + FOREIGN KEY (tenant_id, author_id) REFERENCES users ON DELETE SET NULL <emphasis>(author_id)</emphasis> +); +</programlisting> + Without the specification of the column, the foreign key would also set + the column <literal>tenant_id</literal> to null, but that column is still + required as part of the primary key. + </para> + + <para> Analogous to <literal>ON DELETE</literal> there is also <literal>ON UPDATE</literal> which is invoked when a referenced - column is changed (updated). The possible actions are the same. + column is changed (updated). The possible actions are the same, + except that column lists cannot be specified for <literal>SET + NULL</literal> and <literal>SET DEFAULT</literal>. In this case, <literal>CASCADE</literal> means that the updated values of the referenced column(s) should be copied into the referencing row(s). </para> diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index bc5dcba59ce..8f14e4a5c4a 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -138,7 +138,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM <phrase><replaceable class="parameter">referential_action</replaceable> in a <literal>FOREIGN KEY</literal>/<literal>REFERENCES</literal> constraint is:</phrase> -{ NO ACTION | RESTRICT | CASCADE | SET NULL | SET DEFAULT } +{ NO ACTION | RESTRICT | CASCADE | SET NULL [ ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] | SET DEFAULT [ ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] } </synopsis> </refsynopsisdiv> diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 57d51a676a7..b97bb9ded15 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -108,7 +108,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM <phrase><replaceable class="parameter">referential_action</replaceable> in a <literal>FOREIGN KEY</literal>/<literal>REFERENCES</literal> constraint is:</phrase> -{ NO ACTION | RESTRICT | CASCADE | SET NULL | SET DEFAULT } +{ NO ACTION | RESTRICT | CASCADE | SET NULL [ ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] | SET DEFAULT [ ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] } </synopsis> </refsynopsisdiv> @@ -1169,19 +1169,23 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </varlistentry> <varlistentry> - <term><literal>SET NULL</literal></term> + <term><literal>SET NULL [ ( <replaceable>column_name</replaceable> [, ... ] ) ]</literal></term> <listitem> <para> - Set the referencing column(s) to null. + Set all of the referencing columns, or a specified subset of the + referencing columns, to null. A subset of columns can only be + specified for <literal>ON DELETE</literal> actions. </para> </listitem> </varlistentry> <varlistentry> - <term><literal>SET DEFAULT</literal></term> + <term><literal>SET DEFAULT [ ( <replaceable>column_name</replaceable> [, ... ] ) ]</literal></term> <listitem> <para> - Set the referencing column(s) to their default values. + Set all of the referencing columns, or a specified subset of the + referencing columns, to their default values. A subset of columns + can only be specified for <literal>ON DELETE</literal> actions. (There must be a row in the referenced table matching the default values, if they are not null, or the operation will fail.) </para> @@ -2224,6 +2228,16 @@ CREATE TABLE cities_partdef </refsect2> <refsect2> + <title>Foreign-Key Constraint Actions</title> + + <para> + The ability to specify column lists in the foreign-key actions + <literal>SET DEFAULT</literal> and <literal>SET NULL</literal> is a + <productname>PostgreSQL</productname> extension. + </para> + </refsect2> + + <refsect2> <title><literal>NULL</literal> <quote>Constraint</quote></title> <para> |