《SQL创建约束》PPT课件.ppt

数据库程序设计SQLServer2000数据库程序设计,第1章SQLServer概述第2章创建和管理数据库第3章创建数据类型和表第4章实现数据完整性第5章Transact-SQL介绍第6章使用Transact-SQL查询工具第7章检索数据第8章数据分组与汇总第9章多表联接第10章子查询第11章修改数据,第12章全文索引查询第13章规划索引第14章创建和维护索引第15章实现视图第16章实现存储过程第17章实现用户定义函数第18章实现触发器第19章多服务器编程第20章优化查询性能第21章分析查询第22章管理事务和锁,第4章实现数据完整性,数据完整性的类型强制数据完整性定义约束约束的类型禁用约束使用默认值和规则决定使用何种强制方法推荐操作,数据完整性的类型,4.1数据完整性的类型,数据完整性指的是数据库中存储的数据的一致性和准确性数据完整性的类型：域完整性、实体完整性、引用完整性域完整性域（或列）完整性是指对列指定一组有效的值并决定是否可为空值实体完整性实体（或表）完整性要求表中的所有行都有一个惟一的标识符，称为主键值引用完整性引用完整性确保主键（在被引用表中）和外键（在引用表中）之间的关系得到保持,第4章实现数据完整性,数据完整性的类型强制数据完整性定义约束约束的类型禁用约束使用默认值和规则决定使用何种强制方法推荐操作,强制数据完整性,声明式数据完整性作为对象定义的一部分来定义数据必须达到的标准SQLServer自动强制完整性通过使用约束、默认和规则来实现过程式数据完整性在脚本中定义数据必须达到的标准在脚本中强制完整性通过使用触发器和存储过程来实现可在客户端或服务器用其他编程语言和工具来实现,4.2强制数据完整性,第4章实现数据完整性,数据完整性的类型强制数据完整性定义约束约束的类型禁用约束使用默认值和规则决定使用何种强制方法推荐操作,定义约束,决定使用何种约束创建约束使用约束的注意事项,4.3定义约束,决定使用何种约束,4.3.1决定使用何种约束,创建约束,使用CREATETABLE或者ALTERTABLECREATETABLE是在创建表时创建约束ALTERTABLE是在一个已有的表上创建约束可以添加约束到已有数据的表上可添加单列或多列约束若约束应用于单列，称为列级约束若约束引用了多列，称为表级约束，即使它并没有引用表中的所有列,4.3.2创建约束,使用约束的注意事项,可直接在表上创建、更改和删除约束，而不必删除并重建表应该在应用程序和事务内建立错误检查逻辑，以测试是否违反了某个约束SQLServer里的约束只是“最后防线”当给一个表添加约束的时候，SQLServer将检查现有数据是否违反约束建议创建约束的时候指定名称，否则系统将为约束自动产生一个复杂的名称名称必须惟一，且符合SQLServer标识符的规则查看约束的信息系统存储过程：sp_helpconstraint、sp_help信息模式视图：check_constraints、referential_constraints、table_constraints系统表：syscomments、sysreferences、sysconstraints,4.3.3使用约束的注意事项,第4章实现数据完整性,数据完整性的类型强制数据完整性定义约束约束的类型禁用约束使用默认值和规则决定使用何种强制方法推荐操作,约束的类型,DEFAULT约束CHECK约束PRIMARYKEY约束UNIQUE约束FOREIGNKEY约束级联引用完整性,4.4约束的类型,DEFAULT约束,如果一个列的值在INSERT语句中没有指定，DEFAULT约束将自动输入一个值，可以是预先指定的常量、NULL或者一个系统函数运行时的值语法：CONSTRAINT约束名DEFAULT约束表达式创建DEFAULT约束的两种方法创建一个默认（CREATEDEFAULT），然后使用存储过程sp_bindefault将默认绑定到一个列CREATETABLE或ALTERTABLE时使用DEFAUTL约束应用DEFAULT约束的注意事项DEFAULT约束创建时将检查表中的现存数据DEFAULT约束只对INSERT语句有效每列只能定义一个DEFAULT约束不能和“标识”属性及rowversion数据类型共同使用,4.4.1DEFAULT约束,DEFAULT约束（续）,应用DEFAULT约束的注意事项（续）允许使用一些系统提供的值：USER、CURRENT_USER、SESSION_USER、SYSTEM_USER及CURRENT_TIMESTAMP有可能会和CHECK约束冲突为具有PRIMARYKEY或UNIQUE约束的列指定默认值是没有意义的常量值外面可以加或者不加括号，字符或者日期常量必须加上单引号或双引号在一个INSERT语句中对一个列,4.4.1DEFAULT约束,DEFAULT约束（续）,DEFAULT约束示例为Northwind数据库中的Customers表的ContactName列创建DEFAULT约束，当INSERT语句中此列的值没有提供的时候，自动使用“UNKNOWN”作为它的值,4.4.1DEFAULT约束,USENorthwindALTERTABLEdbo.CustomersADDCONSTRAINTDF_contactnameDEFAULTUNKNOWNFORContactName,CHECK约束,限制输入到指定列的值只能为某些特定值语法：CONSTRAINT约束名CHECK(逻辑表达式)两种强制域完整性的方法：CHECK约束和规则CHECK约束定义了一个表达式，若数据修改语句使得表达式值为FALSE的话，将拒绝语句执行规则的功能和CHECK约束基本相同，除了语法不同，能力稍弱。规则是为了向下兼容而保留的规则可定义一次，然后对多个列分别绑定；而CHECK约束则需要对每个列定义。但CHECK约束的功能略强一些（例如引用同行中其他列的值和调用系统函数等）,4.4.2CHECK约束,CHECK约束（续）,应用CHECK约束的注意事项在每次执行INSERT或者UPDATE语句的时候校验数据值可以引用同表中的其他列不能应用于数据类型为rowversion的列不能包含子查询可通过执行DBCCCHECKCONSTRAINTS语句来返回违反了约束的列列级CHECK约束可省略名字，让系统自动生成CHECK约束可使用正则表达式表达式可以用AND以及OR连接以表示复杂逻辑表级CHECK约束可引用同行中的多列，但不能跨行或跨表可用CHECK约束来防止输入NULL值，例如CHECK(输入值ISNOTNULL)NULL值可能使表达式值为未知，这时数据修改语句仍可执行CHECK约束中可使用系统函数,4.4.2CHECK约束,CHECK约束（续）,CHECK约束示例为Employees表中的BrithDate增加CHECK约束，使出生日期处于可接受的日期范围内,4.4.2CHECK约束,USENorthwindALTERTABLEdbo.EmployeesADDCONSTRAINTCK_birthdateCHECK(BirthDate01-01-1900ANDBirthDategetdate(),PRIMARYKEY约束,PRIMARYKEY约束在表中定义了一个惟一标识每一列的主键语法：CONSTRAINT约束名PRIMARYKEYCLUSTERED|NONCLUSTERED(列,.n)应用PRIMARYKEY约束的注意事项每张表只能有一个PRIMARYKEY约束输入的值必须是惟一的不允许空值将在指定列上创建惟一索引,4.4.3PRIMARYKEY约束,PRIMARYKEY约束（续）,PRIMARYKEY约束示例在Customers表上创建PRIMARYKEY约束，指明表的主键值是CustomerID，并且创建非聚集索引以强制约束,4.4.3PRIMARYKEY约束,USENorthwindALTERTABLEdbo.CustomersADDCONSTRAINTPK_CustomersPRIMARYKEYNONCLUSTERED(CustomerID),UNIQUE约束,UNIQUE约束指明列中的任意两行不能有相同的值语法：CONSTRAINT约束名UNIQUECLUSTERED|NONCLUSTERED(列,.n)应用UNIQUE约束的注意事项允许一个空值在一个表上允许多个UNIQUE约束可在一个或者多个列上定义是通过一个惟一索引强制约束的,4.4.4UNIQUE约束,UNIQUE约束（续）,PRIMARYKEY和UNIQUE约束声明PRIMARYKEY或UNIQUE约束的结果只是自动创建了一个指定列上的惟一索引，通过惟一索引来确保值的惟一性可空性PRIMARYKEY的各个列必须声明为NOTNULL，而UNIQUE的各个列可以声明为允许NULL值在惟一索引中，认为所有的NULL值是相等的索引的属性PRIMARYKEY约束所创建的惟一索引默认为CLUSTERED，除非表中另外一列已经声明为CLUSTEREDUNIQUE约束所创建的惟一索引默认为NONCLUSTERED选择键保持键的长度尽可能短，必要时可另外创建一个代替键不要使用float或real数据类型的列作为主键,4.4.4UNIQUE约束,UNIQUE约束（续）,UNIQUE约束示例在Suppliers表的公司名列上创建UNIQUE约束,4.4.4UNIQUE约束,USENorthwindALTERTABLEdbo.SuppliersADDCONSTRAINTU_CompanyNameUNIQUENONCLUSTERED(CompanyName),FOREIGNKEY约束,FOREIGNKEY约束：定义到同表或其他表中具有PRIMARYKEY或者UNIQUE约束的列的引用语法：CONSTRAINT约束名FOREIGNKEY(列,n)REFERENCES引用表(引用列,n)具有FOREIGNKEY约束的列的取值范围只能是被引用的列的列值,4.4.5FOREIGNKEY约束,FOREIGNKEY约束（续）,应用FOREIGNKEY约束的注意事项提供了单列或多列的引用完整性。FOREIGNKEY子句中指定的列的个数和数据类型必须和REFERENCES子句中指定的列的个数和数据类型匹配并不自动创建索引修改数据的时候，用户必须在被FOREIGNKEY约束引用的表上具有SELECT或REFERENCES权限若引用的是同表中的列，那么可只用REFERENCES子句而省略FOREIGNKEY子句,4.4.5FOREIGNKEY约束,FOREIGNKEY约束（续）,FOREIGNKEY约束的示例使用FOREIGNKEY约束，确保Orders表中的客户标识与Customers表中的有效的客户标识相关联,4.4.5FOREIGNKEY约束,USENorthwindALTERTABLEdbo.OrdersADDCONSTRAINTFK_Orders_CustomersFOREIGNKEY(CustomerID)REFERENCESdbo.Customers(CustomerID),级联引用完整性,4.4.6级联引用完整性,FOREIGNKEY约束包含一个CASCADE选项，允许对一个定义了UNIQUE或者PRIMARYKEY约束的列的值的修改自动传播到引用它的外键上，这个动作称为级联引用完整性语法：CONSTRAINT约束名FOREIGNKEY(列,n)REFERENCES引用表(引用列,n).ONDELETECASCADE|NOACTIONONUPDATECASCADE|NOACTIONNOACTION：任何企图删除或者更新被其他表的外键所引用的键都将引发一个错误，对数据的改变会被回滚。NOACTION是默认值CASCADE：若父表中的行变化了，则引用表中相应的行也自动变化,级联引用完整性（续）,4.4.6级联引用完整性,应用CASCADE选项的注意事项可在多个具有引用关系的表之间组合CASCADE和NOACTION选项。若SQLServer遇到NOACTION，则中断并回滚所有相关的CASCADE动作CASCADE选项不能对定义为rowversion数据类型的外键或主键列指定,第4章实现数据完整性,数据完整性的类型强制数据完整性定义约束约束的类型禁用约束使用默认值和规则决定使用何种强制方法推荐操作,禁用约束,禁用现有数据上的约束检查在加载新数据时禁用约束检查,4.5禁用约束,禁用现有数据上的约束检查,当在表上添加约束的时候，我们可以禁用对已有数据的约束检查语法：ALTERTABLE表名WITHCHECKWITHNOCHECKADDCONSTRAINT约束名FOREIGNKEY(column,n)REFERENCES引用表(引用列,n)CHECK(搜索条件),4.5.1禁用现有数据上的约束检查,禁用现有数据上的约束检查（续）,对已有数据禁用约束检查的注意事项只能禁用CHECK和FOREIGNKEY约束当为一个已有数据的表添加CHECK或FOREIGNKEY约束的时候，使用WITHNOCHECK选项来禁用对已有数据的约束检查当现有数据不再变化的时候，使用WITHNOCHECK选项。若数据被更新，则它的新值必须符合CHECK约束确定禁用约束检查是恰当的。在决定添加约束前，可修改现有数据,4.5.1禁用现有数据上的约束检查,在加载新数据时禁用约束检查,为了避免约束检查的开销，有时候可能希望禁用约束已经确保数据符合约束数据并不符合约束，但稍后可以更改其值并重用约束语法：ALTERTABLE表名CHECK|NOCHECKCONSTRAINTALL|约束名,.n查看约束是启用还是禁用的状态系统存储过程sp_help系统函数OBJECTPROPERTY的CnstIsDisabled属性,4.5.2在加载新数据时禁用约束检查,第4章实现数据完整性,数据完整性的类型强制数据完整性定义约束约束的类型禁用约束使用默认值和规则决定使用何种强制方法推荐操作,使用默认值和规则,默认值和规则是可以绑定到一个或多个列或用户定义数据类型的对象，使得可以一次定义，多次使用。但它们不是ANSI兼容的。默认值：若插入数据的时候没有指定某列的值，则此列绑定的默认值将提供一个默认值。语法：创建：CREATEDEFAULT默认值AS常量表达式删除：DROPDEFAULT默认值,.n绑定一个默认：sp_bindefault解除默认值绑定：sp_unbindefault创建默认值的注意事项列的默认值必须符合绑定到此列上的任何规则列的默认值必须符合此列上的任何CHECK约束不能为已有默认绑定的列或用户定义数据类型创建DEFAULT约束,4.6使用默认值和规则,使用默认和规则（续）,规则：规则指定了能插入列的可接受的值。它确保数据在指定值域内，匹配某个模式，或者匹配指定列表中的项。语法：创建：CREATERULE规则AS条件表达式删除：DROPRULE规则,.n绑定规则：sp_bindrule分离已绑定的规则：sp_unbindrule关于规则的注意事项规则定义可以包含任何在WHERE子句中有效的表达式一个列或者用户定义数据类型只能被一个规则绑定,4.6使用默认和规则,第4章实现数据完整性,数据完整性的类型强制数据完整性定义约束约束的类型禁用约束使用默认值和规则决定使用何种强制方法推荐操作,决定使用何种强制方法,4.7决定使用何种强制方法,应综合考虑功能性和性能开销对于基本的完整性逻辑，例如有效值和维护表间的关系，最好使用声明式完整性约束如果要维护复杂的、大量的、非主键或外键关系一部分的数据，必须使用触发器或存储过程,第4章实现数据完整性,数据完整性的类型强制数据完整性定义约束约束的类型禁用约束使用默认值和规则决定使用何种强制方法推荐操作,推荐操作,4.8推荐操作,目标定义和使用DEFAULT和CHECK约束定义和使用PRIMARYKEY和FOREIGNKEY约束创建和使用SQLServer2000规则和默认值练习1定义DEFAULT约束练习2定义CHECK约束练习3定义PRIMARYKEY约束练习4定义FOREIGNKEY约束可选创建默认值和规则,实验实现数据完整性,回顾,学习完本章后，将能够：描述数据完整性的类型描述强制数据完整性的方法决定使用何种约束和创建约束定义和使用DEFAULT、CHECK、PRIMARYKEY、UNIQUE和FOREIGNKEY约束禁用约束检查描述和使用默认值和规则决定使用何种数据完整性强制方法,