Sql2005中的约束

在Sql2005中，定义主键和外键之间的约束时，和Sql2000有些不同。主要表现在INSERT And UPDATE Specification属性设置时（点击表之间“带钥匙的连接线”时，属性视图显示）。

其属性分为：NO ACTION 、CASCADE 、SET NULL、SET DEFAULT  4种（默认属性为第一种），那么在更新或删除主键时，外键是如何操作呢？

=======以下摘自msdn=====
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/54ca1d10-5106-4340-bde4-480b83e7e813.htm

The REFERENCES clauses of the tabindex="0" keywords="1e068443-b9ea-486a-804f-ce7b6e048e8b">CREATE TABLE and tabindex="0" keywords="f1745145-182d-4301-a334-18f799d361d1">ALTER TABLE statements support the ON DELETE and ON UPDATE clauses:

• [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
  
  
• [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
  
  

NO ACTION is the default if ON DELETE or ON UPDATE is not specified.

ON DELETE NO ACTION

Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, an error is raised and the DELETE statement is rolled back.

ON UPDATE NO ACTION

Specifies that if an attempt is made to update a key value in a row whose key is referenced by foreign keys in existing rows in other tables, an error is raised and the UPDATE statement is rolled back.

CASCADE, SET NULL and SET DEFAULT allow for deletions or updates of key values to affect the tables defined to have foreign key relationships that can be traced back to the table on which the modification is performed. If cascading referential actions have also been defined on the target tables, the specified cascading actions also apply for those rows deleted or updated. CASCADE cannot be specified for any foreign keys or primary keys that have a timestamp column.

ON DELETE CASCADE

Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows that contain those foreign keys are also deleted.

ON UPDATE CASCADE

Specifies that if an attempt is made to update a key value in a row, where the key value is referenced by foreign keys in existing rows in other tables, all the values that make up the foreign key are also updated to the new value specified for the key.

Note:
CASCADE cannot be specified if a timestamp column is part of either the foreign key or the referenced key.

ON DELETE SET NULL

Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all the values that make up the foreign key in the rows that are referenced are set to NULL. All foreign key columns of the target table must be nullable for this constraint to execute.

ON UPDATE SET NULL

Specifies that if an attempt is made to update a row with a key referenced by foreign keys in existing rows in other tables, all the values that make up the foreign key in the rows that are referenced are set to NULL. All foreign key columns of the target table must be nullable for this constraint to execute.

ON DELETE SET DEFAULT

Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all the values that make up the foreign key in the rows that are referenced are set to their default value. All foreign key columns of the target table must have a default definition for this constraint to execute. If a column is nullable, and there is no explicit default value set, NULL becomes the implicit default value of the column. Any nonnull values that are set because of ON DELETE SET DEFAULT must have corresponding values in the primary table to maintain the validity of the foreign key constraint.

ON UPDATE SET DEFAULT

Specifies that if an attempt is made to update a row with a key referenced by foreign keys in existing rows in other tables, all the values that make up the foreign key in the rows that are referenced are set to their default value. All foreign key columns of the target table must have a default definition for this constraint to execute. If a column is nullable, and there is no explicit default value set, NULL becomes the implicit default value of the column. Any non-null values that are set because of ON UPDATE SET DEFAULT must have corresponding values in the primary table to maintain the validity of the foreign key constraint.

=======================

简单总结：假设a表中为主键，b表中的某列为对应的外键，假设b表中有多行数据通过外键列对应着a中的某行（主键）。删除（更新是一样的）a中该行时，如果约束设置如下，那么：
NO ACTION-------不能删除a中此行，因为存在b中外键对其引用，回滚；
CASCADE --------层叠删除，a中此行删除，b中对应的多行也自动删除<注意：如果b表和c表也存在主外键约束，同时约束也设置为CASCADE时，那么删除操作将往下延伸，即c中的对应b表的数据也被删除>
SET NULL-------- a中此行删除，b中对应的多行数据此外键值设为null （b表此列必须允许null值）
SET DEFAULT --a中此行删除，b中对应的多行数据此外键值设为默认值（设计b表时可指定）