The foreign key constraints in sqlexpress are studied.

Previously, we used databases to Create Table relationships, but these relationships are often not used.

This time I wrote a project that involves the foreign key constraints and cascading deletion of multiple tables.

The sqlexpress provided in vs2005 is used as a database in vs. It doesn't feel very convenient (it's easy to talk about it. Who should I sell sqlserver2005 Dev !)

I have created a table, created a link, and set the foreign key constraint.

Then I thought everything was fine. I went to write code and draw the interface.

After the code and interface are completed, a test is performed to delete the data of a table in the middle of the cascading chain, and an error is reported.

Title The delete statement conflicts with the reference constraint "FK _ course extension info table _ instructor table. This conflict occurs in the Database "X: \ XXX. MDF", table "DBO. Course extension information table", column 'instructor number '.
The statement has been terminated.

Then I studied the foreign key constraints. What is going on? I found a definition on the Internet:

TitleForeign key constraint

• If you enter a non-null value in the foreign key constraint column, this value must exist in the referenced column; otherwise, an error message that violates the foreign key constraint is returned.

• The foreign key constraint is applied to the preceding columns unless the source column is specified.
• The foreign key constraint can only reference tables in the same database on the same server. The integrity of references between databases must be achieved through triggers. For more information, see create trigger.
• Foreign key can reference other columns in the same table (self-reference ).
• The references clause of the column-level foreign key constraint can only list one reference column, and the column must have the same data type as the column defining the constraint.
• The number of referenced columns in the references clause of the table-level foreign key constraint must be the same as the number of columns in the constraint column list. The data type of each referenced column must also be the same as that of the corresponding column in the list.
• If a column of the timestamp type is a foreign key or a part of the referenced key, cascade cannot be specified.
• Cascade and no action can be combined on tables with reference relationships. If SQL Server encounters no action, the execution of the statement is terminated and the related cascade operation is rolled back. When the delete statement causes the combination of cascade and no action, all cascade operations will be executed before SQL Server checks the no action operation.
• A table can contain up to 253 foreign key constraints.
• The foreign key constraint is not mandatory for temporary tables.
• Each table can reference a maximum of 253 different tables in its foreign key constraint.
• The foreign key constraint can only reference columns in the primary key or unique constraint of the referenced table or in the unique index of the referenced table.

Still puzzled.

Right-click the link in the relationship diagram, view the attributes, and find that the "insert and update standard category" contains "no operation", which is probably a problem here.
I searched on msdn and found this article: visualized database tool-foreign key column attribute
MS-help: // Ms. msdnqtr. v80.chs/ms. msdn. v80/ms. visualstudio. v80.chs/dv_vdt01/html/2cb23e81-6342-4390-9d0e-b7a4805eca0f.htm
Here are the key words:

Title

Insert and update specifications

Expand to display the "Delete rule" and "update Rule" information about the link.

Delete rule

Specify what happens when the end user of the database tries to delete a row that contains the data involved in the foreign key relationship. If it is set:

• If no operation is performed, an error message is displayed, indicating that the deletion operation is not allowed and the delete operation will be rolled back.

• Cascade the deletion of all rows that contain the data involved in the foreign key relationship.

• Set null. If all foreign key columns in the table accept null values, set this value to null. Only applicable to SQL Server 2005.

• Set the default value. If all the foreign key columns in the table have defined the default value, set this value to the default value defined by the column. Only applicable to SQL Server 2005.

Update rules

Specifies the situation when the database user attempts to update a row that contains the data involved in the foreign key relationship. If it is set:

• If no operation is performed, an error message is displayed, indicating that the deletion operation is not allowed and the delete operation will be rolled back.

• Cascade the deletion of all rows that contain the data involved in the foreign key relationship.

• Set null. If all foreign key columns in the table accept null values, set this value to null. Only applicable to SQL Server 2005.

• Set the default value. If all the foreign key columns in the table have defined the default value, set this value to the default value defined by the column. Only applicable to SQL Server 2005.

I finally understood:
1. In vs2005, the attribute window of the foreign key constraint is not called "cascade" or "cascade". It is estimated that it is a translation error.
2. In vs2005, create a foreign key constraint in the graph. The "Default insert and update specifications" are "no operation ".
That is to say, if you update or delete it, it will report an error.
3. solution:
A. It is easy to delete, and cascade deletion is enough. But you have to consider it carefully during update.
B. You can simply remove the foreign key constraint, consider it yourself, and then write transcation SQL.