sql数据库完整性约束

第5章数据库完整性场景：学校在进行每学期的奖学金评选的时候，要求学生通过校内网站提交奖学金申请表，这个表中数据将会加入到学生数据库中，作为评选依据。学校的学籍管理办公室数据库管理员抱怨，每次都有学生输入了错误的数据，如：错误的专业名称、和学籍档案不一致的姓名、错误的导师名称等等，有时也会因网络超时，相同的信息重复的输入多次，这样会增加了更多的管理工作。若你是学校的数据库管理人员认为可由什么方法解决这个问题，提高效率呢？通过实现约束来解决这个问题。约束是SQLServer提供的自动保持数据库完整性的一种方法，它通过限制字段中数据、记录中数据和表之间的数据来保证数据的完整性。在SQLSERVER中，对于基本表的约束分为列约束和表约束。什么是数据库完整性数据库的完整性是指数据的正确性和相容性数据库完整性是防止不合语义或不正确的数据进入数据库完整性体现了是否真实地反映现实世界例:学生的年龄必须是整数，取值范围为14-29；学生的性别只能是男或女；学生的学号一定是唯一的；学生所在的系必须是学校开设的系；DBMS维护数据库完整性的机制：1.提供定义完整性约束条件的机制DBMS应提供定义数据库完整性约束条件，并把它们存入数据库中。2.提供完整性检查的方法检查数据是否满足完整性约束条件的机制称为完整性检查。一般在INSERT、UPDATE、DELETE语句执行后开始检查。3.违约处理DBMS若发现用户的操作违背了完整性约束条件，就采取一定的动作以保证数据的完整性，如拒绝执行该操作，或级联执行其他操作。约束是SQLServer提供的自动保持数据库完整性的一种方法，它通过限制字段中数据、记录中数据和表之间的数据来保证数据的完整性。在SQLSERVER中，对于基本表的约束分为列约束和表约束。列约束是对某一个特定列的约束，包含在列定义中，直接跟在该列的其他定义之后，用空格分隔，不必指定列名；表约束与列定义相互独立，不包括在列定义中，通常用于对多个列一起进行约束，与列定义用“，”分隔，定义表约束时必须指出要约束的那些列的名称。完整性约束与对象类型约束关键字名称描述实体PRIMARYKEY主键约束唯一标识每一行UNIQUE惟一约束防止非主键重复引用FOREIGNKEY外键约束定义值与同一个表或另一个表的主键值匹配的一列或多列组合域CHECK检查约束指定列的允许值DEFAULT默认值约束指定列的默认值NULL空值约束指定是否允许为NULL对象DEFAULTRULE默认值对象规则对象完整性约束与对象在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语句操作法设置主键约束，其语法形式如下：•PRIMARYKEY•CONSTRAINTconstraint_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语句完成惟一性约束的操作，其语法形式如下：•UNIQUE•CONSTRAINTconstraint_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_expression[FORcolumn_name]•DEFAULTconstraint_expression[FORcolumn_name]举例：为dept字段创建默认约束。程序清单如下：altertabledepartmentaddconstraintdept_defdefault(‘计算机’)fordname5.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将该默认对象绑定到列上。默认