第12章-约束-《SQLServer2005程序设计语言》

SQLServer2005数据库程序设计第12章约束第12章约束12.1约束概述12.2设计约束12.3主键约束12.4外键约束12.5UNIQUE约束12.6CHECK约束12.7DEFAULT约束12.8禁用约束12.1.1数据完整性数据完整性指的是数据库中存储的数据的一致性和准确性数据完整性的类型：域完整性、实体完整性、引用完整性实体完整性实体（或表）完整性要求表中的所有行都有一个惟一的标识符，称为主键值域完整性域（或列）完整性是指对列指定一组有效的值并决定是否可为空值引用完整性引用完整性确保主键（在被引用表中）和外键（在引用表中）之间的关系得到保持用户定义完整性12.1.2约束的概念和类型完整性类型约束类型描述域DEFAULT指定列的默认值CHECK指定列的允许值FOREIGNKEY指定必须存在值的列NULL指定是否允许为NULL实体PRIMARYKEY唯一标识每一行UNIQUE防止非主键重复引用FOREIGNKEY定义值与同一个表或另一个表的主键值匹配的一列或多列组合CHECK指定根据同一个表中其他列的值可在列中接受的数据值域（或列）完整性实体（或行）完整性引用完整性（在表或者同一个表的列之间）12.1.2约束的概念和类型实施数据完整性的选项机制描述数据类型定义可存储在列中的数据类型规则定义可插入到列中的可接受的值默认值定义如果值没有指定的时候列的值约束定义数据库引擎如何强制数据完整性触发器定义当表被修改的时候自动执行的代码XML构架定义XML文档的命名空间、结构和可接受内容

强制数据完整性声明式数据完整性作为对象定义的一部分来定义数据必须达到的标准SQLServer自动强制完整性通过使用约束、默认和规则来实现过程式数据完整性在脚本中定义数据必须达到的标准通过使用触发器和存储过程来实现可在客户端或服务器用其他编程语言和工具来实现强制数据完整性的首选方法使用约束的注意事项可直接在表上创建、更改和删除约束，而不必删除并重建表应该在应用程序和事务内建立错误检查逻辑，以测试是否违反了某个约束SQLServer里的约束只是“最后防线”当给一个表添加约束的时候，SQLServer将检查现有数据是否违反约束建议创建约束的时候指定名称，否则系统将为约束自动产生一个复杂的名称名称必须惟一，且符合SQLServer标识符的规则查看约束的信息系统存储过程：sp_helpconstraint、sp_help第12章约束12.1约束概述12.2设计约束12.3主键约束12.4外键约束12.5UNIQUE约束12.6CHECK约束12.7DEFAULT约束12.8禁用约束12.2设计约束使用CREATETABLE或者ALTERTABLECREATETABLE是在创建表时创建约束ALTERTABLE是在一个已有的表上创建约束可以添加约束到已有数据的表上可添加单列或多列约束若约束应用于单列，称为列级约束若约束引用了多列，称为表级约束，即使它并没有引用表中的所有列第12章约束12.1约束概述12.2设计约束12.3主键约束12.4外键约束12.5UNIQUE约束12.6CHECK约束12.7DEFAULT约束12.8禁用约束12.3主键约束PRIMARYKEY约束在表中定义了一个惟一标识每一列的主键 语法：[CONSTRAINT约束名]PRIMARYKEY[CLUSTERED|NONCLUSTERED]{(列[,...n])}应用PRIMARYKEY约束的注意事项每张表只能有一个PRIMARYKEY约束输入的值必须是惟一的不允许空值将在指定列上创建惟一索引可以指定聚集索引和非聚集索引（如果先前不存在聚集索引，那么默认是聚集索引）PRIMARYKEY约束示例在Customers表上创建PRIMARYKEY约束，指明表的主键值是CustomerID，并且创建非聚集索引以强制约束USENorthwindALTERTABLEdbo.Customers

ADDCONSTRAINTPK_CustomersPRIMARYKEYNONCLUSTERED(CustomerID)12.3主键约束第12章约束12.1约束概述12.2设计约束12.3主键约束12.4外键约束12.5UNIQUE约束12.6CHECK约束12.7DEFAULT约束12.8禁用约束12.4外键约束FOREIGNKEY约束：定义到同表或其他表中具有PRIMARYKEY或者UNIQUE约束(即唯一性)的列的引用

语法：[CONSTRAINT约束名][FOREIGNKEY][(列[,…n])]

REFERENCES引用表

[(引用列

[,…n])]具有FOREIGNKEY约束的列的取值范围只能是被引用的列的列值应用FOREIGNKEY约束的注意事项提供了单列或多列的引用完整性。FOREIGNKEY子句中指定的列的个数和数据类型必须和REFERENCES子句中指定的列的个数和数据类型匹配并不自动创建索引修改数据的时候，用户必须在被FOREIGNKEY约束引用的表上具有SELECT或REFERENCES权限若引用的是同表中的列，那么可只用REFERENCES子句而省略FOREIGNKEY子句12.4外键约束FOREIGNKEY约束的示例使用FOREIGNKEY约束，确保Orders表中的客户标识与Customers表中的有效的客户标识相关联USENorthwindALTERTABLEdbo.Orders

ADDCONSTRAINTFK_Orders_CustomersFOREIGNKEY(CustomerID)REFERENCESdbo.Customers(CustomerID)12.4外键约束级联引用完整性FOREIGNKEY约束包含一个CASCADE选项，允许对一个定义了UNIQUE或者PRIMARYKEY约束的列的值的修改自动传播到引用它的外键上，这个动作称为级联引用完整性

语法：[CONSTRAINT约束名][FOREIGNKEY][(列[,…n])]

REFERENCES引用表

[(引用列

[,…n])].

[ONDELETE{CASCADE|NOACTION}]

[ONUPDATE{CASCADE|NOACTION}]NOACTION：任何企图删除或者更新被其他表的外键所引用的键都将引发一个错误，对数据的改变会被回滚。NOACTION是默认值CASCADE：若父表中的行变化了，则引用表中相应的行也自动变化级联引用完整性Cascade选项UPDATE行为DELETE行为NOACTION

(Default)引发错误；回滚操作CASCADE在引用表中更新外键在引用表中删除行SETNULL在引用表中设置外键为NULLSETDEFAULT在引用表中设置外键为DEFAULT值FOREIGNKEY约束包含一个CASCADE选项级联引用完整性（续）应用CASCADE选项的注意事项可在多个具有引用关系的表之间组合CASCADE和NOACTION选项。若SQLServer遇到NOACTION，则中断并回滚所有相关的CASCADE动作CASCADE选项不能对定义为rowversion

数据类型的外键或主键列指定示例向order表中添加FOREIGNKEY约束，该表级联任何对customer中cust_id的更新，但是不允许在customer中删除任何被引用的行CREATETABLEorders(order_id

intNOTNULLIDENTITYPRIMARYKEY

cust_id

intNOTNULLREFERENCES

Customer(cust_id)ONUPDATECASCADEONDELETENOACTION)

级联引用完整性（续）第12章约束12.1约束概述12.2设计约束12.3主键约束12.4外键约束12.5UNIQUE约束12.6CHECK约束12.7DEFAULT约束12.8禁用约束12.5UNIQUE约束UNIQUE约束指明列中的任意两行不能有相同的值

语法：[CONSTRAINT约束名]UNIQUE[CLUSTERED|NONCLUSTERED]{(列[,...n])}应用UNIQUE约束的注意事项允许一个空值在一个表上允许多个UNIQUE约束可在一个或者多个列上定义是通过一个惟一索引强制约束的PRIMARYKEY和UNIQUE约束声明PRIMARYKEY或UNIQUE约束的结果只是自动创建了一个指定列上的惟一索引。可空性PRIMARYKEY的各个列必须声明为NOTNULL，而UNIQUE的各个列可以声明为允许NULL值在惟一索引中，认为所有的NULL值是相等的索引的属性PRIMARYKEY约束所创建的惟一索引默认为CLUSTERED，除非表中另外一列已经声明为CLUSTEREDUNIQUE约束所创建的惟一索引默认为NONCLUSTERED选择键不要使用float或real数据类型的列作为主键12.5UNIQUE约束UNIQUE约束示例在Suppliers表的公司名列上创建UNIQUE约束USENorthwindALTERTABLEdbo.Suppliers

ADDCONSTRAINTU_CompanyName

UNIQUENONCLUSTERED(CompanyName)12.5UNIQUE约束第12章约束12.1约束概述12.2设计约束12.3主键约束12.4外键约束12.5UNIQUE约束12.6CHECK约束12.7DEFAULT约束12.8禁用约束12.6CHECK约束限制输入到指定列的值只能为某些特定值

语法：[CONSTRAINT约束名]CHECK(逻辑表达式)

两种强制域完整性的方法：CHECK约束和规则CHECK约束定义了一个表达式，若数据修改语句使得表达式值为FALSE的话，将拒绝语句执行规则的功能和CHECK约束基本相同，除了语法不同，能力稍弱。规则是为了向下兼容而保留的规则可定义一次，然后对多个列分别绑定；而CHECK约束则需要对每个列定义。但CHECK约束的功能略强一些（例如引用同行中其他列的值和调用系统函数等）CHECK约束示例为Employees表中的BrithDate

增加CHECK约束，使出生日期处于可接受的日期范围内USENorthwind

ALTERTABLEdbo.Employees

ADD

CONSTRAINTCK_birthdate

CHECK(BirthDate>'01-01-1900'ANDBirthDate<getdate())12.6CHECK约束使用规则规则：规则指定了能插入列的可接受的值。它确保数据在指定值域内，匹配某个模式，或者匹配指定列表中的项。 语法：创建：CREATERULE

规则AS条件表达式 删除：DROPRULE

规则

[,...n]

绑定规则：sp_bindrule

分离已绑定的规则：sp_unbindrule关于规则的注意事项规则定义可以包含任何在WHERE子句中有效的表达式一个列或者用户定义数据类型只能被一个规则绑定使用规则例：创建规则CREATETABLEcust_sample(cust_id

intPRIMARYKEY,

cust_namechar(50));GOCREATERULEid_chkAS@idBETWEEN0and10000;绑定规则sp_bindrule

id_chk,'cust_sample.cust_id';GOsp_unbindrule'cust_sample.cust_id';例：创建规则绑定规则EXECsp_bindrule'score_rule',‘SC.score'使用规则CREATERULEscore_rule

AS@score>=0AND@score<=100

关于规则的注意事项创建的规则对先前已经存在于数据库中的数据无效一个列或者用户定义数据类型只能被一个规则绑定规则表达式的类型必须和列的数据类型兼容规则定义可以包含任何在WHERE子句中有效的表达式如果列同时有默认值和规则与之关联，则默认值必须满足规则的定义，与规则冲突的默认值不能插入列使用规则第12章约束12.1约束概述12.2设计约束12.3主键约束12.4外键约束12.5UNIQUE约束12.6CHECK约束12.7DEFAULT约束12.8禁用约束12.7DEFAULT约束如果一个列的值在INSERT语句中没有指定，DEFAULT约束将自动输入一个值，可以是预先指定的常量、NULL或者一个系统函数运行时的值 语法：[CONSTRAINT约束名]DEFAULT约束表达式

创建DEFAULT约束的两种方法创建一个默认（CREATEDEFAULT），然后使用存储过程sp_bindefault

将默认绑定到一个列CREATETABLE或ALTERTABLE时使用DEFAUTL约束应用DEFAULT约束的注意事项DEFAULT约束创建时将检查表中的现存数据DEFAULT约束只对INSERT语句有效每列只能定义一个DEFAULT约束有可能会和CHECK约束冲突为具有PRIMARYKEY或UNIQUE约束的列指定默认值是没有意义的常量值外面可以加或者不加括号，字符或者日期常量必须加上单引号或双引号12.7DEFAULT约束使用默认值默认值和规则是可以绑定到一个或多个列或用户定义数据类型的对象，使得可以一次定义，多次使用。默认值：若插入数据的时候没有指定某列的值，则此列绑定的默认值将提供一个默认值。

语法：创建：CREATEDEFAULT默认值AS常量表达式 删除：DROPDEFAULT默认值[,...n]

绑定一个默认： sp_bindefault

解除默认值绑定：sp_unbindefault使用默认值创建默认值的注意事项列的默认值必须符合绑定到此列上的任何规则列的默认值必须符合此列上的任何CHECK约束不能为已有默认绑定的列或用户定义数据类型创建DEFAULT约束createtabletable2(snochar(10),snamevarchar(10),sexchar(2))goaltertabletable2addconstraintDF_Snamedefault'UNKNOWN'forsname/*或者 createdefaultDF_Snameas'UNKNOWN'

sp_bindefaultDF_Sname,'table2.sname‘ */GOaltertabletable2addconstraintDF_SEXdefault'男'forsex/*或者 createdefaultDF_SEXas'男'

sp_bindefaultDF_SEX,'table2.sex‘ */insertintotable2defaultvaluesinsertintotable2values('0621010001','张三',default)第12章约束12.1约束概述12.2设计约束12.3主键约束12.4外键约束12.5UNIQUE约束12.6CHECK约束12.7DEFAULT约束12.8禁用约束12.8禁用约束禁用现有数据上的约束检查在加载新数据时禁用约束检查禁用现有数据上的约束检查当在表上添加约束的时候，我们可以禁用对已有数据的约束检查 语法：AL