基于SQL Server 数据库完整性约束的案例教学研究

本栏目责任编辑：王力
计算机教学与教育信息化
基于SQL Server 数据库完整性约束的案例教学研究
魏慧娟，李思，文小爽
（郑州信息工程职业学院，河南郑州450121）
摘要：数据库完整性是DBMS 中衡量数据库存在状态是否合理的重要技术指标。

在SQL Server 数据库中，完整性约束如何
定义和使用是数据库教学环节中的重点和难点。

该文结合案例教学法和项目化教学法，提出了将一个项目贯穿到实体完整性、域完整性、参照完整性、用户自定义完整性的案例设计中，研究出完整性约束的教学方法，能够帮助学生深入透彻地理解完整性约束，为提高学生的实践动手能力和灵活运用数据库技术的能力奠定了基础。

关键词：SQL Server 数据库；完整性约束；案例中图分类号：TP311
文献标识码：A
文章编号：1009-3044(2021)09-0166-03
开放科学（资源服务）标识码（OSID ）：
数据库技术是计算机专业的学生必须掌握的重要技术，而学生在学习数据库技术时，通常觉得完整性约束部分的知识晦涩难懂，只能片面的掌握约束知识点的应用，不能有效地整合知识点的零碎案例，形成一个完整的知识体系。

针对此现象，本文结合一个项目，在SQL Server 数据库的基础上设计了一套完整性约束的教学案例。

1项目概述
对于项目的选择，要贴合学生的现实生活，有助于学生理解。

本文选择基于Java 的网上购物系统的设计与开发项目[1]，该项目主要分为需求分析、设计数据库、实现完整的数据库、设计应用程序界面、连接数据库、应用程序对数据库的查询统计等、系统调试七个阶段，本文案例主要选择实现完整的数据库这个阶段，旨在培养学生能够依据E-R 图创建出符合完整性约束的数据库的职业能力。

项目的部分需求描述如下：每个用户有唯一的ID,但是相同商品的商品ID 是一样的，每个带有商品ID 的商品都有库存记录在数据库中，用户和商品之间产生多对多的订单关系。

根据该描述可得到数据库的概念模型（E-R 模型），部分实体的E-R 图如图1所示。

图1系统E-R 图
数据库的概念模型和物理模型之间可以相互转换，学生应
该学会根据E-R 图绘制出系统的物理表结构，转换的规则是一个实体对应一张物理表，实体和实体之间产生的关系要不要转换成表分为3种情况：（1）1:1关系不用生成新的物理表，只需在关系的任一方实体转换成的物理表中添加外键约束即可；（2）1:n 关系也无须生成新的物理表，只需在关系n 的一方实体转换成的物理表中添加外键约束（关系1方的主键）；（3）m:n 关系必须转换成新的物理表，新的物理表由关系m 方和关系n 方的主属性和新s 的联系属性构成。

依据此规则可描绘出该系统的主要关系模式，具体如下：
User(userid,username,password,sex,phone,address,points)Goods(goodsid,goodsname,categoryid,price,stock,collection)Order(orderid,userid[fk1],goodsid[fk2],quantity)
2数据库完整性的概述
SQL Server 数据库完整性是指数据库中数据的一致性、正确性和相容性，通过完整性约束实现，主要分为实体完整性、域
完整性、参照完整性和用户自定义完整性[2]。

数据库中所有数据的状态及状态间的转换都受到约束的限制，如果不对数据进行完整性约束，会出现数据的更新异常、插入异常、删除异常等不一致性的问题。

合理地制定完整性约束是保证SQL Server 数据库安全的重要技术手段。

在设计开发数据库阶段，如何设定完整性约束更为重要，本文将从实体、域、参照、用户自定义完整性四个方面探讨User 、Goods 、Order 三张表完整性约束的案例设计。

整体的教学过程大致如下：首先由教师提出问题引出案例，然后学生分组讨论，再由教师演示案例的设计，接着由学生自己动手实践，最后由教师进行点评。

3实体完整性
实体完整性主要对表中的每一行进行限制，每一行是一个
元组，一个实体，需要确保每个实体都是唯一的，不能重复。

可通过设置主键（PRIMARY KEY ）约束、唯一（UNIQUE ）约束、索
收稿日期：2020-10-25
作者简介：魏慧娟（1990—），女，河南周口人，助教，硕士，主要从事数据库技术研究。

166
计算机教学与教育信息化
本栏目责任编辑：王
力
Computer Knowledge and Technology 电脑知识与技术
第17卷第9期(2021年3月)
引实现实体完整性。

根据实体完整性的知识点联系网上购物系统的项目将设计以下案例说明PRIMARY KEY 和UNIQUE 约束的用法。

1）主键约束案例1：如何为Order 表设置PRIMARY KEY 约束？分析：如何确定一张表的主键关键在于所选字段是否可以唯一标识一条记录，设定为主键的字段可以是单个也可以是多个。

而多个字段组合在一起设为一个主键，并不是说一张表存在多个主键。

在Order 表中，怎么唯一标识表中的一行记录呢，一个userid 可以标识一条订单记录吗？很明显单独的一个use⁃rid 或goodsid 都不能标识一个订单记录，因为一个用户可以拥有多个订单，一个商品也可以被生成多个订单。

所以这里选择userid 、goodsid 作为组合主键（订单ID 单独作为主键不再重点介绍）。

组合主键的概念很多初学者搞不清楚，经常误以为是一张表有多个主键。

为Order 表设置组合主键的sql 语句（不区分大小写）如下：
Alter table Order add constraint pk_1primary key(userid,goodsid)--增加PRIMARY KEY 约束
2）唯一约束案例2：怎样使每个用户的用户名称唯一？为User 表设置唯一约束和主键约束有什么区别？
分析：UNIQUE 约束用于限制列的值唯一，通过对表中列的限制实现实体完整性。

SQL Server 数据库系统自动为建立UNIQUE 约束的列创建唯一索引。

要想每个用户的用户名称唯一，只需为用户名称这一列增加UNIQUE 约束。

可是User 表中的用户ID 作为主键是唯一的，用户名称也是唯一的，有什么区别？设置为主键的用户ID 列是唯一的，但不允许为NULL ，而用户名称的列允许存在一个NULL 值，这就是唯一约束和主键约束的区别。

将User 表的username 字段增加唯一约束的语句如下：
Alter table User add constraint un_1unique(username)--增加UNIQUE 约束
4域完整性
域完整性主要通过限制表中的每一列实现列中所有数据取值的合法性，可通过建立检查（CHECK ）约束、默认值（DE⁃FAULT ）约束、非空（NOT NULL ）约束、规则（RULE ）和默认值对象(DEFAULT)实现域完整性[3]，此外也可通过数据类型限制列中数据的类型、长度、精度等。

下面结合项目设计案例3-5讲解域完整性的实现方法。

1）检查约束和非空约束案例3：为User 表增加约束，该约束限制电话号码列只能为11位数字，以数字1开头，且该列的值不允许为空。

分析：CHECK 约束可通过限制insert 或update 某一列或多列的值强制实现域完整性[4]。

这里根据限制条件分析出使用的约束为CHECK 约束和NOT NULL 约束。

根据限制电话号码列只能为11位数字的条件得出CHECK 表达式需要like 和通配符“[]”连用：
Alter table User add constraint ck_1check(phone like ’1[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]’)--增加CHECK 约束
Alter table User alter column phone varchar(20)not null--增加NOT NULL 约束
2）默认值约束
案例4：如果在向User 表增加用户记录时，没有相关用户的积分信息，则将积分字段的值默认为0，该如何设置约束？
分析：如果不向User 表中积分列插入任何值，则积分列的值为NULL 而不是0，学生特别容易混淆0和NULL 。

DEFAULT 约束通过自动为没有明确指定任何值的列添加默认值强制实现域完整性。

如果积分列没有指定具体的数值，则该列的值可通过增加DEFAULT 约束将其自动设为0。

Alter table User add constraint df_1default(0)for points--增加DEFAULT 约束
3）规则和默认值对象除了通过增加检查、非空和默认值约束实现域完整性，也可通过建立规则和默认值对象实现域完整性，规则和默认值对象都是独立存储在数据库中的对象。

规则作用于表中的列或用户自定义的数据类型上，系统会检查用户即将增加或更新的数据是否违反绑定在域上的规则[5]。

默认值对象也一样，所以都不会受到绑定的列或用户自定义数据类型删除的影响。

案例5：定义RULE ，限制用户购买某种商品的数量不能超过5个；定义DEFAULT 对象，将没有提供购买数量的销售数量列的数值设置为1。

分析：RULE 和DEFAULT 对象因为是独立存在的数据库对象，都需先定义，再绑定到要限制的列或用户自定义数据类型上。

下面只给出定义、绑定和解绑规则，DEFAULT 对象绑定解绑不再列举。

Create rule rule_1as @x between 0and 5--定义规则rule_1Exec sp_bindrule ’rule_1’,’Order.quantity ’--绑定rule_1到列quantity
Exec sp_unbindrule ’Order.quantity ’--利用存储过程sp_unbindrule 解绑rule_1
Create default value1as 1--定义默认值对象value1
5参照完整性
参照完整性的实现主要通过定义一个数据库中不同表之
间的关系，要求一张表（子表）的一列必须引用另一张表（父表）的一列（主键），实现此关系的列在子表中被定义为外键（FOR⁃EIGN KEY ）。

建立FOREIGN KEY 约束是实现参照完整性的主要手段。

案例6：如何使Order 表和User 表、Goods 表产生联系，产生联系后，向Order 表中插入一条记录，该记录中的用户ID 在Us⁃er 表却不存在，可以插入成功吗？
分析：如果使Order 表和User 表建立关系，则应建立FOR⁃EIGN KEY 约束，如何判断约束要建在哪个表中？首先分清谁是父表，谁是子表，被引用的表称为父表，父表中包含被引用列的全部信息，并且该列在父表中充当主键。

Order 表和User 表只能通过用户ID 产生关系，而User 表中包含用户ID 列的全部信息，且用户ID 为User 表的主键，所以User 表为被引用的表（即父表），在子表Order 表中设置用户ID 为外键。

同理，在子表Order 表中设置商品ID 列为外键参照Goods 表（父表）的商品ID 列。

建立完FOREIGN KEY 约束后，必须保证Order 表用户ID 的取值和User 表中用户ID 的取值相匹配，如果向Order 表中插入不匹配的数据，就会出现插入异常的现象。

在教学过程中，发现学生经常不理解外键约束的概念，分不清子父表，通过此案例已讲解清楚如何区分子父表。

Alter table Order add constraint fk_1foreign key(userid)refer⁃
167
本栏目责任编辑：王力
计算机教学与教育信息化
Computer Knowledge and Technology 电脑知识与技术第17卷第9期(2021年3月)
ences User(userid)--在Order 表中设置userid 为外键
Alter table Order add constraint fk_2foreign key(goodsid)ref⁃erences Goods(goodsid)--在Order 表中设置goodsid 为外键
6用户自定义完整性
用户自定义完整性涵盖范围比较广，实现域完整性的各种方法都支持用户自定义完整性，如字段的数据类型，检查约束，默认值约束，规则，存储过程，触发器等。

这里简单介绍下如何定义触发器，实现用户自定义完整性。

触发器是教学环节中的难点，触发器的执行是在某些特定条件下自动触发执行的，不需要调用。

在触发器的知识点上，学生首先要充分理解两个临时表inserted 和deleted ，才能熟练的创建触发器。

案例7：用户每生成一次订单，商品的库存量都会自动更新。

分析：用户生成一次订单说明Order 表中增加了一条记录，而商品的库存量自动更新说明触发器触发条件是向Order 表中insert 数据，触发器触发后引起的操作是update Goods 表的库存量。

所以要在Order 表上创建after （与for 同义）触发器，生成订单时，要插入的销售数量@quantity 存放在临时表inserted 中，Goods 表的库存量等于当前商品@goodsid 的库存量减去@quan⁃tity 。

Create trigger tri_1on Order for insert as
declare @quantity int,@goodsid varchar(20)
select @quantity=quantity,@goodsid=goodsid from inserted update Goods set stock=stock-@quantity where goodsid=@goodsid
7结束语
本文借助于一个项目设计案例介绍了保持数据库完整性的各种实现方法，设计的案例引导学生逐步了解完整性约束的各个知识点，从而使其具备设计完整数据库的职业能力。

此外，精心选择的各个案例能够调动学生学习的积极性，进一步培养学生的实践能力，帮助学生对完整性约束建立一个全面的知识体系。

参考文献：
[1]朱成.基于Java 的网上购物系统的设计与开发[D].南昌:南昌航空大学,2019:22.
[2]李熹.问题驱动与反例教学法相结合提高数据库完整性的教学质量[J].广西民族大学学报(自然科学版),2017,23(1):104-108.
[3]徐博龙.数据库中域完整性的设计与应用[J].信息与电脑(理论版),2019(14):152-154.
[4]陈林琳,蒋丽丽,解二虎.SQL Server 2008数据库设计教程[M].镇江:江苏大学出版社,2013.
[5]陈潇.面向SQL Server 2012的数据库约束的设计与应用[J].软件工程,2018,21(12):12-14.
【通联编辑：代影】
（上接第152页）
有通过工作人员不断地去在实际操作当中进行培养才能够得到。

所以说，高校在开展大数据专业的时候，一定要注意培养人才不仅仅只是知识方面的培养，还需要将知识和实际动手能力进行一个有机的结合，这样才能够真正地加强大数据人才的工程实践能力。

5）多元化人才培养方式。

大数据专业是近些年来新兴的一个专业，有许多高校都存在着师资力量不足的情况，而大数据又是一门应用型的专业，所以说国务院也明确地提出了必须要创新人才培养的模式，建立多层次的人才培养系统，可以采取跨校联合培养这样的方式，使用这样的方法更好地去培养大数据综合型人才，高校在培养的过程当中，也可以引入一些校外的企业或者是行业资源，实现人才的多元化培养，缩短人才培养跟企业的工作之间的距离。

4结束语
为了能够更好地适应时代的发展，高校不断地提高了对于
大数据专业方面应用的关注度。

并且也在不断地深化建立相关的专业型人才培养体系，这主要是为了适应现代社会对于人
才的需求，明确好教学的目标，这种培养的办法也有效地提升了学生的实践能力和创新能力。

大数据技术课程体系，也会伴随着时代的发展以及企业用人的不断地变化而产生一定的局部的调整，这样可以进一步的提升学生未来的就业能力。

参考文献：
[1]陈瑶.基于成果导向的音乐学专业应用型人才培养模式研究[J].佳木斯职业学院学报,2020,36(1):207-208.
[2]刘涛.基于成果导向理念的高职经济类专业人才培养模式研究[J].环球市场信息导报,2018(38):159.
[3]韩萍,左忠义,刘岩,等.基于成果导向理念的交通工程专业人才培养模式研究[J].教育教学论坛,2016(38):223-224.
[4]王莉,杨俊伟,徐志辉.基于成果导向教育的创新人才培养模式的课程建设研究[J].才智,2016(25):209.
[5]于敏,王宗运.基于成果导向教育的高校人才培养模式的课程建设研究[J].建筑工程技术与设计,2020(25):4214.
[6]张爽,张其久.基于OBE 成果导向的机械类工业工程专业人才培养模式研究[J].科技风,2018(7):51-52.
【通联编辑：张薇】
168。