SQLServer数据库完整性教学案例探析

岳清

摘要：数据库完整性约束条件是数据模型三要素之一，与数据结构和数据操作一起抽象表示现实世界的数据和信息，数据库完整性是数据库课程的重要组成部分。针对目前高校普遍开设的基于SQL Server数据库课程，分析了数据库完整性约束的重要性，给出了一个包含实体完整性、参照完整性和用户自定义完整性的案例，探索数据库教学方法，为提升数据库能力奠定了基础。

关键词：SQL Server；数据库；完整性；教学；案例

中图分类号：TP311 文献标识码：A 文章编号：1009-3044（2017）26-0001-02

Abstract： The constraints of database integrity is one of the three elements of data model， and together with data structure and manipulation， an abstract representation of the data and information in the real world. The integrity of database is one of the important part of courses about database. For the SQL Server Database courses commonly offered in colleges， the paper analyzes the importance of the constraint of database integrity， offers a case including the entity integrity， referential integrity and user-defined Integrity， and explores into the teaching methods of database to cement the base for improving database capacity.

Key words： SQL Server；Database； integrity； Teaching； Case

1 概述

完整性約束保证当授权用户对数据库进行修改时不会破坏数据的一致性，防止对数据的意外损坏[1]，是保障数据正确性的手段之一。在数据库课程中，完整性约束的设计与实现是教学中不可缺少的一部分，在教学中可以通过案例帮助学生理解完整性约束的重要性和实现方法，本文给出了一个基于SQL Server的数据完整性的案例。

2 数据库完整性案例

对于案例的选择，要优先选择学生熟悉的例子，有助于理解语义，如本文选用了熟悉的学生借阅图书的例子。本例中，一个学校有多名学生，每个学生有唯一的学号；学校的图书馆有若干本书，书有图书编号，有些书如高等数学习题集借阅的人多，相同的书图书馆备有多本，它们有相同的图书编号，数据库要记录相同图书编号的书有几本，未借出在库的有几本；每个借阅的信息和归还的信息要记录在数据库中。

根据以上语义，进行概念模型设计，得到如图1所示的E-R图，从图中可以看出，系统有两个实体：学生和图书，由于一个学生可以借多本书，一本书也可以在不同时间被多个学生借阅，因此两个实体间的关系是多对多（m：n）。E-R模型主要是面向用户的，需要将其转换为具体数据库管理系统支持的数据模型[2]。由这个概念模型转换为关系数据库模型时，转换为3个表：学生实体对应的student表，图书实体对应的book表，表示学生实体和图书实体多对多关系的borrow表。三个表的结构如表1—表3所示。

2.1 实体完整性

一个实体在数据库中表现为表中的一条记录，实体完整性保证表中每个记录是唯一的，通过主键来实现。在student表和book表中，studentID和bookID做主键，保证了唯一。在borrow表中，由于一个学生可以借阅多本书，一本书可以在不同时间被多个学生借阅，因此，studentID和bookID分别做主键都是不合适的。一个学生在不同的时间段可借阅相同的书，例如某同学在大一时借阅了高等数学习题集这本书，在大二可以再次借阅这本书，因此studentID和bookID做复合主键也不合适。在borrow表中可以将studentID、bookID和borrowDate这三个字段做复合主键。在数据库设计中，当复合主键的元素大于等于3时，通常就增加一个没有实际意义的编号字段作为主键，在borrow表中，增加了一个borrowID作为主键，该字段没有其他含义，只是标注记录的唯一性。在教学环节中，可以通过设置borrow表的主键来讲解复合主键的概念，强调复合主键不是多个主键，而是多个字段组成的一个集合，是一个整体。

2.2 参照完整性

参照完整性实现手段是外键，表明了表之间的关系。在borrow表中，studentID一定是学生表存在的studentID，因此，borrow表中的studentID字段作为外键，引用了学生表的studentID。同理，borrow表的bookID也是外键，引用了book表的bookID。

在教学过程中，为了让学生更好地体会什么是外键，可以完成以下2个小实验。

1） 在borrow表中添加一条记录，该记录中的studentID在student表中不存在。测试的结果：记录无法添加到borrow表。

2） 删除student表中的一个学生信息，该学生在borrow表中有借阅信息。测试结果：无法删除，原因是如果删除了，borrow表中对应的记录就失去了意义，无法确定这条借阅信息是那个学生的借阅信息，因为学生的信息已经在student表中删除。endprint

设定参照完整性之后，student表、book表和borrow表之间的关系如图2所示，student表和borrow表是一对多的关系，book表和borrow表也是一对多的关系。

2.3 用户自定义完整性

用户定义完整性涵盖的内容比较多，例如：字段的数据类型、字段的约束、触发器等。

2.3.1 字段的数据类型

在book表中，bookPrice表示圖书的定价，数据类型为float，bookCount和bookInCount表示该本书的数量和目前在库的数量，数据类型为smallint。限定了数据类型就可以确保对应的字段值只能是数值类型的数据，避免由于操作错误输入非数字字符。borrow表的borrowDate和returnDate的数据类型是smalldatetime，不仅避免了非日期型的数据的存储，另一个优点就是在这两个日期型的字段上可以利用SQL Server的内置函数进行计算，如计算图书借阅图书的天数，可以使用DateDiff函数求出borrowDate和returnDate之间的天数。

在教学过程中，应说明数据类型的选择的重要性。许多初学者经常将所有的字段的数据类型都定为char类型，认为这样不容易出错。这种思路是错误的，根据语义为字段选择合适的数据类型，SQL Server有许多内置的函数可以用于不同的数据类型的数据上。

2.3.2 字段的约束

字段的约束是为了保证语义的正确性。例如本例中，相同的图书可以有多本，但是在库的，也就是没有借出的数量应该小于等于该书中的数量，即book表中的应该添加一条约束：“bookInCount<=bookCount”。同理，借阅图书中归还日期不应早与借阅的日期，在borrow表中也要增加一约束：“returnDate is null or returnDate>borrowDate”，表示归还日期或者为空（只借出还没有归还的情况）或者归还日期不早于借出日期。在本例中，student表中studentGender字段的取值只能是“男”或“女”，应该添加约束：“studentGender in （'男'，'女'）”。

教学中要强调数据库设计中字段的约束重要性，这是对语义的保障，这一工作如果不在数据库设计中完成，那么就要在程序设计中完成。在数据库设计阶段完成不仅是工作量小而且也符合规范化设计流程。

2.3.3 触发器

触发器功能强大，能够为数据完整性和系统的总体操作增加极大的灵活性。触发器功能上类似于CHECK约束，但是可以跨表、数据库或服务器工作[3]。通常用触发器来满足业务逻辑，实现完整性。

触发器triborrow在对借阅表borrow添加数据时系统自动触发，统计borrow表中该生，即学号相同的学生的借阅信息中没有归还的日期的记录的个数，也就是该生的正在借阅没有归还的书的数量，当超过5本时，则进行回滚，借阅不成功。否则借阅成功，同时book表中的表示在库数量的字段bookInCount减1。

当还书的时候，修改borrow表，添加returnDate字段的值，同时修改book表中的表示在库数量的字段bookInCount，将其值加1。该触发器的代码如下：

该触发器首先判断是否修改前的returnDate字段为空，而修改后的returnDate字段不为空，也就是在进行还书操作，如果是，book表中的字段表示在库数量的字段值加1。

触发器是教学过程的一个难点，在教学中首先要强调触发器的作用和执行过程，触发器是用来实现用户自定义完整性，触发器的执行不是用户直接调用，而是在对某个表执行特定的操作，如增删改，自动的触发。可以通过以上案例分析触发器是如何实现特定的业务逻辑。其次在编写触发器中，通常会用到系统的两个临时表deleted和inserted，如在本例triUpdateborrow触发器中，要检查修改前的returnDate字段为空，而修改后的returnDate字段不为空，修改前记录信息放在deleted表中，修改后的记录放在了inserted表中。

3 结论

本文通过了一个完整的案例介绍了数据库课程中的数据完整性实现过程。在实际的教学中，教师通过理论讲解、案例分析、设计和实现等多个环节让学生理解数据库完整性的作用和实现方法。为学生今后从事相关数据库开发工作奠定了基础。

参考文献：

[1] Abraham Silberschatz.数据库系统概述[M].北京：机械工业出版社，2006.

[2] 何玉洁，刘福刚.数据库原理及应用[M].北京：人民邮电出版社，2012.

[3] Robert Vieira.SQL Server 2008高级程序设计[M].北京：清华大学出版社，2010.endprint