情景5数据完整性及完整性约束.ppt

1、情景5 数据完整性及完整性约束,SQL Server数据库技术及应用,数据完整性及完整性约束,数据库中的数据是从外界输入的，而数据的输入由于种种原因，会发生输入无效或错误信息。保证输入的数据符合规定，成为了数据库系统，尤其是多用户的关系数据库系统首要关注的问题。数据完整性因此而提出。本情景将讲述数据完整性的概念及其在SQL Server 中的实现法,目录,任务1：掌握数据完整性 任务2：掌握数据约束的实现,任务1：掌握数据完整性,5.1 完整性的概念 数据完整性（Data Integrity）是指数据的精确性（Accuracy） 和可靠性（Reliability）。它是应防止数据库中存在不符合

2、语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。数据完整性分为四类：实体完整性（Entity Integrity）、域完整性（Domain Integrity）、参照完整性（Referential Integrity）、用户定义的完整性（User-defined Integrity,5.1.1 实体完整性（Entity Integrity,实体完整性规定表的每一行在表中是唯一的实体。表中定义的UNIQUE、PRIMARYKEY 和IDENTITY 约束就是实体完整性的体现,5.1.2 域完整性（Domain Integrity,域完整性是指数据库表中的列必须满足某种特定

3、的数据类型或约束。其中约束又包括取值范围、精度等规定。表中的CHECK、FOREIGN KEY 约束和DEFAULT、 NOT NULL定义都属于域完整性的范畴,5.1.3 参照完整性（Referential Integrity,参照完整性是指两个表的主关键字和外关键字的数据对应一致。它确保了有主关键字的表中对应其表的外关键字的行存在，即保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散。参照完整性是建立在外关键字和主关键字之间或外关键字和唯一性关键字之间的关系上的,5.1.4 用户定义的完整性（User-defined Integrity,不同的关系数据库系统根据其应用环

4、境的不同，往往还需要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。SQL Server 提供了定义和检验这类完整性的机制，以便用统一的系统方法来处理它们，而不是用应用程序来承担这一功能。其他的完整性类型都支持用户定义的完整性,5.2 完整性的实施约束,5.2.1 主键（Primary Key） 表通常具有包含唯一标识表中每一行的一列或多列，这样的一列或多列称为表的主键（primary key），用于实现表的实体完整性。主键具有以下特性： （1）保证表中唯一可区分记录行； （2）每个表只能存在一个主键； （3）主键可以

5、由一个或多个字段组成； （4）主键不能为空也不能重复,5.2.1 主键（Primary Key,例5-1】由于在Student_info表中，需要用学号作为记录唯一区分的标志，可以把学号当作该表的PRIMARY KEY，如何来完成设置呢,5.2.1 主键（Primary Key,1）打开SSMS(SQL Server Management Studio)管理器，依次如图5-1展开各节点，点击“设计”选项,5.2.1 主键（Primary Key,2）右键单击要设置PRIMARY KEY的列，比如本例中选择右键Sid，在弹出的快捷菜单中选择“设置主键”选项，如图5-2所示,5.2.1 主键（Pr

6、imary Key,3）完成后，在该字段前面将会显示一把钥匙，表示设置成功，如图5-3所示,5.2.1 主键（Primary Key,4）如果需要删除主键，则在Sid上单击右键选择“删除主键”选项，如图5-4所示,5.2.1 主键（Primary Key,第二种方法，使用T-SQL创建主键约束 定义主键约束的基本语句格式如下： ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY （列或者列的组合,5.2.1 主键（Primary Key,更普遍的使用方法是在创建Student_info表时，就把主键设置写进SQL代码中： CREATE TABLE Stu

7、dent_info ( SidChar(8) PRIMARY KEY, Sname Varchar(10), Sgender Char(2), Sbirth Datetime, Sdepart Char(6), Saddr Nvarchar(50), Stel Char(14),5.2.2 UNIQUE约束,UNIQUE约束通过确保在列中不输入重复值保证一列或多列的实体完整性，比如说公民身份证号这一列就不允许出现重复值，每个UNIQUE约束都要创建一个唯一索引。对于实施UNIQUE约束的列，不允许有任意两行具有相同的索引值。与主键不同的是，SQL Server允许为一个表创建多个UNIQUE约

8、束,5.2.2 UNIQUE约束,例5-2】在学校的课程管理中，不允许两门名称完全相同的课程存在，也就是说在Course_info表中，Cname应该是唯一的，应当采用UNIQUE约束。该如何操作呢,5.2.2 UNIQUE约束,1）打开SSMS(SQL Server Management Studio)管理器，依次如图5-5展开各节点，点击“设计”选项,5.2.2 UNIQUE约束,2）右键单击要设置唯一键的列，比如本例中选择右键Cname，在弹出的快捷菜单中选择“索引/键”，如图5-6所示,5.2.2 UNIQUE约束,3）点击“添加”按钮，选择如图5-7所示的按钮,5.2.2 UNIQU

9、E约束,4）选择要设置唯一键的Cname字段。如图5-8所示，单击“确定”按钮,5.2.2 UNIQUE约束,第二种方法，是采用SQL语句完成唯一约束设置。 定义唯一约束的基本格式如下： CONSTRAINT 约束名 UNIQUE （列或列的组合,5.2.2 UNIQUE约束,其实，更普遍的使用方法是在创建Student_info表时，就把唯一键设置写进SQL代码中： CREATE TABLE Course_info ( Cid Char(10), Cname Varchar(20) UNIQUE, Cperiod Tinyint, Ccedit Numeric(3,2), Cterm Cha

10、r(2), Ctype Nvarchar,5.2.3 NOT NULL,列的为空性决定表中的行是否可让该列包含空值。空值不同于零。NULL的意思是表示没有输入，出现NULL通常表示值未知或未定义。NOT NULL表示不允许为空。当插入或者修改数据时，设置了NOT NULL的约束的列的值不允许为空，必须存在具体的值,5.2.3 NOT NULL,例5-3】学号是为一位同学而设定的，如果没有该同学的存在，也就不应该为其设置学号。所以一旦有学号就表示该学生一定存在，该学生存在，也就不允许其姓名为空，那么如何设置学生姓名Sname字段不允许为空呢,5.2.3 NOT NULL,1）打开SSMS（SQL

11、 Server Management Studio）管理器，依次如图5-9展开各节点，点击“设计”选项,5.2.3 NOT NULL,2）把Sname字段中“允许NULL值”的勾去掉，如图5-10所示,5.2.3 NOT NULL,第二种方法，使用T-SQL创建不允许为空约束，普遍的使用方法是在创建Student_info表时，就把主键设置写进SQL代码中： CREATE TABLE Student_info ( SidChar(8) PRIMARY KEY, Sname Varchar(10) NOT NULL, Sgender Char(2), Sbirth Datetime, Sdepa

12、rt Char(6), Saddr Nvarchar(50), Stel Char(14),5.2.4 CHECK,CHECK约束限制输入列或者多列中的可能值，从而保证数据库中数据的域完整性，一个数据表可以定义多个CHECK约束,5.2.4 CHECK,例5-4】在SC表中对成绩字段设置其取值范围在0100之间。 第一种方法，使用SSMS（SQL Server Management Studio）管理器设置。步骤如下： （1）打开SSMS（SQL Server Management Studio）管理器，依次如图5-11展开各节点，点击“设计”选项,5.2.4 CHECK,5.2.4 CHEC

13、K,2）在字段“Grade”上点击右键，选择“Check约束”，如图5-12所示,5.2.4 CHECK,3）选择“添加”按钮，在表达式中输入“Grade = 0 AND Grade =100”，为标识取一个名字，设置好后点击“关闭”按钮即可，如图5-13所示,5.2.4 CHECK,如果要删除约束可以点击上图所示的删除按钮。对于Check约束还有以下几点需要说明： （1）可以将多个Check约束应用于单个列； （2）Check约束不接受计算结果为False的值； （3）在执行添加和修改记录语句时验证Check约束，删除记录时不验证Check约束,5.2.4 CHECK,第二种方法，使用T-S

14、QL创建Check约束。定义该约束的基本语句格式如下： ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK（表达式,5.2.4 CHECK,更普遍的使用方法是在创建SC表时，就把Check约束设置写进SQL代码中： CREATE TABLE SC ( Sid Char(8), Cid Char(10), Grade Numeric(5,2) CHECK (Grade = 0 AND Grade = 100),5.2.5 DEFAULT,DEFAULT定义是指表中添加新行时给表中某一列指定的默认的数据。使用DEFAULT定义，一是可以避免NOT NULL值的数据为空性

15、错误；二是可以加快用户的输入速度。DEFAULT定义同样可以通过SSMS或者T-SQL语句创建。 当绑定到列或用户定义数据类型时，如果插入时没有明确提供值，默认值便会指定一个值，并将其插入到对象所绑定的列中。因为默认值定义和表存储在一起，当除去表时，将自动除去默认值定义,5.2.5 DEFAULT,例5-5】在Student_info信息表中，我们将学生所在系在默认情况下设置为G02001。 第一种方法，使用SSMS（SQL Server Management Studio）管理器，步骤如下,5.2.5 DEFAULT,1）打开SSMS（SQL Server Management Studio

16、）管理器设置。依次如图5-14所示展开各节点，点击“设计”选项,5.2.5 DEFAULT,2）选择“Sdepart”字段，在窗口的下部，有列属性对话框，在“默认值或绑定”中输入“G02001”，如图5-15所示,5.2.5 DEFAULT,第二种方法，使用T-SQL创建Default定义。具体如下： ALTER TABLE 表名 ADD CONSTRAINT 约束名 DEFAULT 常量 FOR 列名,5.2.5 DEFAULT,更普遍的使用方法是在创建Student_info表时，就可以把默认值设置写进SQL代码中： CREATE TABLE Student_info ( Sid Char

17、(8) PRIMARY KEY, Sname Varchar(10) NOT NULL, Sgender Char(2), Sbirth Datetime, Sdepart Char(6) DEFAULT g02001, Saddr Nvarchar(50), Stel Char(14),5.2.6 FOREIGN KEY,FOREIGN KEY约束为表中一列或多列数据提供参照完整性，它限制到表中被约束列的值必须在被引用表中已经存在。例如，在图5-16中，在学生的选课表中，所选的课程，其代码必须在课程表（course表）中存在，否则学生所选的课程是不存在的，也就是没有意义的。为了保证两张或者多

18、张表中数据的一致性，引入了外键约束,5.2.6 FOREIGN KEY,5.2.6 FOREIGN KEY,例5-6】在SC选课信息表中，我们将Cid字段的外键设置为课程表（COURSE表）中的Cid字段。 第一种方法，使用SSMS（SQL Server Management Studio）管理器，步骤如下,5.2.6 FOREIGN KEY,1）打开SSMS（SQL Server Management Studio）管理器设置，依次如图5-17所示展开各节点，点击“设计”选项,5.2.6 FOREIGN KEY,2）在打开的“设计”对话框中，在Cid字段上点击右键，在下拉菜单中选择“关系”选

19、项，如图5-18所示,5.2.6 FOREIGN KEY,3）打开“外键关系”对话框，选择“添加”按钮后，进入外键编辑状态。如图5-19所示,5.2.6 FOREIGN KEY,4）单击“表和列规范”右边的按钮，进入“表和列”设置对话框，分别选择主键表和外键表以及列，设置完毕后点击“确定”按钮，再单击“关闭”图表，完成关系的添加。如图5-20所示,5.2.6 FOREIGN KEY,第二种方法，使用T-SQL创建外键约束。步骤如下： ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY 列名 REFERENCES 被引用表名（被引用列名,5.2.6 FOR

20、EIGN KEY,更为普遍的使用方法是在创建SC表时，就把默认值设置写进SQL代码中： CREATE TABLE SC ( Sid Char(8), Cid Char(10) REFERENCES Course_info(Cid), Grade Numeric(5,2) CHECK (Grade = 0 AND Grade = 100),实例：Student数据库的约束,首先，我们可以把上一情景中的数据表结构拿出来，进行进一步的完善，也就是通过SQL语句来实现约束。具体的数据表的结构及约束设置如下,表5-1 Department_info 系部信息表,Department_info,CREAT

21、E TABLE Department_info ( DidChar(6) PRIMARY KEY, DnameVarchar(18) NOT NULL UNIQUE, Ddean Varchar(10) , Dtel Char(14), Daddr Nvarchar(50),表5-2 Student_info(学生信息表,Student_info (学生信息表,CREATE TABLE Student_info ( Sid Char(8) PRIMARY KEY, Sname Varchar(10) NOT NULL, Sgender Char(2) CHECK(Sgender = 男 OR Sgender = 女), Sbirth Datetime CHECK(Sbirth = 1900 OR Sbirth = 2100), Sd