数据库应用基础第五章数据完整性课件.ppt

1、第五章第五章 数据完整性数据完整性 SELECT ALL|DISTINCT select_list INTO new_table_name FROM table_name WHERE condition GROUP BY clause HAVING clause ORDER BY COMPUTE clause select_list所要查询的字段名称所要查询的字段名称INTO new_table_name将查询结果放到一个新的临时表中将查询结果放到一个新的临时表中FROM table_name欲查询数据的表的名称欲查询数据的表的名称WHERE condition欲查询数据的条件欲查询数据的条件

2、GROUP BY clause根据字段类别做总计函数处理根据字段类别做总计函数处理HAVING clause预查询数据的条件预查询数据的条件ORDER BY将查询结果按某字段排序将查询结果按某字段排序COMPUTE clause在在SELECT查询的同时，作数据总计查询的同时，作数据总计5.1 5.1 数据完整性的基本概念数据完整性的基本概念1 数据完整性的类型数据完整性的类型2 强制数据完整性强制数据完整性（1 1）实体完整性）实体完整性（2 2）域完整性）域完整性（3 3）引用完整性）引用完整性（4 4）用户定义完整性）用户定义完整性1 数据完整性的类型数据完整性的类型 数据完整性数据完整

3、性是指存储在数据库是指存储在数据库中的数据的中的数据的一致性一致性和和准确性准确性。（1 1）实体完整性）实体完整性 实体完整性实体完整性要求表中所有的行具有要求表中所有的行具有唯一唯一的标识符。的标识符。例：图书信息表例：图书信息表book_infobook_info中字段中字段book_idbook_id取值必须唯一。取值必须唯一。1 数据完整性的类型数据完整性的类型（2 2）域完整性）域完整性 域完整性域完整性是指数据库表中对指定列是指数据库表中对指定列有效的输入值有效的输入值。例：图书馆管理系统中，输入某例：图书馆管理系统中，输入某本图书的数量时不应当出现小于本图书的数量时不应当出现小

4、于0 0本的情况。本的情况。1 数据完整性的类型数据完整性的类型（3 3）引用完整性）引用完整性 引用完整性引用完整性用于在输入或删除记录用于在输入或删除记录时，保持表之间已定义的关系。时，保持表之间已定义的关系。例：对于例：对于 Library_DBLibrary_DB数据库中的数据库中的 book_infobook_info 表和表和 class class 表，引用完整性表，引用完整性基于基于 book_infobook_info 表中的外键表中的外键 (class_idclass_id)与与 class class 表中的主键表中的主键 (class_idclass_id)之间之间的关

5、系的关系。1 数据完整性的类型数据完整性的类型（4 4）用户定义完整性）用户定义完整性 用户定义完整性用户定义完整性使用户能够定义不使用户能够定义不属于其他任何完整性分类的属于其他任何完整性分类的特定业务规特定业务规则则。2 强制数据完整性强制数据完整性 强制数据完整性强制数据完整性就是数据完整性的就是数据完整性的实现。实现。SQL Server 2008 SQL Server 2008 通过下列机制来强制通过下列机制来强制列中数据的完整性列中数据的完整性 PRIMARY KEY PRIMARY KEY 约束约束 FOREIGN KEY FOREIGN KEY 约束约束 UNIQUE UNIQ

6、UE 约束约束 CHECK CHECK 约束约束 DEFAULT DEFAULT 约束约束 允许空值允许空值2 强制数据完整性强制数据完整性（1 1）声明数据完整性）声明数据完整性 声明数据完整性声明数据完整性是指定义数据标准是指定义数据标准规定数据必须作为对象定义的一部分，规定数据必须作为对象定义的一部分，SQL ServerSQL Server将自动确保数据符合标准。将自动确保数据符合标准。2 强制数据完整性强制数据完整性（2 2）过程定义数据完整性）过程定义数据完整性 过程定义数据完整性过程定义数据完整性是指通过编写是指通过编写用来定义数据必须满足的标准和强制该用来定义数据必须满足的标准

7、和强制该标准的脚本来实现数据完整性。标准的脚本来实现数据完整性。5.2 5.2 约束的类型约束的类型1 PRIMARY KEY 约束约束2 FOREIGN KEY 约束约束3 UNIQUE 约束约束4 CHECK 约束约束5 DEFAULT 约束约束 主键：主键：表中的一列或列的组合，表中的一列或列的组合，其值能其值能唯一地标识唯一地标识表中的每一行。表中的每一行。PRIMARY KEYPRIMARY KEY约束约束：不允许不允许数据库数据库表在指定主键约束的列（或组合列）表在指定主键约束的列（或组合列）上上具有相同的值具有相同的值，且，且不允许有空值不允许有空值，一个表一个表只能有一个只能有

8、一个PRIMARY KEYPRIMARY KEY约束。约束。例：图书编号例：图书编号(book_idbook_id)，借阅人编号，借阅人编号(borrower_idborrower_id)以及借书日期以及借书日期(lend_datelend_date)均有重复的数据记录，如果将三个字段组均有重复的数据记录，如果将三个字段组合起来作为主键，就能保证主键的唯一性。合起来作为主键，就能保证主键的唯一性。1 PRIMARY KEY 约束（主键约束）约束（主键约束）约束约束是一种强制数据完整性的是一种强制数据完整性的标准机制标准机制。book_idbook_idborrower_idborrower_i

9、dlend_datelend_dateback_dateback_date07890789960651 960651 2009-9-102009-9-102009-10-102009-10-100284 0284 9606519606512009-9-102009-9-102009-10-012009-10-010284 0284 9608119608112009-9-102009-9-102009-9-282009-9-28078907899606519606512009-10-102009-10-10NULLNULL 外键：外键：用于建立和加强两个表数据用于建立和加强两个表数据之间的链接的

10、一列或多列，通过将保存之间的链接的一列或多列，通过将保存表中主键值的一列或多列添加到另一个表中主键值的一列或多列添加到另一个表中，可创建两个表之间的链接，这个表中，可创建两个表之间的链接，这个列就成为第二个表的外键。列就成为第二个表的外键。被被FOREIGN KEY 参照的列在表参照的列在表中应该具有中应该具有PRIMARY KEY约束约束或或 UNIQUE 约束约束。例：例：lend_list表中的表中的 book_id 列是列是链接到链接到 book_info 表的外键表的外键。2 FOREIGN KEY 约束（外键约束）约束（外键约束）尽管外键约束的尽管外键约束的主要目的是控制存主要目的

11、是控制存储在外键表中的数据储在外键表中的数据，但它，但它还可以控制还可以控制对主键表中数据的修改对主键表中数据的修改。例：如果在例：如果在 book_infobook_info表中删除一本图书表中删除一本图书(book_idbook_id)，而这本书的，而这本书的book_idbook_id在在 lend_listlend_list 表中记录借书信息时使用了，那么这两个表之间表中记录借书信息时使用了，那么这两个表之间关联的完整性将被破坏，外键约束防止这种情况关联的完整性将被破坏，外键约束防止这种情况的发生。的发生。2 FOREIGN KEY 约束（外键约束）约束（外键约束）UNIQUE UNI

12、QUE 约束：约束：用以用以确保确保在在非主键列非主键列中中不输入重复值不输入重复值，但，但允许有空值允许有空值 。可以。可以在单一指定列和多个指定列上创建在单一指定列和多个指定列上创建UNIQUEUNIQUE约束。约束。在强制下面的唯一性时应使用在强制下面的唯一性时应使用 UNIQUE 约束而不是约束而不是 PRIMARY KEY 约束：约束：l非主键的一列或列组合非主键的一列或列组合 l允许空值的列 3 UNIQUE 约束约束 CHECK CHECK 约束：约束：通过逻辑表达式判断通过逻辑表达式判断限制插入限制插入到列中的值。到列中的值。CHECK 约束在约束在执行执行INSERT语句语句

13、或或UPDATE语句语句时作时作用。用。例：例：记录图书数量的记录图书数量的book_state表，表，图书总数量图书总数量number列必须满足大于等列必须满足大于等于于0，剩余图书数量，剩余图书数量leftnum列下限为列下限为0，上限为上限为 number列中所存值。列中所存值。4 CHECK 约束（检查约束）约束（检查约束）DEFAULT DEFAULT 约束：约束：当用户在向数据库表当用户在向数据库表中插入数据时，如果没有明确的提供输入中插入数据时，如果没有明确的提供输入值时，值时，SQL Server自动为该列自动为该列输入指定输入指定值值。5 DEFAULT 约束（默认约束）约束

14、（默认约束）使用使用DEFAULT约束时，应约束时，应注意注意：l 创建创建DEFAULT约束时，约束时，SQL Server将对表中将对表中现有的数据进行数据完整性验证。现有的数据进行数据完整性验证。l 表中的每一列上只能定义一个表中的每一列上只能定义一个 DEFAULT约束。约束。l DEFAULT 约束只在执行约束只在执行INSERT语句时起作用。语句时起作用。例：例：通常将数字型列的默认值指定通常将数字型列的默认值指定为零，将字符串列的默认值指定为为零，将字符串列的默认值指定为暂缺。暂缺。5.3 5.3 定义约束定义约束4 创建约束的其他选项创建约束的其他选项1 约束的创建约束的创建

15、2 查看约束的定义信息查看约束的定义信息 3 删除约束删除约束 5 使用默认使用默认6 使用规则使用规则1 约束的创建约束的创建（1 1）通过使用）通过使用CREATE TABLECREATE TABLE命令在建命令在建表时创建约束表时创建约束CREATE TABLE table_name(column_name data_type(NULL|NOT NULL)CONSTRAINT constraint_namePRIMARY KEY CLUSTERED|NONCLUSTERED|UNIQUE CLUSTERED|NONCLUSTERED|FOREIGN KEY REFERENCES ref_

16、table(ref_column)|DEFAULT constant_expression|CHECK(logical_expression),.)创建约束的表的名称创建约束的表的名称创建约束的列的名称创建约束的列的名称所在列的数据类型所在列的数据类型新建约束的名称新建约束的名称例例5-15-1：在：在Library_DBLibrary_DB数据库中建立用于数据库中建立用于记录图书借阅记录的表记录图书借阅记录的表lend_listlend_list。该表。该表中记录的信息有：图书编号，借阅者编号，中记录的信息有：图书编号，借阅者编号，借阅日期以及归还日期。借阅日期以及归还日期。字段名字段名类型

17、类型长度长度主键或外键主键或外键允许为空允许为空中文说明中文说明book_idbook_idnvarcharnvarchar1515PKPK、FKFK否否图书编号图书编号borrower_idborrower_idintintPKPK、FKFK否否借阅人编号借阅人编号lend_datelend_datedatedatePKPK否否借书日期借书日期back_dateback_datedatedate还书日期还书日期CREATE TABLE CREATE TABLE Library_DB.dbo.lend_listLibrary_DB.dbo.lend_list(book_idbook_id nv

18、archar(15)CONSTRAINT nvarchar(15)CONSTRAINT lend_bookid_chklend_bookid_chk REFERENCES REFERENCES book_info(book_idbook_info(book_id)NOT NULL,)NOT NULL,borrower_idborrower_id intint CONSTRAINT CONSTRAINT lend_id_chklend_id_chk REFERENCES REFERENCES borrower_info(idborrower_info(id)NOT NULL,)NOT NULL,

19、lend_datelend_date date NOT NULL,date NOT NULL,back_dateback_date date dateCONSTRAINT CONSTRAINT pk_chkpk_chk PRIMARY PRIMARY KEY(book_id,borrower_id,lend_dateKEY(book_id,borrower_id,lend_date)1 约束的创建约束的创建（2 2）建表完成后，再通过）建表完成后，再通过ADD CONSTRAINTADD CONSTRAINT语句添加约束语句添加约束 PRIMARY KEY 约束 ALTER TABLE tab

20、le_name ADD CONSTRAINT constraint_name PRIMARY KEY CLUSTERED|NONCLUSTERED(column_name,.)指定在指定在PRIMARY KEY约束所在列创建约束所在列创建聚集索引还是非聚集索引还是非聚集索引聚集索引 FOREIGN KEY 约束 ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name,.)REFERENCES ref_table(column_name,.)ON DELETE CASCADE|NO ACTION

21、 ON UPDATE CASCADE|NO ACTION被被FOREIGN KEY约束参约束参照的表的名照的表的名称称使用使用FOREIGN KEY 约束时，应注意:l 在在FOREIGN KEY语句中指定的列数和数据语句中指定的列数和数据类型必须和在类型必须和在REFERENCES子句中的列数和子句中的列数和数据类型数据类型相匹配相匹配。l 在同一张表中相互参照时，可以使用没有在同一张表中相互参照时，可以使用没有FOREIGN KEY子句的子句的REFERENCE子句。子句。UNIQUE 约束ALTER TABLE table_nameADD CONSTRAINT constraint_na

22、me UNIQUE CLUSTERED|NONCLUSTERED(column_name,.)例例5-25-2：在数据库：在数据库Library_DBLibrary_DB中，表中，表classclass上的上的namename列上添加一个列上添加一个UNIQUEUNIQUE约束。约束。use Library_DBALTER TABLE classADD CONSTRAINT U_classname UNIQUE(name)go CHECK 约束ALTER TABLE table_nameADD CONSTRAINT constraint_name CHECK（logical_expressio

23、n）,.检查约束的检查约束的逻辑表达式逻辑表达式例例5-35-3：在数据库：在数据库Library_DBLibrary_DB中，表中，表lend_listlend_list上的上的back_dateback_date列上添加一个列上添加一个CHECKCHECK约束。使得约束。使得归还日期大于等于借书日期归还日期大于等于借书日期 use Library_DBALTER TABLE lend_listADD CONSTRAINT backdate_chkCHECK(back_date=lend_date)go DEFAULT 约束ALTER TABLE table_nameADD CONSTRAI

24、NT constraint_name DEFAULT default_value FOR column_name,.默认约束默认约束的默认值的默认值例例5-45-4：在：在Library_DBLibrary_DB数据库中建立用于记录数据库中建立用于记录图书数量的表图书数量的表book_statebook_state。该表中记录的信息。该表中记录的信息有：图书编号，总数量，剩余库存量。有：图书编号，总数量，剩余库存量。创建创建book_state表：表：CREATE TABLE Library_DB.dbo.book_state (book_id nvarchar(15)NOT NULL,num

25、ber int NOT NULL,leftnum int NOT NULL )对对book_id加入外键约束，参考加入外键约束，参考book_info表的表的book_id列列：use Library_DB ALTER TABLE book_state ADD CONSTRAINT bookid_chk FOREIGN KEY(book_id)REFERENCES book_info(book_id)对对number和和leftnum两列加入两列加入CHECK约束约束：use Library_DB ALTER TABLE book_state ADD CONSTRAINT number_chk

26、 CHECK(number=0),CONSTRAINT leftnum_chk CHECK(leftnum=0 AND leftnum=number)对对number和和leftnum两列加入两列加入DEFAULT约束约束：use Library_DB ALTER TABLE book_state ADD CONSTRAINT DF_number DEFAULT 0 FOR number,CONSTRAINT DF_leftnum DEFAULT 0 FOR leftnum2 查看约束的定义信息查看约束的定义信息（1 1）使用系统存储过程）使用系统存储过程用户可以通过使用系统存储过程用户可以通

27、过使用系统存储过程sp_help，sp_helptext，sp_helpconstraint来查看来查看约束的相关信息。约束的相关信息。EXEC sp_help(sp_helptext,sp_helpconstraint)需要查看的需要查看的约束的名称约束的名称例例5-55-5：使用系统存储过程：使用系统存储过程sp_helptextsp_helptext 查看查看表表book_statebook_state 中，约束中，约束leftnumleftnum（剩余库存量）（剩余库存量）的的leftnumleftnum _ _chkchk定义文本信息。定义文本信息。use Library_DBEXE

28、C sp_helptext leftnum_chkgo2 查看约束的定义信息查看约束的定义信息（2 2）使用规划图）使用规划图用户可以通过查询系统规划视图用户可以通过查询系统规划视图check_constraints，referential_constraints，table_constraints得到约束的相关信息。得到约束的相关信息。例例5-65-6：使用系统规划视图：使用系统规划视图table _constraintstable _constraints查看数据库查看数据库Library_DBLibrary_DB上存在的所有约束的相上存在的所有约束的相关信息。关信息。use Librar

29、y_DBSELECT*from INFORMATION_SCHEMA.TABLE_CONSTRAINTSgo 2 查看约束的定义信息查看约束的定义信息（3 3）使用）使用Microsoft SQL Server Microsoft SQL Server Management Studio Management Studio管理界面查看约束管理界面查看约束 3 删除约束删除约束（1 1）利用）利用Microsoft SQL ServerMicrosoft SQL Server Management Studio Management Studio管理界面删除约束管理界面删除约束 在在“对象资源管

30、理器对象资源管理器”中中“选择选择”约束约束”，并选中并选中backdate_chk约束，右击，然后约束，右击，然后删除即可。删除即可。3 删除约束删除约束（2 2）利用系统函数）利用系统函数dropdrop删除约束删除约束 ALTER TABLE table_nameDROP CONSTRAINT constraint_name 例例5-75-7：使用系统命令删除表：使用系统命令删除表lend_listlend_list上定义上定义的的backdate_chkbackdate_chk约束。约束。use Library_DBALTER TABLE lend_listDROP CONSTRAIN

31、T backdate_chkgo可利用系统存储过程对约束的删除确认可利用系统存储过程对约束的删除确认EXEC sp_helpconstraint backdate_chk 4 创建约束的其它选项创建约束的其它选项（1 1）使用）使用WITH NOCHECKWITH NOCHECK选项选项 当用户需要在一个已经存在大量数据的表上当用户需要在一个已经存在大量数据的表上定义约束时，定义约束时，SQL Server将自动检验存在数将自动检验存在数据以证明它们满足约束的要求。此时，用户据以证明它们满足约束的要求。此时，用户可以通过使用可以通过使用“WITH NOCHECK”选项来禁选项来禁止止SQL S

32、erver对现有数据的约束检查。对现有数据的约束检查。能禁止约束检查的只有CHCEK和FOREIGN KEY约束。4 创建约束的其它选项创建约束的其它选项（1 1）使用）使用WITH NOCHECKWITH NOCHECK选项选项 ALTER TABLE table_nameWITH NOCHECKADD CONSTRAINT constraint_nameCHECK(logical_expression)FOREIGN KEY(column_name,.)REFERENCES reftable_name(refcol_name,.)例例5-85-8：假设数据库表：假设数据库表lend_lis

33、tlend_list中已经存在大中已经存在大量记录，我们需要在量记录，我们需要在back_dateback_date列上添加一个列上添加一个CHECKCHECK约束，以保证还书日期约束，以保证还书日期(back_dateback_date)大于大于等于借书日期等于借书日期(lend_datelend_date)。use Library_DBALTER TABLE lend_list WITH NOCHECKADD CONSTRAINT backdate_chk CHECK(back_date=lend_date)go4 创建约束的其它选项创建约束的其它选项（2 2）使用）使用NOCHECKNO

34、CHECK选项选项 当用户需要向在一张定义有约束的表中插入当用户需要向在一张定义有约束的表中插入新记录或修改记录时，新记录或修改记录时，SQL Server将自动检将自动检验新数据以确定它们满足表上约束的要求。验新数据以确定它们满足表上约束的要求。此时，用户可以通过使用此时，用户可以通过使用“NOCHECK”选项选项来禁止来禁止SQL Server对新数据的约束检查。对新数据的约束检查。能禁止约束检查的只有CHCEK和FOREIGN KEY约束。4 创建约束的其它选项创建约束的其它选项（2 2）使用）使用NOCHECKNOCHECK选项选项 ALTER TABLE table_nameCHECK|NOCHECKCONSTRAINT ALL|constraint_name,.例例5-95-9：设置数据库表：设置数据库表lend_listlend_list中约束中约束backdate_chkbackdate_chk的的NOCHECKNOCHECK选项，使得选项，使得SQL ServerSQL Server对即将插入的大量数据不作对即将插入的大量数据不作backdate_chkbackdate_chk的约的约束检查。束检查。use Library_DBALTER TABLE lend_list NOCHECK CONSTRAINT backdate_chk go