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

2021-04-25魏慧娟李思文小爽

电脑知识与技术 2021年9期

关键词：案例

魏慧娟李思文小爽

摘要：数据库完整性是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所示。

数据库的概念模型和物理模型之间可以相互转换，学生应该学会根据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）约束、索引实现实体完整性。根据实体完整性的知识点联系网上购物系统的项目将设计以下案例说明PRIMARY KEY和UNIQUE约束的用法。

1）主键约束

案例1：如何为Order表设置PRIMARY KEY约束？

分析：如何确定一张表的主键关键在于所选字段是否可以唯一标识一条记录，设定为主键的字段可以是单个也可以是多个。而多个字段组合在一起设为一个主键，并不是说一张表存在多个主键。在Order表中，怎么唯一标识表中的一行记录呢，一个userid可以标识一条订单记录吗？很明显单独的一个userid或goodsid都不能标识一个订单记录，因为一个用户可以拥有多个订单，一个商品也可以被生成多个订单。所以这里选择userid、goodsid作为组合主键（订单ID单独作为主键不再重点介绍）。组合主键的概念很多初学者搞不清楚，经常误以为是一张表有多个主键。为Order表设置组合主键的sql语句（不区分大小写）如下：

Alter table Order add constraint pk_1 primary 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_1 unique（username）--增加UNIQUE约束

4域完整性

域完整性主要通过限制表中的每一列实现列中所有数据取值的合法性，可通过建立检查（CHECK）约束、默认值（DEFAULT）约束、非空（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_1 check（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_1 default（0） for points--增加DEFAULT约束

3）规则和默认值对象

除了通过增加检查、非空和默认值约束实现域完整性，也可通过建立规则和默认值对象实现域完整性，规则和默认值对象都是独立存储在数据库中的对象。规则作用于表中的列或用户自定义的数据类型上，系统会检查用户即将增加或更新的数据是否违反绑定在域上的规则[5]。默认值对象也一样，所以都不会受到绑定的列或用户自定义数据类型删除的影响。

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

分析：RULE和DEFAULT对象因为是独立存在的数据库对象，都需先定义，再绑定到要限制的列或用户自定义数据类型上。下面只给出定义、绑定和解绑规则，DEFAULT对象绑定解绑不再列举。

Create rule rule_1 as @x between 0 and 5 --定义规则rule_1

Exec sp_bindrulerule_1，Order.quantity--绑定rule_1到列quantity

Exec sp_unbindruleOrder.quantity --利用存储过程sp_unbindrule解绑rule_1

Create default value1 as 1 --定义默认值对象value1

5参照完整性

参照完整性的实现主要通过定义一个数据库中不同表之间的关系，要求一张表（子表）的一列必须引用另一张表（父表）的一列（主键），实现此关系的列在子表中被定义为外键（FOREIGN KEY）。建立FOREIGN KEY约束是实现参照完整性的主要手段。

案例6：如何使Order表和User表、Goods表产生联系，产生联系后，向Order表中插入一条记录，该记录中的用户ID在User表却不存在，可以插入成功吗？

分析：如果使Order表和User表建立关系，则应建立FOREIGN 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_1 foreign key（userid） references User（userid）--在Order表中设置userid为外键

Alter table Order add constraint fk_2 foreign key（goodsid） references Goods（goodsid） --在Order表中设置goodsid为外键

6用户自定义完整性

用户自定义完整性涵盖范围比较广，实现域完整性的各种方法都支持用户自定义完整性，如字段的数据类型，检查约束，默认值约束，规则，存储过程，触发器等。这里简单介绍下如何定义触发器，实现用户自定义完整性。触发器是教学环节中的难点，触发器的执行是在某些特定条件下自动触发执行的，不需要调用。在触发器的知识点上，学生首先要充分理解两个临时表inserted和deleted，才能熟练的创建触发器。

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

分析：用户生成一次订单说明Order表中增加了一条记录，而商品的库存量自动更新说明触发器触发条件是向Order表中insert数据，触发器触发后引起的操作是update Goods表的库存量。所以要在Order表上创建after（与for同义）触发器，生成订单时，要插入的销售数量@quantity存放在临时表inserted中，Goods表的库存量等于当前商品@goodsid的库存量减去@quantity。

Create trigger tri_1

on Order for insert

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.