Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitc75be2a

Browse files
committed
Change ON UPDATE SET NULL/SET DEFAULT referential actions to meet SQL spec.
Previously, when executing an ON UPDATE SET NULL or SET DEFAULT action fora multicolumn MATCH SIMPLE foreign key constraint, we would set only thosereferencing columns corresponding to referenced columns that were changed.This is what the SQL92 standard said to do --- but more recent versionsof the standard say that all referencing columns should be set to null ortheir default values, no matter exactly which referenced columns changed.At least for SET DEFAULT, that is clearly saner behavior. It's somewhatdebatable whether it's an improvement for SET NULL, but it appears thatother RDBMS systems read the spec this way. So let's do it like that.This is a release-notable behavioral change, although considering thatour documentation already implied it was done this way, the lack ofcomplaints suggests few people use such cases.
1 parentf5297bd commitc75be2a

File tree

5 files changed

+72
-218
lines changed

5 files changed

+72
-218
lines changed

‎doc/src/sgml/ddl.sgml

Lines changed: 24 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -735,7 +735,7 @@ CREATE TABLE t1 (
735735
</para>
736736

737737
<para>
738-
A table cancontain more than one foreign key constraint. This is
738+
A table canhave more than one foreign key constraint. This is
739739
used to implement many-to-many relationships between tables. Say
740740
you have tables about products and orders, but now you want to
741741
allow one order to contain possibly many products (which the
@@ -827,41 +827,52 @@ CREATE TABLE order_items (
827827
row(s) referencing it should be automatically deleted as well.
828828
There are two other options:
829829
<literal>SET NULL</literal> and <literal>SET DEFAULT</literal>.
830-
These cause the referencing columns to be set to nulls or default
830+
These cause the referencing column(s) in the referencing row(s)
831+
to be set to nulls or their default
831832
values, respectively, when the referenced row is deleted.
832833
Note that these do not excuse you from observing any constraints.
833834
For example, if an action specifies <literal>SET DEFAULT</literal>
834-
but the default value would not satisfy the foreign key, the
835+
but the default value would not satisfy the foreign key constraint, the
835836
operation will fail.
836837
</para>
837838

838839
<para>
839840
Analogous to <literal>ON DELETE</literal> there is also
840841
<literal>ON UPDATE</literal> which is invoked when a referenced
841842
column is changed (updated). The possible actions are the same.
843+
In this case, <literal>CASCADE</> means that the updated values of the
844+
referenced column(s) should be copied into the referencing row(s).
842845
</para>
843846

844847
<para>
848+
Normally, a referencing row need not satisfy the foreign key constraint
849+
if any of its referencing columns are null. If <literal>MATCH FULL</>
850+
is added to the foreign key declaration, a referencing row escapes
851+
satisfying the constraint only if all its referencing columns are null
852+
(so a mix of null and non-null values is guaranteed to fail a
853+
<literal>MATCH FULL</> constraint). If you don't want referencing rows
854+
to be able to avoid satisfying the foreign key constraint, declare the
855+
referencing column(s) as <literal>NOT NULL</>.
856+
</para>
857+
858+
<para>
859+
A foreign key must reference columns that either are a primary key or
860+
form a unique constraint. This means that the referenced columns always
861+
have an index (the one underlying the primary key or unique constraint);
862+
so checks on whether a referencing row has a match will be efficient.
845863
Since a <command>DELETE</command> of a row from the referenced table
846864
or an <command>UPDATE</command> of a referenced column will require
847865
a scan of the referencing table for rows matching the old value, it
848-
is often a good idea to index the referencing columns. Because this
866+
is often a good idea to index the referencing columns too. Because this
849867
is not always needed, and there are many choices available on how
850868
to index, declaration of a foreign key constraint does not
851869
automatically create an index on the referencing columns.
852870
</para>
853871

854872
<para>
855873
More information about updating and deleting data is in <xref
856-
linkend="dml">.
857-
</para>
858-
859-
<para>
860-
Finally, we should mention that a foreign key must reference
861-
columns that either are a primary key or form a unique constraint.
862-
If the foreign key references a unique constraint, there are some
863-
additional possibilities regarding how null values are matched.
864-
These are explained in the reference documentation for
874+
linkend="dml">. Also see the description of foreign key constraint
875+
syntax in the reference documentation for
865876
<xref linkend="sql-createtable">.
866877
</para>
867878
</sect2>

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 17 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -585,8 +585,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
585585
These clauses specify a foreign key constraint, which requires
586586
that a group of one or more columns of the new table must only
587587
contain values that match values in the referenced
588-
column(s) of some row of the referenced table. If <replaceable
589-
class="parameter">refcolumn</replaceable> is omitted, the
588+
column(s) of some row of the referenced table. Ifthe<replaceable
589+
class="parameter">refcolumn</replaceable>listis omitted, the
590590
primary key of the <replaceable class="parameter">reftable</replaceable>
591591
is used. The referenced columns must be the columns of a non-deferrable
592592
unique or primary key constraint in the referenced table. Note that
@@ -599,12 +599,16 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
599599
values of the referenced table and referenced columns using the
600600
given match type. There are three match types: <literal>MATCH
601601
FULL</>, <literal>MATCH PARTIAL</>, and <literal>MATCH
602-
SIMPLE</literal>,which isalsothe default. <literal>MATCH
602+
SIMPLE</literal> (which is the default). <literal>MATCH
603603
FULL</> will not allow one column of a multicolumn foreign key
604-
to be null unless all foreign key columns are null.
605-
<literal>MATCH SIMPLE</literal> allows some foreign key columns
606-
to be null while other parts of the foreign key are not
607-
null. <literal>MATCH PARTIAL</> is not yet implemented.
604+
to be null unless all foreign key columns are null; if they are all
605+
null, the row is not required to have a match in the referenced table.
606+
<literal>MATCH SIMPLE</literal> allows any of the foreign key columns
607+
to be null; if any of them are null, the row is not required to have a
608+
match in the referenced table.
609+
<literal>MATCH PARTIAL</> is not yet implemented.
610+
(Of course, <literal>NOT NULL</> constraints can be applied to the
611+
referencing column(s) to prevent these cases from arising.)
608612
</para>
609613

610614
<para>
@@ -652,8 +656,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
652656
<listitem>
653657
<para>
654658
Delete any rows referencing the deleted row, or update the
655-
value of the referencing column to the newvalue of the
656-
referencedcolumn, respectively.
659+
values of the referencing column(s) to the newvalues of the
660+
referencedcolumns, respectively.
657661
</para>
658662
</listitem>
659663
</varlistentry>
@@ -672,6 +676,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
672676
<listitem>
673677
<para>
674678
Set the referencing column(s) to their default values.
679+
(There must be a row in the referenced table matching the default
680+
values, if they are not null, or the operation will fail.)
675681
</para>
676682
</listitem>
677683
</varlistentry>
@@ -680,8 +686,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
680686

681687
<para>
682688
If the referenced column(s) are changed frequently, it might be wise to
683-
add an index to theforeign keycolumn so that referential actions
684-
associated with the foreign keycolumn can be performed more
689+
add an index to thereferencingcolumn(s) so that referential actions
690+
associated with the foreign keyconstraint can be performed more
685691
efficiently.
686692
</para>
687693
</listitem>

‎src/backend/utils/adt/ri_triggers.c

Lines changed: 12 additions & 175 deletions
Original file line numberDiff line numberDiff line change
@@ -207,11 +207,6 @@ static void ri_BuildQueryKeyPkCheck(RI_QueryKey *key,
207207
int32constr_queryno);
208208
staticboolri_KeysEqual(Relationrel,HeapTupleoldtup,HeapTuplenewtup,
209209
constRI_ConstraintInfo*riinfo,boolrel_is_pk);
210-
staticboolri_AllKeysUnequal(Relationrel,HeapTupleoldtup,HeapTuplenewtup,
211-
constRI_ConstraintInfo*riinfo,boolrel_is_pk);
212-
staticboolri_OneKeyEqual(Relationrel,intcolumn,
213-
HeapTupleoldtup,HeapTuplenewtup,
214-
constRI_ConstraintInfo*riinfo,boolrel_is_pk);
215210
staticboolri_AttributesEqual(Oideq_opr,Oidtypeid,
216211
Datumoldvalue,Datumnewvalue);
217212
staticboolri_Check_Pk_Match(Relationpk_rel,Relationfk_rel,
@@ -1950,7 +1945,6 @@ RI_FKey_setnull_upd(PG_FUNCTION_ARGS)
19501945
RI_QueryKeyqkey;
19511946
SPIPlanPtrqplan;
19521947
inti;
1953-
booluse_cached_query;
19541948

19551949
/*
19561950
* Check that this is a valid trigger call on the right time and event.
@@ -1985,7 +1979,7 @@ RI_FKey_setnull_upd(PG_FUNCTION_ARGS)
19851979
/* ----------
19861980
* SQL3 11.9 <referential constraint definition>
19871981
*General rules 7) a) ii) 2):
1988-
*MATCH FULL
1982+
*MATCHSIMPLE/FULL
19891983
*... ON UPDATE SET NULL
19901984
* ----------
19911985
*/
@@ -2026,29 +2020,10 @@ RI_FKey_setnull_upd(PG_FUNCTION_ARGS)
20262020
if (SPI_connect()!=SPI_OK_CONNECT)
20272021
elog(ERROR,"SPI_connect failed");
20282022

2029-
/*
2030-
* "MATCH SIMPLE" only changes columns corresponding to the
2031-
* referenced columns that have changed in pk_rel.This means the
2032-
* "SET attrn=NULL [, attrn=NULL]" string will be change as well.
2033-
* In this case, we need to build a temporary plan rather than use
2034-
* our cached plan, unless the update happens to change all
2035-
* columns in the key.Fortunately, for the most common case of a
2036-
* single-column foreign key, this will be true.
2037-
*
2038-
* In case you're wondering, the inequality check works because we
2039-
* know that the old key value has no NULLs (see above).
2040-
*/
2041-
2042-
use_cached_query= (riinfo.confmatchtype==FKCONSTR_MATCH_FULL)||
2043-
ri_AllKeysUnequal(pk_rel,old_row,new_row,
2044-
&riinfo, true);
2045-
20462023
/*
20472024
* Fetch or prepare a saved plan for the set null update operation
2048-
* if possible, or build a temporary plan if not.
20492025
*/
2050-
if (!use_cached_query||
2051-
(qplan=ri_FetchPreparedPlan(&qkey))==NULL)
2026+
if ((qplan=ri_FetchPreparedPlan(&qkey))==NULL)
20522027
{
20532028
StringInfoDataquerybuf;
20542029
StringInfoDataqualbuf;
@@ -2080,37 +2055,23 @@ RI_FKey_setnull_upd(PG_FUNCTION_ARGS)
20802055

20812056
quoteOneName(attname,
20822057
RIAttName(fk_rel,riinfo.fk_attnums[i]));
2083-
2084-
/*
2085-
* MATCH SIMPLE - only change columns corresponding
2086-
* to changed columns in pk_rel's key
2087-
*/
2088-
if (riinfo.confmatchtype==FKCONSTR_MATCH_FULL||
2089-
!ri_OneKeyEqual(pk_rel,i,old_row,new_row,
2090-
&riinfo, true))
2091-
{
2092-
appendStringInfo(&querybuf,
2093-
"%s %s = NULL",
2094-
querysep,attname);
2095-
querysep=",";
2096-
}
2058+
appendStringInfo(&querybuf,
2059+
"%s %s = NULL",
2060+
querysep,attname);
20972061
sprintf(paramname,"$%d",i+1);
20982062
ri_GenerateQual(&qualbuf,qualsep,
20992063
paramname,pk_type,
21002064
riinfo.pf_eq_oprs[i],
21012065
attname,fk_type);
2066+
querysep=",";
21022067
qualsep="AND";
21032068
queryoids[i]=pk_type;
21042069
}
21052070
appendStringInfoString(&querybuf,qualbuf.data);
21062071

2107-
/*
2108-
* Prepare the plan. Save it only if we're building the
2109-
* "standard" plan.
2110-
*/
2072+
/* Prepare and save the plan */
21112073
qplan=ri_PlanCheck(querybuf.data,riinfo.nkeys,queryoids,
2112-
&qkey,fk_rel,pk_rel,
2113-
use_cached_query);
2074+
&qkey,fk_rel,pk_rel, true);
21142075
}
21152076

21162077
/*
@@ -2463,25 +2424,15 @@ RI_FKey_setdefault_upd(PG_FUNCTION_ARGS)
24632424

24642425
quoteOneName(attname,
24652426
RIAttName(fk_rel,riinfo.fk_attnums[i]));
2466-
2467-
/*
2468-
* MATCH SIMPLE - only change columns corresponding
2469-
* to changed columns in pk_rel's key
2470-
*/
2471-
if (riinfo.confmatchtype==FKCONSTR_MATCH_FULL||
2472-
!ri_OneKeyEqual(pk_rel,i,old_row,new_row,
2473-
&riinfo, true))
2474-
{
2475-
appendStringInfo(&querybuf,
2476-
"%s %s = DEFAULT",
2477-
querysep,attname);
2478-
querysep=",";
2479-
}
2427+
appendStringInfo(&querybuf,
2428+
"%s %s = DEFAULT",
2429+
querysep,attname);
24802430
sprintf(paramname,"$%d",i+1);
24812431
ri_GenerateQual(&qualbuf,qualsep,
24822432
paramname,pk_type,
24832433
riinfo.pf_eq_oprs[i],
24842434
attname,fk_type);
2435+
querysep=",";
24852436
qualsep="AND";
24862437
queryoids[i]=pk_type;
24872438
}
@@ -3857,120 +3808,6 @@ ri_KeysEqual(Relation rel, HeapTuple oldtup, HeapTuple newtup,
38573808
}
38583809

38593810

3860-
/* ----------
3861-
* ri_AllKeysUnequal -
3862-
*
3863-
*Check if all key values in OLD and NEW are not equal.
3864-
* ----------
3865-
*/
3866-
staticbool
3867-
ri_AllKeysUnequal(Relationrel,HeapTupleoldtup,HeapTuplenewtup,
3868-
constRI_ConstraintInfo*riinfo,boolrel_is_pk)
3869-
{
3870-
TupleDesctupdesc=RelationGetDescr(rel);
3871-
constint16*attnums;
3872-
constOid*eq_oprs;
3873-
inti;
3874-
3875-
if (rel_is_pk)
3876-
{
3877-
attnums=riinfo->pk_attnums;
3878-
eq_oprs=riinfo->pp_eq_oprs;
3879-
}
3880-
else
3881-
{
3882-
attnums=riinfo->fk_attnums;
3883-
eq_oprs=riinfo->ff_eq_oprs;
3884-
}
3885-
3886-
for (i=0;i<riinfo->nkeys;i++)
3887-
{
3888-
Datumoldvalue;
3889-
Datumnewvalue;
3890-
boolisnull;
3891-
3892-
/*
3893-
* Get one attribute's oldvalue. If it is NULL - they're not equal.
3894-
*/
3895-
oldvalue=SPI_getbinval(oldtup,tupdesc,attnums[i],&isnull);
3896-
if (isnull)
3897-
continue;
3898-
3899-
/*
3900-
* Get one attribute's newvalue. If it is NULL - they're not equal.
3901-
*/
3902-
newvalue=SPI_getbinval(newtup,tupdesc,attnums[i],&isnull);
3903-
if (isnull)
3904-
continue;
3905-
3906-
/*
3907-
* Compare them with the appropriate equality operator.
3908-
*/
3909-
if (ri_AttributesEqual(eq_oprs[i],RIAttType(rel,attnums[i]),
3910-
oldvalue,newvalue))
3911-
return false;/* found two equal items */
3912-
}
3913-
3914-
return true;
3915-
}
3916-
3917-
3918-
/* ----------
3919-
* ri_OneKeyEqual -
3920-
*
3921-
*Check if one key value in OLD and NEW is equal. Note column is indexed
3922-
*from zero.
3923-
*
3924-
*ri_KeysEqual could call this but would run a bit slower. For
3925-
*now, let's duplicate the code.
3926-
* ----------
3927-
*/
3928-
staticbool
3929-
ri_OneKeyEqual(Relationrel,intcolumn,HeapTupleoldtup,HeapTuplenewtup,
3930-
constRI_ConstraintInfo*riinfo,boolrel_is_pk)
3931-
{
3932-
TupleDesctupdesc=RelationGetDescr(rel);
3933-
constint16*attnums;
3934-
constOid*eq_oprs;
3935-
Datumoldvalue;
3936-
Datumnewvalue;
3937-
boolisnull;
3938-
3939-
if (rel_is_pk)
3940-
{
3941-
attnums=riinfo->pk_attnums;
3942-
eq_oprs=riinfo->pp_eq_oprs;
3943-
}
3944-
else
3945-
{
3946-
attnums=riinfo->fk_attnums;
3947-
eq_oprs=riinfo->ff_eq_oprs;
3948-
}
3949-
3950-
/*
3951-
* Get one attribute's oldvalue. If it is NULL - they're not equal.
3952-
*/
3953-
oldvalue=SPI_getbinval(oldtup,tupdesc,attnums[column],&isnull);
3954-
if (isnull)
3955-
return false;
3956-
3957-
/*
3958-
* Get one attribute's newvalue. If it is NULL - they're not equal.
3959-
*/
3960-
newvalue=SPI_getbinval(newtup,tupdesc,attnums[column],&isnull);
3961-
if (isnull)
3962-
return false;
3963-
3964-
/*
3965-
* Compare them with the appropriate equality operator.
3966-
*/
3967-
if (!ri_AttributesEqual(eq_oprs[column],RIAttType(rel,attnums[column]),
3968-
oldvalue,newvalue))
3969-
return false;
3970-
3971-
return true;
3972-
}
3973-
39743811
/* ----------
39753812
* ri_AttributesEqual -
39763813
*

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp