SQL Server foreign key constraint no action, cascade, set null, Set Default

Most of the data is written as follows:

 

No action
If you try to delete or modify a row, and the row's key is referenced by the foreign key in the existing row of another table, an error is generated and the delete/update statement is rolled back.
Cascade, set null, and set default
Tables with foreign key relationships can be affected by deleting or updating key values. These foreign key relationships can be traced back to the tables in which they are modified. If cascade reference is also defined for the target table, the specified cascade operation will also be applied to the rows to be deleted or updated. Cascade cannot be specified for a foreign key or primary key with a timestamp column.
On Delete Cascade
Specify that if you try to delete a row, and the row's key is referenced by the foreign key in the existing row of other tables, all rows containing those foreign keys will also be deleted.
On update Cascade
Specifies whether to update the key value in a row
The key value of this row is referenced by the foreign key in the existing row of other tables. All values that constitute the foreign key are updated to the new value specified for this key. (If the timestamp column is a foreign key or a part of the referenced key, cascade cannot be specified. )
On Delete set null
Specify that if you try to delete a row, and the row's key is referenced by the foreign key in the existing row of another table, all values that constitute the foreign key in the referenced row will be set to null. All foreign key columns in the target table must be null. This constraint can only be executed.
On update set null
Specifies whether to update a row
The key of the row is referenced by the foreign key in the existing row of other tables. All values that constitute the foreign key in the referenced row are set to null. All foreign key columns in the target table must be null. This constraint can only be executed.
On Delete set default
Specify whether to delete a row
The keys of this row are referenced by the foreign keys in the existing rows of other tables. All values that constitute the Foreign keys in the referenced rows are set to their default values. All foreign key columns of the target table must have
This constraint can be executed only when the default value is defined. If a column can be null without an explicit default value, null is used as the implicit default value for the column. Because on Delete
Any non-null value set by set default must have a corresponding value in the master table to maintain the validity of the foreign key constraint.
On update set default
Specifies whether to update a row
The keys of this row are referenced by the foreign keys in the existing rows of other tables. All values that constitute the Foreign keys in the referenced rows are set to their default values. All foreign key columns of the target table must have
This constraint can be executed only when the default value is defined. If a column can be null without an explicit default value, null is used as the implicit default value for the column. Because on Update
Any non-null value set by set default must have a corresponding value in the master table to maintain the validity of the foreign key constraint.

 

Have you seen the special texts in the above colors? Is it true that on update set null or default can be used without changing the key value of the primary table (that is, as long as you change any column of the non-primary key ), what if the corresponding foreign key value in the sub-table is changed to null or default?

The answer is no. You must change the key value to make it take effect.

Therefore, we recommend that you read the original and Reprinted Words carefully and write the words carefully. Although practice is easy to know, we must pursue correctness and rigor as materials. Msdn simply does not write the key value, all of which only writes: "If the corresponding row in the parent table is updated", although it is understandable, it is not clear: "The key value must be updated ". However, it is even more confusing to write some unwritten materials.

In addition, sqlserver 2000 only supports no action | cascade, and later versions only support set null | set default.