实验八 数据完整性 实验指导

实验八 数据完整性 实验指导

一、完整性的概念

数据完整性是指存储在数据库中的数据正确无误，并且相关数据具有一致性。数据库中的数据是否完整，关系到数据库系统能否真实的反映现实世界。立足在“学生”表中学生的学号要具有唯一性，学生性别只能是男或女，其所在的系部、专业、班级必须是存在的，否则，就会出现数据库中的数据与现实不符的现象。如果数据库中总存在不完整的数据，那么它就没有存在的必要了，因此，实现数据的完整性在数据库管理系统中十分重要。 根据数据完整性机制所作用的数据库对象和范围不同，数据完整性可分为实体完整性、域完整性、参照完整性和用户定义完整性4种类型。 1. 实体完整性

实体是表中的记录，表中的一条记录就是一个实体。实体完整性要求在表中不能存在完全相同的记录，而且每条记录都要具有一个非空且不重复的主键值。这样，就可以保证数据所代表的任何事物都不重复、可以区分。例如，学生表中的学号必须唯一，并且不能为空，这样就可以保证学生记录的唯一性。实现实体完整性的方法主要有主键约束、唯一索引、唯一约束和制定IDENTITIY属性。 2. 域完整性

域完整性是指特定列的项的有效性。域完整性要求向表中指定列输入的数据必须具有正确数据类型、格式及有效的数据范围。例如，假设现实中学生的成绩为百分制，则在“课程注册”表中，向成绩列输入的数据，不能出现字符，也不能是小于0或大于100的数值。实现域完整性的方法主要有CHECK约束、外键约束、默认约束、非空约束、规则及在建表时设置的数据类型。 3. 参照完整性

参照完整性是指在有关联的两个或两个以上的表中，通过使用主键和外键或唯一键和外键之间的关系，使表中的键值在相关表中保持一致。引用完整性要求不能引用不存在的值。如果一个键值发生更改，则在整个数据库中，对该键值和所有引用要进行一致性的更改。例如，在学生表中的“班级代码”列的值必须是在班级表中“班级代码”列中存在的值，防止在录入学生记录时将学生分配到一个不存在的班级中。在SQL Server 2005中，参照完整性是通过FOREIGN KEY 和CHECK约束，以外键与主键之间或外键与唯一键之间的关系为基础。

4. 用户定义完整性

用户定义的完整性是应用领域需要遵守的约束条件，其允许用户定义不属于其他任何完整性分类的特定业务规则。所有的完整性类型（包括CREATE TABLE中所有列级约束和表级约束、存储过程以及触发器）都支持用户定义完整性。

1

二、使用约束

约束是SQL Server提供的自动强制数据完整性的一种方法，它通过定义列的取值规则来维护数据完整性。 1. 约束的类型

SQL Server2005支持的约束有NOT NULL（非空）约束、CHECK（检查）约束、UNIQUE（唯一）约束、PRIMARY KEY（主键）约束、FOREIGN KEY（外键）约束和DEFAULT（默认）约束。

 PRIMARY KEY约束。主键约束用来强制实现数据的实体完整性，它是在表中定义一

个主键来唯一标识表中每行记录。例如，在“学生”表中可以将学生编号设置为主键，用来保证表中的学生记录具有唯一性。一般情况下，数据库中的每个表都包含一列或一组列来唯一标识表中的每一行记录的值。  UINQUE约束。唯一约束用来强制实现数据的实体完整性，它主要用来限制表的非主

键列中不允许输入重复值。例如在“专业”表中可以将专业代码作为主键用来保证记录的唯一性。如果不允许一个学院有同名专业存在，应该为专业名称列定义唯一约束，以保证非主键列中不出现重复值。

 NOT NULL约束。非空约束用来强制实现数据的域完整性，它用于设定某列值不能

为空。如果指定某列不能为空，则在添加记录时，必须为此列添加数据。例如，对于“班级表，存在一个班，就必须存在其相应专业，这时，就应该设置专业代码不能空。注意：定义了主键约束和标识列属性的列不允许为空。

 CHECK约束。检查约束用来强制数据的域完整性，它使用逻辑表达式来限定表中的

列可以接受的数据范围。例如，对于学生成绩的取值应限定在0~100之间，这时，就应该为成绩列创建检查约束，使其取值在正常范围内。  DEFAULT约束。默认约束用来强制数据的域完整性，它为表中某列建立一个默认值。

插入记录时，如果没有为该列提供输入值，系统就会自动将默认值赋给该列。例如，对于“学生“表中的性别字段，可以提供其默认值为男，当输入记录时，对于男生就可以不输入性别数据，而由默认值提供。  FOREIGN KEY约束。外键是指一个表中的一列或列组合，它虽不是该表的主键，却

是另一个表的主键。通过外键约束可以为相关联的表建立联系，实现数据的参照完整性，维护两表之间数据的一致性关系。例如，如果要求“学生”表中“班级代码”列的取值，必须是“班级”表中“班级代码”列的列值之一，就应该在“学生”表的“班级代码”上创建外键约束，从而使“学生”表和“班级”表中的“班级代码”具有一致性。 约束还可以分为列约束和表约束两类。当约束被定义于某个表的一列时成为列约束，定义于某个表的多列时成为表约束。当一个约束中必须包含一个以上的列时，必须使用表约束。 2. 创建主键约束

（1）使用SSMS创建主键约束

【例】在“studentdb”数据库中，将“选课SC”表中的“学号sno、课程号Cno”列设

2

置为组合主键。其操作步骤如下：

1）启动SSMS，在“对象资源管理器”中，依次展开“数据库”、“studentdb”、“表”，右击“sc”表，在弹出的快捷菜单中选择“修改”命令，打开“表设计器”。

2）在表设计器中，选择需要设置为主键的字段，如果需要选择多个字段，可以按住【ctrl】，同时使用鼠标单击每个要选择的字段。本例中，依次选学号sno、课号Cno字段。 3）选好字段后，右击选择的某个字段，在弹出的快捷菜单中选择“设置主键”。如图8.1所示。

图8.1 选择“设置主键”命令

4）执行命令后，在作为主键的字段前有一个钥匙图标，“表设计器”窗口如图8.2所示。也可以在选择好字段后，单击工具栏中的

“钥匙”工具按钮，设置主键。

图8.2 “表设计器”窗口

5)关闭表设计器，并保存设置。

3

（2）使用T-SQL语言创建主键约束

可以用CREATE TABLE，也可以用ALTER TABLE为已存在的表创建主键约束。格式如下：

ALTER TABLE table_name ADD

CONSTRAINT constraint_name

PRIMARY KEY[CLUSTERED|NONCLUSTERED] {(COLUMN[,…n])} 其中：

 Constraint_name是主键约束名称；  CLUSTERED表示在该列上建立聚集索引；

 NON CLUSTERED表示在该列上建立非聚集索引。 下面分别使用建表命令和修改表命令创建主键约束。

【例】在studentdb数据库中建立一个“民族”表Nationality（民族代码NNo，民族名称NName），将“民族代码”指定为主键。

USE studentdb GO

CREATE TABLE Nationality

(NNo char(2) CONSTRAINT pk_nno PRIMARY KEY, NName varchar(30) NOT NULL)

【例】在studentdb数据库中的“课程Course”表中，指定字段“课程号Cno”为表的主键，代码如下：

ALTER TABLE course ADD CONSTRAINT pk_cno PRIMARY KEY CLUSTERED(cno)

3. 创建唯一约束

（1）使用SSMS创建唯一约束

【例】在studendb数据库中，为课程“course”表中的“cname”字段创建一个唯一约束。操作步骤如下：

1）启动SSMS，在“对象资源管理器”中，依次展开“数据库”、“studentdb”、“表”节点。

2）右击“course”表，在快捷菜单中单击“修改”命令，打开“表设计器”，右击任意

4

字段，在快捷菜单中单击“索引/键”，打开“索引/键”对话框。

3）单击“添加”命令按钮，系统给出系统默认的唯一约束名：“IX_Course”，显示在“选定的注/唯一或索引”列表框中，如图8.3所示。

图8.3 “索引/键”对话框

4）选中唯一约束“IX_Course”，在其右侧的“属性”窗口中，可以执行修改约束名称、设置约束列等操作。

5）单击“属性”中的“常规”“列”属性，右侧出现“”按钮，单击该按钮，打开“索引列”对话框，在如图8.4所示，在列名下拉列表框中选择“cname”，在排序顺序中选择“升序”，设置创建唯一约束的列名。

5

图8.3 “索引/键”对话框

6）设置完成后，单击“确定”，回到“索引/键”对话框，修改常规属性中“是唯一的”属性值为“是”，如图8.5所示。

图8.5 “索引/键”对话框1

7）设置完成后，关闭“索引/键”对话框和“表设计器”窗口，保存设置。

6

（2）使用T-SQL语言创建唯一约束 语法格式：

ALTER TABLE table_name ADD

CONSTRAINT constraint_name

UNIQUE[CLUSTERED|NONCLUSTERED] {(COLUMN[,…n])} 其中：

 table_name是需要创建唯一约束的表名称；  constraint_name是唯一约束名称；

 column是表中需要创建唯一约束的列名称；

【例】在studentdb数据库中，为“民族”表Nationality中的“民族名称”字段创建唯一约束，代码如下：

ALTER TABLE Nationality

ADD CONSTRAINT uk_NName UNIQUE NONCLUSTERED(NNAME)

4. 创建检查约束

（1）使用SSMS创建检查约束

【例】为student表的“出生日期sbirth”列创建一个名称为ck_csrq的检查约束，以保证输入的数据大于1990年1月1日而小于当天日期。

1）启动SSMS，在“对象资源管理器”中，依次展开“数据库”、“studentdb”、“表”。 2）右击“student”表，在快捷菜单单击“修改”，打开“表设计器”，右击任意字段，单击“CHECK约束”，打开对话框。

3）单击“添加”命令按钮，系统给出默认的CHECK约束名：“CK_student”，显示在“选定的CHECK约束”列表中，创建“CHECK约束”对话框如图8.6所示。

7

图8.6 创建“CHECK约束”对话框

4）单击“属性”窗口中“常规”属性“表达式”，在其对应的文本框中输入约束条件（输入sbirth>’1990/01/01’ AND sbirth图8.7 “CHECK约束表达式”对话框

单击确定后，回到“CHECK约束”对话框中，如图8.8所示。

8

图8.8 “CHECK约束表达式”对话框1

5）根据需要，在“标识”属性“名称”对应的文本框中修改CHECK约束的名称，如“ck_csrq”，根据需要，修改“表设计器”对应的三个属性，三个属性值均是逻辑值，具体含义如下：

 强制用于INSERT和UPDATE：若该属性值为“是”，则在进行插入和修改操作时，

数据要符合检查约束的要求，否则操作不成功。

 强制用于复制：若该属性值为“是”，则在表中进行数据的复制操作时，所有的数

据要符合检查约束的iu，否则操作无效。

 在创建或重新启用时检查现有数据：若属性值为“是”，则保证表中已经存在的数

据也符合检查约束条件的限制，若表中有不符合条件的数据，则不能创建检查约束。 6）设置完成后，单击“关闭”命令按钮，完成CHECK约束的创建。 （2）使用T-SQL语言创建检查约束 语法格式：

ALTER TABLE table_name

ADD CONSTRAINT constraint_name CHECK (logical_expression) 其中：

 Table_name是表名称；

 Constraint_name 是约束名称；

9

 Logical_expression是检查约束的条件表达式。

【例】在studentdb数据库中，为“选课”表sc中的“成绩”Grade字段创建一个检查约束，以保证输入的学生成绩符合百分制要求。即在0~100之间。

ALTER TABLE sc

ADD CONSTRAINT ck_grade

CHECK (Grade>=0 AND Grade<=100)

5. 创建默认约束

用户在输入数据时，如果没有给某列赋值，该列的默认约束将自动为该列指定默认值。默认值可以是常量、内置函数或表达式。使用默认约束可以提高输入记录的速度。

（1）使用SSMS创建默认约束

【例】在student数据库中，有一“教师”Teacher表，为该表中的“学历”Teducation字段创建默认值，其默认值为“bachelor”。操作步骤：

1）启动SSMS，在“对象资源管理器”中，依次展开“数据库”、“student”、“表” 2）右击“教师表”teacher，在快捷菜单中选择“修改”，打开“表设计器”。 3）单击需要设置默认的列，在下面列属性设置栏的“默认值或绑定”选项对应的输入框中输入默认值即可，如图8.9所示。

图8.9 “表设计器”创建默认值窗口

4）设置完成后，关闭“表设计器”。

10

（2）使用T-SQL语言创建默认约束 语法格式：

ALTER TABLE table_name

ADD CONSTRAINT constraint_name

DEFAULT constant_expression[FOR column_name] 其中：

 Constant_expression是默认值

 Column_name是建立默认的列名称

【例】在student数据库中，为“教师”Teacher表的“学历”Teducation字段创建默认值，其默认值为“doctor”。

ALTER TABLE teacher ADD CONSTRAINT df_xueli

DEFAULT 'doctor' FOR Teducation

6. 创建外键约束

外键约束用来维护两个表之间的一致性关系。外键的建立是将一个表（主键表）的主键列包含在另一个表（从键表）中，这些列就是从键表中的外键。在从键表中插入或更新的外键值，必须存在于主键表中，这就保证了两个表中相关数据的一致性。注意，要先在主键表中设置好主键（或唯一键），才能从主键表中建立与之具有数据一致性关系的外键。

（1）使用SSMS创建外键约束

【例】在studentdb数据库中，为“student”表的所属班级代码“Sclass”列创建外键约束，从而保证在“student”表中输入有效的班级代码。

1）启动SSMS，在“对象资源管理器”中，依次展开“数据库”、“student”、“表”。 2）右击“student”表，选择“修改”，打开表设计器，右击任意字段，选择“关系”，打开“外键关系”对话框。

3）单击“添加”，系统给出默认的外键约束名：“FK_student_student”，显示在“选定的关系”列表框中，如图8.10所示。

11

图8.10 “外键关系”对话框

4）单击“FK_student_student”外键约束名，在其右侧的“属性”窗口单击“表和列规范”属性，再单击该属性右侧的“”按钮，打开“表和列”对话框，如图8.11所示。

图8.11 “表和列”对话框

12

5）在“表和列”对话框中，修改外键的名称，选择主键表和表中的主键，以及外键表和表中的外键，修改后结果如图8.11所示。单击“确定”，回到“外键关系”对话框。

6）单击关闭按钮，完成外键约束的创建。 （2）使用T-SQL语言创建外键约束 语法格式：

ALTER TABLE table_name

ADD CONSTRAINT constraint_name [FOREIGN KEY]{(column_name[,…])}

REFERENCES ref_table[(ref_column_name[,…])] 其中：    

table_name是需要创建外键的表名称； constraint_name是外键约束名称； Ref_table是主键表名称；

Ref_column_name是主键表的主键列名称。

【例】在studentdb数据库中，为“student”表的所属班级代码“Sclass”列创建外键约束，从而保证在“student”表中输入有效的班级代码。

ALTER TABLE student

ADD CONSTRAINT fk_student_class FOREIGN KEY (sclassno) REFERENCES class(classno)

7. 查看约束的定义

（1）使用SSMS查看约束

1）在SSMS中，右击要查看约束的表，打开“表设计器”。 2）在“表设计器”中可以查看主键约束、空值约束和默认值约束。

3）在“表设计器”中，右击任意字段，在弹出的快捷菜单中选择相关约束命令，进入“相关约束”对话框，查看外键约束、唯一约束和CHECK约束。

（2） 使用系统存储过程查看约束信息

可以通过系统存储过程sp_help来查看约束的名称、创建者、类型和创建时间，语法格式：

[EXEC] sp_help 约束名称

13

如果约束存在文本信息，可以使用sp_helptext来查看，格式： [EXEC] sp_helptext 约束名称

【例】使用系统存储过程查看选课“sc”表中的ck_grade约束信息。

EXEC sp_help ck_grade EXEC sp_helptext ck_grade

在“查询编辑器”窗口中执行以上代码，结果如图8.12所示。

图8.12 约束ck_grade的相关信息

8. 删除约束

1.使用SSMS删除约束

在表设计器中，可以移除主键，修改非空，去掉默认值，在表设计器中右击任意字段，在弹出的快捷菜单中选择相关约束命令，如“关系、索引/键、CHECK约束”等，进入“相关约束”对话框，选中约束，单击删除命令即可。

2.使用DROP命令删除约束

在“查询编辑器”中，也可以删除约束，命令格式： ALTER TABLE table_name

DROP CONSTRAINT constraint_name[,…n] 【例】删除教师sc表中的约束ck_grade。

ALTER TABLE sc

DROP CONSTRAINT ck_grade

14