第5章 数据库完整性约束

1、第 5 章数据库完整性场景：场景： 学校的学籍管理办公室数据库管理员抱怨，每次都有学生输入了错学校的学籍管理办公室数据库管理员抱怨，每次都有学生输入了错误的数据，如：错误的专业名称、和学籍档案不一致的姓名、错误的导误的数据，如：错误的专业名称、和学籍档案不一致的姓名、错误的导师名称等等，有时也会因网络超时，相同的信息重复的输入多次，这样师名称等等，有时也会因网络超时，相同的信息重复的输入多次，这样会增加了更多的管理工作。会增加了更多的管理工作。 若你是学校的数据库管理人员认为可由什么方法解决这个问题，提若你是学校的数据库管理人员认为可由什么方法解决这个问题，提高效率呢？高效率呢？ 通过通过数据

2、库完整性数据库完整性来解决这个问题。来解决这个问题。n什么是数据库完整性q数据库的完整性是指数据的正确性和相容性q数据库完整性是防止不合语义或不正确的数据进入数据库q完整性体现了是否真实地反映现实世界q例: n学生的年龄必须是整数，取值范围为14-29；n学生的性别只能是男或女；n学生的学号一定是唯一的；n学生所在的系必须是学校开设的系；n DBMS维护数据库完整性的机制：q1.提供定义完整性约束条件的机制 DBMS应提供定义数据库完整性约束条件，并把它们存入数据库中。q2.提供完整性检查的方法 检查数据是否满足完整性约束条件的机制称为完整性检查。一般在INSERT、UPDATE、DELETE

3、语句执行后开始检查。q3.违约处理 DBMS若发现用户的操作违背了完整性约束条件，就采取一定的动作以保证数据的完整性，如拒绝执行该操作，或级联执行其他操作。 约束是约束是SQL Server提供的自动保持数据库完整性的一种方法，它通过限提供的自动保持数据库完整性的一种方法，它通过限制字段中数据、记录中数据和表之间的数据来保证数据的完整性。制字段中数据、记录中数据和表之间的数据来保证数据的完整性。 在在SQL SERVER中，对于基本表的约束分为中，对于基本表的约束分为列约束和表约束列约束和表约束。列约束是对某一个特定列的约束，包含在列定义中，直接跟在该列的其列约束是对某一个特定列的约束，包含在

4、列定义中，直接跟在该列的其他定义之后，用空格分隔，不必指定列名；表约束与列定义相互独立，他定义之后，用空格分隔，不必指定列名；表约束与列定义相互独立，不包括在列定义中，通常用于对多个列一起进行约束，与列定义用不包括在列定义中，通常用于对多个列一起进行约束，与列定义用“，”分隔，定义表约束时必须指出要约束的那些列的名称。分隔，定义表约束时必须指出要约束的那些列的名称。 完整性约束与对象完整性约束与对象类型约束关键字名称描述实体PRIMARY KEY主键约束主键约束唯一标识每一行唯一标识每一行UNIQUE惟一约束惟一约束防止非主键重复防止非主键重复引用FOREIGN KEY外键约束外键约束定义值与

5、同一个表或另一个表的定义值与同一个表或另一个表的主键值匹配的一列或多列组合主键值匹配的一列或多列组合 域CHECK检查约束检查约束指定列的允许值指定列的允许值DEFAULT默认值约束默认值约束指定列的默认值指定列的默认值NULL空值约束空值约束指定是否允许为指定是否允许为NULL对象DEFAULTRULE默认值对象默认值对象规则对象规则对象 完整性约束与对象完整性约束与对象在在SQL Server 2008中有中有6种约束和种约束和2种对象：种对象：完整性约束的基本语法格式为：完整性约束的基本语法格式为： CONSTRAINT constraint_name（约束名）（约束名） 约束不指定名称

6、时，系统会给定一个名称。约束不指定名称时，系统会给定一个名称。 完整性约束完整性约束 PRIMARY KEY 与与 UNIQUE 约束类似，通过建立唯一索引来保证基本约束类似，通过建立唯一索引来保证基本表在主键列取值的唯一性，但它们之间存在着很大的区别：表在主键列取值的唯一性，但它们之间存在着很大的区别：一个表只能有一个一个表只能有一个 PRIMARY KEY 约束约束 ，但可定义多个，但可定义多个UNIQUE约束；约束；对于指定为对于指定为PRIMARY KEY的一个列或多个列的组合，其中任何一个的一个列或多个列的组合，其中任何一个列都不能出现空值，而对于列都不能出现空值，而对于UNIQUE

7、所约束的唯一键，则允许为空。所约束的唯一键，则允许为空。注意：注意：不能为同一个列或一组列既定义不能为同一个列或一组列既定义UNIQUE约束，又定义约束，又定义PRIMARY KEY约束；约束； PRIMARY KEY既可用于列约束，也可用于表约束。既可用于列约束，也可用于表约束。 5.1 PRIMARY KEY 约束约束 PRIMARY KEY 约束用于定义基本表的主键，它是惟一确定表中每一条记约束用于定义基本表的主键，它是惟一确定表中每一条记录的标识符，其值不能为录的标识符，其值不能为NULL，也不能重复，以此来保证实体的完整性。，也不能重复，以此来保证实体的完整性。 主键的创建操作方法有

8、两种：主键的创建操作方法有两种：SQL Server管理平台操作法和管理平台操作法和Transact-SQL 语句操作法。语句操作法。 （1） SQL Server管理平台操作法（如图示）管理平台操作法（如图示）（2）使用）使用Transact-SQL语句操作法设置主键约束，其语法形式如下：语句操作法设置主键约束，其语法形式如下： PRIMARY KEY CONSTRAINT constraint_name PRIMARY KEY （ column_name ） 5.1 PRIMARY KEY 约束约束（2）举例：）举例：建立一个建立一个SCORE表，包括：表，包括：SNO、CNO和和GRAD

9、E，定义，定义SNO，CNO共同共同组成组成SCORE的主键。的主键。 5.1 PRIMARY KEY 约束约束（2）举例：）举例：建立一个建立一个SCORE表，包括：表，包括：SNO、CNO和和GRADE，定义，定义SNO，CNO共同组成共同组成SCORE的主键。的主键。CREATE TABLE SCORE(SNO CHAR(10) NOT NULL,CNO CHAR(10) NOT NULL,GRADE NUMERIC(9),CONSTRAINT SC_PRIM PRIMARY KEY (SNO,CNO); 5.1 PRIMARY KEY 约束约束5.2 UNIQUE约束约束 惟一性约束用

10、于指定一个或者多个列的组合值具有惟一性，以防止惟一性约束用于指定一个或者多个列的组合值具有惟一性，以防止在列中输入重复的值。定义了在列中输入重复的值。定义了UNIQUE约束的那些列称为唯一键，系统自约束的那些列称为唯一键，系统自动为唯一键建立唯一索引，从而保证了唯一键的唯一性。动为唯一键建立唯一索引，从而保证了唯一键的唯一性。 当使用惟一性约束时，需要考虑以下几个因素：当使用惟一性约束时，需要考虑以下几个因素：使用惟一性约束的字段允许为空值；使用惟一性约束的字段允许为空值；一个表中可以允许有多个惟一性约束；一个表中可以允许有多个惟一性约束；可以把惟一性约束定义在多个字段上；可以把惟一性约束定义

11、在多个字段上；惟一性约束用于强制在指定字段上创建一个惟一性索引；惟一性约束用于强制在指定字段上创建一个惟一性索引；默认情况下，创建的索引类型为非聚集索引。默认情况下，创建的索引类型为非聚集索引。 创建惟一性约束的方法有两种：通过创建惟一性约束的方法有两种：通过 SQL Server 管理平台可以完成管理平台可以完成创建和修改惟一性约束的操作；使用创建和修改惟一性约束的操作；使用 Transact-SQL 语句完成惟一性约束语句完成惟一性约束的操作。的操作。 （1）通过）通过SQL Server管理平台可以完成创建和修改惟一性约束的操作管理平台可以完成创建和修改惟一性约束的操作5.2 UNIQU

12、E约束约束 创建惟一性约束的方法有两种：通过创建惟一性约束的方法有两种：通过 SQL Server 管理平台可以完成管理平台可以完成创建和修改惟一性约束的操作；使用创建和修改惟一性约束的操作；使用 Transact-SQL 语句完成惟一性约束语句完成惟一性约束的操作。的操作。 （1）通过）通过SQL Server管理平台可以完成创建和修改惟一性约束的操作管理平台可以完成创建和修改惟一性约束的操作使用使用Transact-SQL语句完成惟一性约束的操作，其语法形式如下：语句完成惟一性约束的操作，其语法形式如下： UNIQUE CONSTRAINT constraint_name UNIQUE （

13、 column_name ）举例：举例：建立建立Department表，表， dno和和dname 两列均不为空，后者必两列均不为空，后者必须唯一。须唯一。CREATE TABLE Department(dno char(10) NOT NULL,dname char(15) NOT NULL UNIQUE);5.2 UNIQUE约束约束当使用外部键约束时，应该考虑和注意以下几点：当使用外部键约束时，应该考虑和注意以下几点：l FOREIGN KEY 约束确保同一个表或者不同表之间的引用完整性约束确保同一个表或者不同表之间的引用完整性l 必须引用一个必须引用一个 PRIMARY KEY 或者或

14、者 UNIQUE 约束约束l 用户必须在应用表上具有用户必须在应用表上具有 REFERENCES 权限权限l 一个表中最多可以有一个表中最多可以有 31 个外部键约束；个外部键约束；l 在临时表中，不能使用外部键约束；在临时表中，不能使用外部键约束；l 主键和外部键的数据类型必须严格匹配主键和外部键的数据类型必须严格匹配 。5.3 FOREIGN KEY 约束约束 FOREIGN KEY 约束是用于建立和加强两个表数据之间的链接的一列约束是用于建立和加强两个表数据之间的链接的一列或多列。外部键约束用于强制参照完整性。或多列。外部键约束用于强制参照完整性。在在SQL Server管理平台中添加外

15、部键约束：管理平台中添加外部键约束：为约束指定有意义的名称为约束指定有意义的名称无需删除以及重新创建表，即可创建、更改或删除约束无需删除以及重新创建表，即可创建、更改或删除约束在你的应用程序和事务中执行错误检查在你的应用程序和事务中执行错误检查禁用禁用 CHECK 和和 FOREIGN KEY 约束：约束：当运行大型批处理作业时提高性能当向表中添加新的约束的时候避免检查已经存在的数据约束检查的注意事项：约束检查的注意事项：使用使用Transact-SQL语句设置外部键约束语句设置外部键约束 ，其语法形式如下：，其语法形式如下： CONSTRAINT constraint_name FOREIG

16、N KEY（column_name,n） REFERENCES ref_table （ref_column,n） create table score( sno char(10) not null foreign key references student(sno), cno char(10) not null foreign key references course(cno), grade numeric(9), primary key (sno,cno);5.3 FOREIGN KEY 约束约束5.4 CHECK 约束约束 检查约束对输入列或者整个表中的值设置检查条件，以限制输入值检查

17、约束对输入列或者整个表中的值设置检查条件，以限制输入值，保证数据库数据的完整性。，保证数据库数据的完整性。 当使用检查约束时，应该考虑和注意以下几点：当使用检查约束时，应该考虑和注意以下几点： 一个列级检查约束只能与限制的字段有关；一个表级检查约束只能与限制的表中字段有关； 一个表中可以定义多个检查约束； 每个 CREATE TABLE 语句中每个字段只能定义一个检查约束； 在多个字段上定义检查约束，则必须将检查约束定义为表级约束； 当执行 INSERT 语句或者 UPDATE 语句时，检查约束将验证数据。 创建检查约束常用的操作方法有如下两种创建检查约束常用的操作方法有如下两种 ：使用：使用

18、SQL Server管理平管理平台创建检查约束；用台创建检查约束；用Transact-SQL语句创建检查约束。语句创建检查约束。（1）使用）使用SQL Server管理平台创建检查约束管理平台创建检查约束5.4 CHECK 约束约束CREATE TABLE SCore ( SNO CHAR (5), CNO CHAR (5), GRADE NUMERIC(9,1), CONSTRAINT SCORE_CHK CHECK (GRADE =0 AND GRADE =100 ) ); 用用Transact-SQL语句创建检查约束。其语法形式如下：语句创建检查约束。其语法形式如下： CONSTRAIN

19、T constraint_name CHECK （logical_expression） CHECK （logical_expression）举例：举例：建立一个建立一个SC表，定义表，定义SCORE 的取值范围为的取值范围为0到到100之间。之间。程序清单如下：程序清单如下：5.4 CHECK 约束约束5.5 DEFAULT 约束约束 默认约束指定在插入操作中如果没有提供输入值时，则系统自动指定默认约束指定在插入操作中如果没有提供输入值时，则系统自动指定值。默认约束可以包括常量、函数、不带变元的内建函数或者空值。值。默认约束可以包括常量、函数、不带变元的内建函数或者空值。 使用默认约束时，应

20、该注意以下几点：使用默认约束时，应该注意以下几点：（1）每个字段只能定义一个默认约束；）每个字段只能定义一个默认约束；（2）如果定义的默认值长于其对应字段的允许长度，那么输入到表中的）如果定义的默认值长于其对应字段的允许长度，那么输入到表中的默认值将被截断；默认值将被截断；（3）不能加入到带有）不能加入到带有IDENTITY属性或者数据类型为属性或者数据类型为timestamp的字段上；的字段上；（4）如果字段定义为用户定义的数据类型，而且有一个默认绑定到这个）如果字段定义为用户定义的数据类型，而且有一个默认绑定到这个数据类型上，则不允许该字段有默认约束。数据类型上，则不允许该字段有默认约束。

21、使用使用SQL Server管理平台创建默认约束：管理平台创建默认约束：5.5 DEFAULT 约束约束创建默认约束的创建默认约束的Transact-SQL语句操作法。其语法形式如下：语句操作法。其语法形式如下： CONSTRAINT constraint_name DEFAULT constraint_expression FOR column_name DEFAULT constraint_expression FOR column_name举例：举例：为为 dept 字段创建默认约束。字段创建默认约束。程序清单如下：程序清单如下：alter table departmentadd con

22、straint dept_def default (计算机计算机) for dname 5.5 DEFAULT 约束约束5.6 NULL 约束约束 空值约束用来控制是否允许该字段的值为空值约束用来控制是否允许该字段的值为NULL。NULL值不是值不是0也不是也不是空白，更不是填入字符串的空白，更不是填入字符串的“NULL”字符串，而是表示字符串，而是表示“不知道不知道”、“ 不不确定确定”或或“没有数据没有数据”的意思。的意思。 当某一字段的值一定要输入才有意义的时候，则可以设置为当某一字段的值一定要输入才有意义的时候，则可以设置为 NOT NULL。如主键列就不允许出现空值，否则就失去了唯一

23、标识一条记录的。如主键列就不允许出现空值，否则就失去了唯一标识一条记录的作用。空值约束只能用于定义列约束。作用。空值约束只能用于定义列约束。 5.6 NULL 约束约束创建空值（创建空值（NULL）约束常用的操作方法有如下两种：）约束常用的操作方法有如下两种： （1）在）在SQL Server管理平台中添加空值约束；管理平台中添加空值约束；使用使用Transact-SQL语句设置空值约束。语句设置空值约束。 使用使用Transact-SQL语句设置空值约束，语句设置空值约束， 其语法形式如下：其语法形式如下：直接在相应的语句后书写直接在相应的语句后书写 NULL | NOT NULL 举例：举

24、例： 建立一个建立一个S表，包括表，包括SNO、SNAME、AGE、SEX和和DEPT，对，对SNO字段字段进行进行 NOT NULL 约束。约束。程序清单如下：程序清单如下： CREATE TABLE STUDENT ( SNO CHAR(10) NOT NULL, SNAME VARCHAR(20), SAGE INT, SSEX CHAR(2) DEFAULT (男男) , SDEPT CHAR(10) );5.6 NULL 约束约束5.7 默认值对象默认值对象n在SQL Server中，有两种使用默认值的方法：q在创建表时，指定默认值约束。l使用SQL Server管理控制器，在设计表

25、时指定默认值。l使用CREATE TABLE语句中的DEFAULT子句指定默认值。q使用CREATE DEFAULT语句创建默认对象，然后使用存储过程sp_bindefault将该默认对象绑定到列上。n默认值对象是单独存储的，删除表的时候，DEFAULT约束会自动删除，但是默认值对象不会被删除。n创建默认值对象后，需要将其绑定到某列或者用户自定义的数据类型上。n主要操作q1. 创建默认值对象q2. 绑定默认值对象q3. 重命名默认值对象q4. 解除默认值对象的绑定q5. 删除默认值对象5.7 默认值对象默认值对象1.创建默认对象 可以使用CREATE DEFAULT语句创建默认对象。其语法格式

26、如下： CREATE DEFAULT default AS constant_expression5.7 默认值对象默认值对象CREATE DEFAULT people_sex AS 男2.绑定默认对象 默认对象创建后不能使用，必须首先将其绑定到某列或者用户自定义的数据类型上。其使用语法格式如下：sp_bindefault defname = default, objname = object_name , futureonly = futureonly_flag5.7 默认值对象默认值对象sp_bindefault people_sex, student.ssex3.重命名默认对象 重命名默

27、认对象也是使用sp_rename存储过程来完成的。5.7 默认值对象默认值对象4. 解除默认对象的绑定 解除绑定可以使用sp_unbindefault存储过程。其语法格式如下： sp_unbindefault objname = object_name5.7 默认值对象默认值对象sp_unbindefault student.ssex5. 删除默认对象 在删除默认对象之前，首先要确认默认对象已经解除绑定。删除默认对象使用DROP DEFAULT语句。其语法格式如下： DROP DEFAULT default ,n5.7 默认值对象默认值对象DROP DEFAULT people_sex; 5.8 规则对象规则对象n规则用以限制存储在表中或用户自定义数据类型的值，是独立的数据库对象。n将规则绑定到列或用户自定义数据类型时