MSSQLSERVER约束及级联操作

新建一张表：员工信息表

createtableEmployees

(

  EmpIdintidentity(1,1),

  EmpNamevarchar(50),

  EmpGenderchar(2),

  EmpAgeint,

  EmpEmailvarchar(100),

  EmpAddressvarchar(500)

)

createtableDepartment

(

  DepIdintidentity(1,1)primarykey,

  DepNamevarchar(50)uniquenotnull 

)

DroptableEmployees

select*fromEmployees

--添加主键约束

altertable Score

addconstraint  PK_Scoreprimary key(sId)

 

--添加唯一约束

altertable student

addconstraint UQ_studentunique(sNo)

--添加默认约束

altertable student

addconstraint DF_studentdefault('男')for sSex

--添加检查约束

altertable student

addconstraint CK_studentcheck(sAge>=18 and sAge<=100)

--添加外键约束（主键表Class外键表student）

altertable student

addconstraint FK_student

foreignkey(sClassId)references Class(cId) --外键student表中的sClassId来references引用主键表中的cid

--级联删除

--ondelete cascade on update cascade

--删除约束

altertable student

dropconstraint FK_student

--用一条语句为表增加多个约束。

altertable Employees add

constraintPK_Employees_EmpIdprimary key(EmpId),--增加主键约束

constraintUQ_Employees_EmpNameunique(EmpName),--增加唯一约束

constraintDF_Employees_EmpGenderdefault('女')for EmpGender,--默认约束

constraintCK_Employees_EmpAgecheck(EmpAge>=0and EmpAge<=120),

constraintFK_Employees_Department_DepIdforeign key(DepId)references Department(DepId)

altertable Employees drop constraint

CK_Employees_EmpAge,

FK_Employees_DepId

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

通过使用级联引用完整性约束，您可以定义当用户试图删除或更新现有外键指向的键时，SQLServer 2005 执行的操作。

CREATETABLE 语句和 ALTERTABLE 语句的 REFERENCES子句支持 ON DELETE 子句和 ON UPDATE 子句。还可以使用“外键关系”对话框定义级联操作：

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

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

--删除原有的外键约束

altertable Employees drop constraint FK_Employees_Department_DepId

--设置主外键关系的级联删除

altertable Employees add constraint FK_Employees_Department_DepId

foreignkey(DepId)references Department(DepId)

ondelete CASCADE --设置级联删除

--onupdate cascade --设置级联更新

如果没有指定ONDELETE 或 ONUPDATE，则默认为NOACTION。

ONDELETE NO ACTION 指定如果试图删除某一行，而该行的键被其他表的现有行中的外键所引用，则产生错误并回滚DELETE语句。

ONUPDATE NO ACTION 指定如果试图更新某一行中的键值，而该行的键被其他表的现有行中的外键所引用，则产生错误并回滚UPDATE语句。

CASCADE、SET NULL 和 SET DEFAULT 允许通过删除或更新键值来影响指定具有外键关系的表，这些外键关系可追溯到在其中进行修改的表。如果为目标表也定义了级联引用操作，那么指定的级联操作也将应用于删除或更新的那些行。不能为具有timestamp列的外键或主键指定 CASCADE。

ONDELETE CASCADE 指定如果试图删除某一行，而该行的键被其他表的现有行中的外键所引用，则也将删除所有包含那些外键的行。

ONUPDATE CASCADE 指定如果试图更新某一行中的键值，而该行的键值被其他表的现有行中的外键所引用，则组成外键的所有值也将更新到为该键指定的新值。

注意：如果 timestamp列是外键或被引用键的一部分，则不能指定 CASCADE。

ONDELETE SET NULL 指定如果试图删除某一行，而该行的键被其他表的现有行中的外键所引用，则组成被引用行中的外键的所有值将被设置为NULL。为了执行此约束，目标表的所有外键列必须可为空值。

ONUPDATE SET NULL 指定如果试图更新某一行，而该行的键被其他表的现有行中的外键所引用，则组成被引用行中的外键的所有值将被设置为NULL。为了执行此约束，目标表的所有外键列必须可为空值。

ONDELETE SET DEFAULT 指定如果试图删除某一行，而该行的键被其他表的现有行中的外键所引用，则组成被引用行中的外键的所有值将被设置为它们的默认值。为了执行此约束，目标表的所有外键列必须具有默认定义。如果某个列可为空值，并且未设置显式的默认值，则将使用NULL 作为该列的隐式默认值。因 ONDELETE SET DEFAULT 而设置的任何非空值在主表中必须有对应的值，才能维护外键约束的有效性。

ONUPDATE SET DEFAULT 指定如果试图更新某一行，而该行的键被其他表的现有行中的外键所引用，则组成被引用行中的外键的所有值将被设置为它们的默认值。为了执行此约束，目标表的所有外键列必须具有默认定义。如果某个列可为空值，并且未设置显式的默认值，则将使用NULL 作为该列的隐式默认值。因 ONUPDATE SET DEFAULT 而设置的任何非空值在主表中必须有对应的值，才能维护外键约束的有效性。