sql数据库完整性约束

第5章数据库完整性,场景：学校在进行每学期的奖学金评选的时候，要求学生通过校内网站提交奖学金申请表，这个表中数据将会加入到学生数据库中，作为评选依据。学校的学籍管理办公室数据库管理员抱怨，每次都有学生输入了错误的数据，如：错误的专业名称、和学籍档案不一致的姓名、错误的导师名称等等，有时也会因网络超时，相同的信息重复的输入多次，这样会增加了更多的管理工作。若你是学校的数据库管理人员认为可由什么方法解决这个问题，提高效率呢？通过实现约束来解决这个问题。约束是SQLServer提供的自动保持数据库完整性的一种方法，它通过限制字段中数据、记录中数据和表之间的数据来保证数据的完整性。在SQLSERVER中，对于基本表的约束分为列约束和表约束。,什么是数据库完整性数据库的完整性是指数据的正确性和相容性数据库完整性是防止不合语义或不正确的数据进入数据库完整性体现了是否真实地反映现实世界例:学生的年龄必须是整数，取值范围为14-29；学生的性别只能是男或女；学生的学号一定是唯一的；学生所在的系必须是学校开设的系；,DBMS维护数据库完整性的机制：1.提供定义完整性约束条件的机制DBMS应提供定义数据库完整性约束条件，并把它们存入数据库中。2.提供完整性检查的方法检查数据是否满足完整性约束条件的机制称为完整性检查。一般在INSERT、UPDATE、DELETE语句执行后开始检查。3.违约处理DBMS若发现用户的操作违背了完整性约束条件，就采取一定的动作以保证数据的完整性，如拒绝执行该操作，或级联执行其他操作。,约束是SQLServer提供的自动保持数据库完整性的一种方法，它通过限制字段中数据、记录中数据和表之间的数据来保证数据的完整性。在SQLSERVER中，对于基本表的约束分为列约束和表约束。列约束是对某一个特定列的约束，包含在列定义中，直接跟在该列的其他定义之后，用空格分隔，不必指定列名；表约束与列定义相互独立，不包括在列定义中，通常用于对多个列一起进行约束，与列定义用“，”分隔，定义表约束时必须指出要约束的那些列的名称。,完整性约束与对象,完整性约束与对象,在SQLServer2008中有6种约束和2种对象：,完整性约束的基本语法格式为：CONSTRAINTconstraint_name（约束名）约束不指定名称时，系统会给定一个名称。,完整性约束,PRIMARYKEY与UNIQUE约束类似，通过建立唯一索引来保证基本表在主键列取值的唯一性，但它们之间存在着很大的区别：,一个表只能有一个PRIMARYKEY约束，但可定义多个UNIQUE约束；对于指定为PRIMARYKEY的一个列或多个列的组合，其中任何一个列都不能出现空值，而对于UNIQUE所约束的唯一键，则允许为空。注意：不能为同一个列或一组列既定义UNIQUE约束，又定义PRIMARYKEY约束；PRIMARYKEY既可用于列约束，也可用于表约束。,5.1PRIMARYKEY约束,PRIMARYKEY约束用于定义基本表的主键，它是惟一确定表中每一条记录的标识符，其值不能为NULL，也不能重复，以此来保证实体的完整性。,主键的创建操作方法有两种：SQLServer管理平台操作法和Transact-SQL语句操作法。（1）SQLServer管理平台操作法（如图示）,（2）使用Transact-SQL语句操作法设置主键约束，其语法形式如下：PRIMARYKEYCONSTRAINTconstraint_namePRIMARYKEY（column_name）,5.1PRIMARYKEY约束,（2）举例：建立一个SCORE表，包括：SNO、CNO和GRADE，定义SNO，CNO共同组成SCORE的主键。,5.1PRIMARYKEY约束,（2）举例：建立一个SCORE表，包括：SNO、CNO和GRADE，定义SNO，CNO共同组成SCORE的主键。CREATETABLESCORE(SNOCHAR(10)NOTNULL,CNOCHAR(10)NOTNULL,GRADENUMERIC(9),CONSTRAINTSC_PRIMPRIMARYKEY(SNO,CNO);,5.1PRIMARYKEY约束,5.2UNIQUE约束,惟一性约束用于指定一个或者多个列的组合值具有惟一性，以防止在列中输入重复的值。定义了UNIQUE约束的那些列称为唯一键，系统自动为唯一键建立唯一索引，从而保证了唯一键的唯一性。当使用惟一性约束时，需要考虑以下几个因素：使用惟一性约束的字段允许为空值；一个表中可以允许有多个惟一性约束；可以把惟一性约束定义在多个字段上；惟一性约束用于强制在指定字段上创建一个惟一性索引；默认情况下，创建的索引类型为非聚集索引。,创建惟一性约束的方法有两种：通过SQLServer管理平台可以完成创建和修改惟一性约束的操作；使用Transact-SQL语句完成惟一性约束的操作。（1）通过SQLServer管理平台可以完成创建和修改惟一性约束的操作,5.2UNIQUE约束,创建惟一性约束的方法有两种：通过SQLServer管理平台可以完成创建和修改惟一性约束的操作；使用Transact-SQL语句完成惟一性约束的操作。（1）通过SQLServer管理平台可以完成创建和修改惟一性约束的操作,使用Transact-SQL语句完成惟一性约束的操作，其语法形式如下：UNIQUECONSTRAINTconstraint_nameUNIQUE（column_name）,举例：建立Department表，dno和dname两列均不为空，后者必须唯一。CREATETABLEDepartment(dnochar(10)NOTNULL,dnamechar(15)NOTNULLUNIQUE);,5.2UNIQUE约束,当使用外部键约束时，应该考虑和注意以下几点：FOREIGNKEY约束确保同一个表或者不同表之间的引用完整性必须引用一个PRIMARYKEY或者UNIQUE约束用户必须在应用表上具有REFERENCES权限一个表中最多可以有31个外部键约束；在临时表中，不能使用外部键约束；主键和外部键的数据类型必须严格匹配。,5.3FOREIGNKEY约束,FOREIGNKEY约束是用于建立和加强两个表数据之间的链接的一列或多列。外部键约束用于强制参照完整性。,使用Transact-SQL语句设置外部键约束，其语法形式如下：CONSTRAINTconstraint_nameFOREIGNKEY（column_name,n）REFERENCESref_table（ref_column,n）,createtablescore(snochar(10)notnullforeignkeyreferencesstudent(sno),cnochar(10)notnullforeignkeyreferencescourse(cno),gradenumeric(9),primarykey(sno,cno);,5.3FOREIGNKEY约束,在SQLServer管理平台中添加外部键约束：,为约束指定有意义的名称无需删除以及重新创建表，即可创建、更改或删除约束在你的应用程序和事务中执行错误检查禁用CHECK和FOREIGNKEY约束：当运行大型批处理作业时提高性能当向表中添加新的约束的时候避免检查已经存在的数据,约束检查的注意事项：,5.4CHECK约束,检查约束对输入列或者整个表中的值设置检查条件，以限制输入值，保证数据库数据的完整性。当使用检查约束时，应该考虑和注意以下几点：一个列级检查约束只能与限制的字段有关；一个表级检查约束只能与限制的表中字段有关；一个表中可以定义多个检查约束；每个CREATETABLE语句中每个字段只能定义一个检查约束；在多个字段上定义检查约束，则必须将检查约束定义为表级约束；当执行INSERT语句或者UPDATE语句时，检查约束将验证数据。,创建检查约束常用的操作方法有如下两种：使用SQLServer管理平台创建检查约束；用Transact-SQL语句创建检查约束。（1）使用SQLServer管理平台创建检查约束,5.4CHECK约束,CREATETABLESCore(SNOCHAR(5),CNOCHAR(5),GRADENUMERIC(9,1),CONSTRAINTSCORE_CHKCHECK(GRADE=0ANDGRADE=100);,用Transact-SQL语句创建检查约束。其语法形式如下：CONSTRAINTconstraint_nameCHECK（logical_expression）CHECK（logical_expression）举例：建立一个SC表，定义SCORE的取值范围为0到100之间。程序清单如下：,5.4CHECK约束,5.5DEFAULT约束,默认约束指定在插入操作中如果没有提供输入值时，则系统自动指定值。默认约束可以包括常量、函数、不带变元的内建函数或者空值。使用默认约束时，应该注意以下几点：（1）每个字段只能定义一个默认约束；（2）如果定义的默认值长于其对应字段的允许长度，那么输入到表中的默认值将被截断；（3）不能加入到带有IDENTITY属性或者数据类型为timestamp的字段上；（4）如果字段定义为用户定义的数据类型，而且有一个默认绑定到这个数据类型上，则不允许该字段有默认约束。,使用SQLServer管理平台创建默认约束：,5.5DEFAULT约束,创建默认约束的Transact-SQL语句操作法。其语法形式如下：CONSTRAINTconstraint_nameDEFAULTconstraint_expressionFORcolumn_nameDEFAULTconstraint_expressionFORcolumn_name举例：为dept字段创建默认约束。程序清单如下：altertabledepartmentaddconstraintdept_defdefault(计算机)fordname,5.5DEFAULT约束,5.6NULL约束,空值约束用来控制是否允许该字段的值为NULL。NULL值不是0也不是空白，更不是填入字符串的“NULL”字符串，而是表示“不知道”、“不确定”或“没有数据”的意思。当某一字段的值一定要输入才有意义的时候，则可以设置为NOTNULL。如主键列就不允许出现空值，否则就失去了唯一标识一条记录的作用。空值约束只能用于定义列约束。,5.6NULL约束,创建空值（NULL）约束常用的操作方法有如下两种：（1）在SQLServer管理平台中添加空值约束；,使用Transact-SQL语句设置空值约束。,使用Transact-SQL语句设置空值约束，其语法形式如下：直接在相应的语句后书写NULL|NOTNULL举例：建立一个S表，包括SNO、SNAME、AGE、SEX和DEPT，对SNO字段进行NOTNULL约束。程序清单如下：CREATETABLESTUDENT(SNOCHAR(10)NOTNULL,SNAMEVARCHAR(20),SAGEINT,SSEXCHAR(2)DEFAULT(男),SDEPTCHAR(10);,5.6NULL约束,5.7默认值对象,在SQLServer中，有两种使用默认值的方法：在创建表时，指定默认值约束。使用SQLServer管理控制器，在设计表时指定默认值。使用CREATETABLE语句中的DEFAULT子句指定默认值。使用CREATEDEFAULT语句创建默认对象，然后使用存储过程sp_bindefault将该默认对象绑定到列上。,默认值对象是单独存储的，删除表的时候，DEFAULT约束会自动删除，但是默认值对象不会被删除。创建默认值对象后，需要将其绑定到某列或者用户自定义的数据类型上。主要操作1.创建默认值对象2.绑定默认值对象3.重命名默认值对象4.解除默认值对象的绑定5.删除默认值对象,5.7默认值对象,1.创建默认对象可以使用CREATEDEFAULT语句创建默认对象。其语法格式如下：CREATEDEFAULTdefaultASconstant_expression,5.7默认值对象,CREATEDEFAULTpeople_sexAS男,2.绑定默认对象默认对象创建后不能使用，必须首先将其绑定到某列或者用户自定义的数据类型上。其使用语法格式如下：sp_bindefaultdefname=default,objname=object_name,futureonly=futureonly_flag,5.7默认值对象,sp_bindefaultpeople_sex,student.ssex,3.重命名默认对象重命名默认对象也是使用sp_rename存储过程来完成的。,5.7默认值对象,4.解除默认对象的绑定解除绑定可以使用sp_unbindefault存储过程。其语法格式如下：sp_unbindefaultobjname=object_name,5.7默认值对象,sp_unbindefaultstudent.ssex,5.删除默认对象在删除默认对象之前，首先要确认默认对象已经解除绑定。删除默认对象使用DROPDEFAULT语句。其语法格式如下：DROPDEFAULTdefault,n,5.7默认值对象,DROPDEFAULTpeople_sex;,5.8规则对象,规则用以限制存储在表中或用户自定义数据类型的值，是独立的数据库对象。将规则绑定到列或用户自定义数据类型时，规则才起作用。表中的每列或每个用户定义数据类型只能和一个规则绑定。但每列可应用多个CHECK约束。如果要删除规则，应确定规则已经解除绑定。,创建规则使用CREATERULE语句，其语法格式如下：CREATERULEruleAScondition_expression其中各参数含义如下：rule是新规则的名称。condition_expression是定义规则的条件。,1创建规则,