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

1、第五章第五章 数据完好性数据完好性 select_list所要查询的字段称号所要查询的字段称号INTO new_table_name将查询结果放到一个新的暂时表中将查询结果放到一个新的暂时表中FROM table_name欲查询数据的表的称号欲查询数据的表的称号WHERE condition欲查询数据的条件欲查询数据的条件GROUP BY clause根据字段类别做总计函数处置根据字段类别做总计函数处置HAVING clause预查询数据的条件预查询数据的条件ORDER BY将查询结果按某字段排序将查询结果按某字段排序COMPUTE clause在在SELECT查询的同时，作数据总计查询的同时

2、，作数据总计5.1 5.1 数据完好性的根本概念数据完好性的根本概念1 数据完好性的类型数据完好性的类型2 强迫数据完好性强迫数据完好性 1 1实体完好性实体完好性2 2域完好性域完好性3 3援用完好性援用完好性4 4用户定义完好性用户定义完好性1 数据完好性的类型数据完好性的类型 数据完好性是指存储在数据库数据完好性是指存储在数据库中的数据的一致性和准确性。中的数据的一致性和准确性。1 1实体完好性实体完好性 实体完好性要求表中一切的行具有实体完好性要求表中一切的行具有独一的标识符。独一的标识符。例：图书信息表例：图书信息表book_infobook_info中字段中字段book_idboo

3、k_id取值必需独一。取值必需独一。1 数据完好性的类型数据完好性的类型2 2域完好性域完好性 域完好性是指数据库表中对指定列域完好性是指数据库表中对指定列有效的输入值。有效的输入值。例：图书馆管理系统中，输入某例：图书馆管理系统中，输入某本图书的数量时不该当出现小于本图书的数量时不该当出现小于0 0本的情况。本的情况。1 数据完好性的类型数据完好性的类型3 3援用完好性援用完好性 援用完好性用于在输入或删除记录援用完好性用于在输入或删除记录时，坚持表之间已定义的关系。时，坚持表之间已定义的关系。例：对于例：对于 Library_DB Library_DB数据库中的数据库中的 book_inf

4、o book_info 表和表和 class class 表，援用完好性表，援用完好性基于基于 book_info book_info 表中的外键表中的外键 (class_id) (class_id) 与与 class class 表中的主键表中的主键 (class_id) (class_id) 之间之间的关系的关系 。1 数据完好性的类型数据完好性的类型4 4用户定义完好性用户定义完好性 用户定义完好性运用户可以定义不用户定义完好性运用户可以定义不属于其他任何完好性分类的特定业务规属于其他任何完好性分类的特定业务规那么。那么。2 强迫数据完好性强迫数据完好性 强迫数据完好性就是数据完好性的强

5、迫数据完好性就是数据完好性的实现。实现。SQL Server 2021 SQL Server 2021 经过以下机制来强迫经过以下机制来强迫列中数据的完好性列中数据的完好性 PRIMARY KEY PRIMARY KEY 约束约束 FOREIGN KEY FOREIGN KEY 约束约束 UNIQUE UNIQUE 约束约束 CHECK CHECK 约束约束 DEFAULT DEFAULT 约束约束 允许空值允许空值2 强迫数据完好性强迫数据完好性1 1声明数据完好性声明数据完好性 声明数据完好性是指定义数据规范声明数据完好性是指定义数据规范规定数据必需作为对象定义的一部分，规定数据必需作为对

6、象定义的一部分，SQL ServerSQL Server将自动确保数据符合规范。将自动确保数据符合规范。2 强迫数据完好性强迫数据完好性2 2过程定义数据完好性过程定义数据完好性 过程定义数据完好性是指经过编写过程定义数据完好性是指经过编写用来定义数据必需满足的规范和强迫该用来定义数据必需满足的规范和强迫该规范的脚本来实现数据完好性。规范的脚本来实现数据完好性。5.2 5.2 约束的类型约束的类型1 PRIMARY KEY 约束约束2 FOREIGN KEY 约束约束3 UNIQUE 约束约束4 CHECK 约束约束5 DEFAULT 约束约束 主键：表中的一列或列的组合，主键：表中的一列或列

7、的组合，其值能独一地标识表中的每一行。其值能独一地标识表中的每一行。 PRIMARY KEY PRIMARY KEY约束：不允许数据库约束：不允许数据库表在指定主键约束的列或组合列表在指定主键约束的列或组合列上具有一样的值，且不允许有空值，上具有一样的值，且不允许有空值，一个表只能有一个一个表只能有一个PRIMARY KEYPRIMARY KEY约束。约束。例：图书编号例：图书编号(book_id)(book_id)，借阅人编号，借阅人编号(borrower_id)(borrower_id)以及借书日期以及借书日期(lend_date)(lend_date)均有反复的数据记录，假设将三个字段组

8、均有反复的数据记录，假设将三个字段组合起来作为主键，就能保证主键的独一性。合起来作为主键，就能保证主键的独一性。 1 PRIMARY KEY 约束主键约束约束主键约束 约束是一种强迫数据完好性的约束是一种强迫数据完好性的规范机制。规范机制。book_idbook_idborrower_idborrower_idlend_datelend_dateback_dateback_date07890789960651 960651 2009-9-102009-9-102009-10-102009-10-100284 0284 9606519606512009-9-102009-9-102009-10-

9、012009-10-010284 0284 9608119608112009-9-102009-9-102009-9-282009-9-28078907899606519606512009-10-102009-10-10NULLNULL 外键：用于建立和加强两个表数据外键：用于建立和加强两个表数据之间的链接的一列或多列，经过将保管之间的链接的一列或多列，经过将保管表中主键值的一列或多列添加到另一个表中主键值的一列或多列添加到另一个表中，可创建两个表之间的链接，这个表中，可创建两个表之间的链接，这个列就成为第二个表的外键。列就成为第二个表的外键。 被被FOREIGN KEY FOREIGN KE

10、Y 参照的列在表参照的列在表中应该具有中应该具有PRIMARY KEYPRIMARY KEY约束或约束或 UNIQUE UNIQUE 约束。约束。 例：例：lend_listlend_list表中的表中的 book_id book_id 列是列是链接到链接到 book_info book_info 表的外键表的外键 。2 FOREIGN KEY 约束外键约束约束外键约束 虽然外键约束的主要目的是控制存虽然外键约束的主要目的是控制存储在外键表中的数据，但它还可以控制储在外键表中的数据，但它还可以控制对主键表中数据的修正。对主键表中数据的修正。 例：假设在例：假设在 book_info book_

11、info表中删除一本图书表中删除一本图书(book_id)(book_id)，而这本书的，而这本书的book_idbook_id在在 lend_list lend_list 表中记录借书信息时运用了，那么这两个表之间表中记录借书信息时运用了，那么这两个表之间关联的完好性将被破坏，外键约束防止这种情况关联的完好性将被破坏，外键约束防止这种情况的发生。的发生。 2 FOREIGN KEY 约束外键约束约束外键约束 UNIQUE UNIQUE 约束：用以确保在非主键列约束：用以确保在非主键列中不输入反复值，但允许有空值中不输入反复值，但允许有空值 。可以。可以在单一指定列和多个指定列上创建在单一指定

12、列和多个指定列上创建UNIQUEUNIQUE约束。约束。 在强迫下面的独一性时应运用在强迫下面的独一性时应运用 UNIQUE 约束而不是约束而不是 PRIMARY KEY 约束：约束： 非主键的一列或列组合非主键的一列或列组合 允许空值的列允许空值的列 3 UNIQUE 约束约束 CHECK CHECK 约束：经过逻辑表达式判别约束：经过逻辑表达式判别限制插入到列中的值。限制插入到列中的值。CHECK CHECK 约束在执约束在执行行INSERTINSERT语句或语句或UPDATEUPDATE语句时作用。语句时作用。 例：记录图书数量的例：记录图书数量的book_statebook_state

13、表，图表，图书总数量书总数量numbernumber列必需满足大于等于列必需满足大于等于0 0，剩余图书数量剩余图书数量leftnumleftnum列下限为列下限为0 0，上限，上限为为 number number列中所存值。列中所存值。 4 CHECK 约束检查约束约束检查约束 DEFAULT DEFAULT 约束：当用户在向数据库表约束：当用户在向数据库表中插入数据时，假设没有明确的提供输入中插入数据时，假设没有明确的提供输入值时，值时，SQL ServerSQL Server自动为该列输入指定值。自动为该列输入指定值。 5 DEFAULT 约束默许约束约束默许约束运用运用DEFAULT约

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

15、息 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_table (ref_column)|

16、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 Library_DB.dbo.lend_listCREATE TABLE Library_DB.dbo.lend_list( (book_id nvarchar(15) CONSTRAINT

18、 lend_bookid_chk book_id nvarchar(15) CONSTRAINT lend_bookid_chk REFERENCES book_info(book_id) NOT NULL,REFERENCES book_info(book_id) NOT NULL,borrower_id int CONSTRAINT lend_id_chk borrower_id int CONSTRAINT lend_id_chk REFERENCES borrower_info(id) NOT NULL,REFERENCES borrower_info(id) NOT NULL,len

19、d_date date NOT NULL,lend_date date NOT NULL,back_date dateback_date dateCONSTRAINT pk_chk PRIMARY CONSTRAINT pk_chk PRIMARY KEY(book_id,borrower_id,lend_date) KEY(book_id,borrower_id,lend_date) ) )1 约束的创建约束的创建2 2建表完成后，再经过建表完成后，再经过ADD CONSTRAINTADD CONSTRAINT语句添加约束语句添加约束 PRIMARY KEY 约束 ALTER TABLE t

20、able_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|

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

22、TRAINT constraint_name 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_n

23、ame CHECK logical_expression ,.检查约束的检查约束的逻辑表达式逻辑表达式例例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 约束约束ALTE

24、R TABLE table_nameADD CONSTRAINT 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 (

25、 book_id nvarchar(15) NOT NULL, number 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

26、TABLE book_state ADD CONSTRAINT number_chk 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 查看约束的定义信息查

27、看约束的定义信息1 1运用系统存储过程运用系统存储过程用户可以经过运用系统存储过程用户可以经过运用系统存储过程sp_help，sp_helptext，sp_helpconstraint来查看来查看约束的相关信息。约束的相关信息。 EXEC sp_help (sp_helptext,sp_helpconstraint) 需求查看的需求查看的约束的称号约束的称号例例5-55-5：运用系统存储过程：运用系统存储过程sp_helptext sp_helptext 查看查看表表book_state book_state 中，约束中，约束leftnumleftnum剩余库存量剩余库存量的的leftnum

28、_chkleftnum _chk定义文本信息。定义文本信息。 use Library_DBEXEC sp_helptext leftnum_chkgo2 查看约束的定义信息查看约束的定义信息2 2运用规划图运用规划图用户可以经过查询系统规划视图用户可以经过查询系统规划视图check_constraints，referential_constraints，table_constraints得到约束的相关信息。得到约束的相关信息。 例例5-65-6：运用系统规划视图：运用系统规划视图table _constraintstable _constraints查看数据库查看数据库Library_DBLi

29、brary_DB上存在的一切约束的相上存在的一切约束的相关信息。关信息。 use Library_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

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

31、ry_DBALTER TABLE lend_listDROP CONSTRAINT backdate_chkgo可利用系统存储过程对约束的删除确认可利用系统存储过程对约束的删除确认EXEC sp_helpconstraint backdate_chk 4 创建约束的其它选项创建约束的其它选项1 1运用运用WITH NOCHECKWITH NOCHECK选项选项 当用户需求在一个曾经存在大量数据的表上当用户需求在一个曾经存在大量数据的表上定义约束时，定义约束时，SQL Server将自动检验存在数将自动检验存在数据以证明它们满足约束的要求。此时，用户据以证明它们满足约束的要求。此时，用户可以经过运用可以经过运用“WITH NOCHECK选项来选项来制止制止SQL Server对现有数据的约束检查。对现有数据的约束检查。 能制止约束检查的只需CHCEK和FOREIGN KEY约束。 4 创建约束的其它选项创建约束的其它选项1 1运用运用WITH NOCHECKWITH NOCHECK选项选项 ALTER TABLE table_nameWITH N