SQL Server foreign key update (delete) Rule

Reprinted: http://database.51cto.com/art/201006/206351.htm

 

This article mainly tells you about the actual operation rules of SQL Server foreign key update (delete) operations. Previously, table relations were generally used in databases, however, these links are often not used after they are created. This time, when I write a project that involves multi-Table SQL Server foreign key constraints and cascading deletion, I have studied the problem.

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.CodeThe interface is painted.

After After the code and interface are completed, delete the data in a table in the middle of the cascade link, and then an error.

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 of SQL Server. What is going on? I found a definition on the Internet:

Title

Foreign key constraint

If a non-null value is input in the foreign key constraint column, this value must exist in the referenced column; otherwise, an error violation error message 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 of SQL Server 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.Article: Visualized database tool-foreign key column attributes

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 SQL Server 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.

Original article title: SQL Server foreign key update (delete) Rule

Connection: http://www.cnblogs.com/yelaiju/archive/2010/05/31/1748380.html

 

------------------------------------------------------

The same is true for SQL Server 2008. I haven't figured out how to select "cascade" for the update rule. I deleted all the data and how can I update it?