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

1、第五章 数据完整性 1复习SELECT查询的基本格式是什么？具有哪些参数？各参数的含义是什么？2SELECT 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欲查询数据的条件GROUP BY cla

2、use根据字段类别做总计函数处理HAVING clause预查询数据的条件ORDER BY将查询结果按某字段排序COMPUTE clause在SELECT查询的同时，作数据总计3本章主要内容5.1 数据完整性的基本概念5.2 定义约束5.3 约束的类型45.1 数据完整性的基本概念1 数据完整性的类型2 强制数据完整性 （1）实体完整性（2）域完整性（3）引用完整性（4）用户定义完整性51 数据完整性的类型 数据完整性是指存储在数据库中的数据的一致性和准确性。（1）实体完整性 实体完整性要求表中所有的行具有唯一的标识符。例：图书信息表book_info中字段book_id取值必须唯一。61 数

3、据完整性的类型（2）域完整性 域完整性是指数据库表中对指定列有效的输入值。例：图书馆管理系统中，输入某本图书的数量时不应当出现小于0本的情况。71 数据完整性的类型（3）引用完整性 引用完整性用于在输入或删除记录时，保持表之间已定义的关系。例：对于 Library_DB数据库中的 book_info 表和 class 表，引用完整性基于 book_info 表中的外键 (class_id) 与 class 表中的主键 (class_id) 之间的关系 。81 数据完整性的类型（4）用户定义完整性 用户定义完整性使用户能够定义不属于其他任何完整性分类的特定业务规则。92 强制数据完整性 强制数据

4、完整性就是数据完整性的实现。SQL Server 2008 通过下列机制来强制列中数据的完整性 PRIMARY KEY 约束 FOREIGN KEY 约束 UNIQUE 约束 CHECK 约束 DEFAULT 约束 允许空值102 强制数据完整性（1）声明数据完整性 声明数据完整性是指定义数据标准规定数据必须作为对象定义的一部分，SQL Server将自动确保数据符合标准。112 强制数据完整性（2）过程定义数据完整性 过程定义数据完整性是指通过编写用来定义数据必须满足的标准和强制该标准的脚本来实现数据完整性。125.2 约束的类型1 PRIMARY KEY 约束2 FOREIGN KEY 约

5、束3 UNIQUE 约束4 CHECK 约束5 DEFAULT 约束13 主键：表中的一列或列的组合，其值能唯一地标识表中的每一行。 PRIMARY KEY约束：不允许数据库表在指定主键约束的列（或组合列）上具有相同的值，且不允许有空值，一个表只能有一个PRIMARY KEY约束。例：图书编号(book_id)，借阅人编号(borrower_id)以及借书日期(lend_date)均有重复的数据记录，如果将三个字段组合起来作为主键，就能保证主键的唯一性。 1 PRIMARY KEY 约束（主键约束） 约束是一种强制数据完整性的标准机制。book_idborrower_idlend_dateba

6、ck_date0789960651 2009-9-102009-10-100284 9606512009-9-102009-10-010284 9608112009-9-102009-9-2807899606512009-10-10NULL14 外键：用于建立和加强两个表数据之间的链接的一列或多列，通过将保存表中主键值的一列或多列添加到另一个表中，可创建两个表之间的链接，这个列就成为第二个表的外键。 被FOREIGN KEY 参照的列在表中应该具有PRIMARY KEY约束或 UNIQUE 约束。 例：lend_list表中的 book_id 列是链接到 book_info 表的外键 。2 F

7、OREIGN KEY 约束（外键约束）15 尽管外键约束的主要目的是控制存储在外键表中的数据，但它还可以控制对主键表中数据的修改。 例：如果在 book_info表中删除一本图书(book_id)，而这本书的book_id在 lend_list 表中记录借书信息时使用了，那么这两个表之间关联的完整性将被破坏，外键约束防止这种情况的发生。 2 FOREIGN KEY 约束（外键约束）16 UNIQUE 约束：用以确保在非主键列中不输入重复值，但允许有空值 。可以在单一指定列和多个指定列上创建UNIQUE约束。 在强制下面的唯一性时应使用 UNIQUE 约束而不是 PRIMARY KEY 约束：

8、非主键的一列或列组合 允许空值的列 3 UNIQUE 约束17 CHECK 约束：通过逻辑表达式判断限制插入到列中的值。CHECK 约束在执行INSERT语句或UPDATE语句时作用。 例：记录图书数量的book_state表，图书总数量number列必须满足大于等于0，剩余图书数量leftnum列下限为0，上限为 number列中所存值。 4 CHECK 约束（检查约束）18 DEFAULT 约束：当用户在向数据库表中插入数据时，如果没有明确的提供输入值时，SQL Server自动为该列输入指定值。 5 DEFAULT 约束（默认约束）使用DEFAULT约束时，应注意： 创建DEFAULT约

9、束时，SQL Server将对表中现有的数据进行数据完整性验证。 表中的每一列上只能定义一个 DEFAULT约束。 DEFAULT 约束只在执行INSERT语句时起作用。例：通常将数字型列的默认值指定为零，将字符串列的默认值指定为暂缺。 195.3 定义约束4 创建约束的其他选项1 约束的创建 2 查看约束的定义信息 3 删除约束 5 使用默认6 使用规则201 约束的创建（1）通过使用CREATE TABLE命令在建表时创建约束CREATE TABLE table_name(column_name data_type (NULL|NOT NULL)CONSTRAINT constraint_

10、namePRIMARY KEY CLUSTERED|NONCLUSTERED|UNIQUE CLUSTERED|NONCLUSTERED|FOREIGN KEY REFERENCES ref_table (ref_column)|DEFAULT constant_expression|CHECK (logical_expression),.)创建约束的表的名称创建约束的列的名称所在列的数据类型新建约束的名称21例5-1：在Library_DB数据库中建立用于记录图书借阅记录的表lend_list。该表中记录的信息有：图书编号，借阅者编号，借阅日期以及归还日期。 字段名类型长度主键或外键允许为空

11、中文说明book_idnvarchar15PK、FK否图书编号borrower_idintPK、FK否借阅人编号lend_datedatePK否借书日期back_datedate还书日期22CREATE TABLE Library_DB.dbo.lend_list(book_id nvarchar(15) CONSTRAINT lend_bookid_chk REFERENCES book_info(book_id) NOT NULL,borrower_id int CONSTRAINT lend_id_chk REFERENCES borrower_info(id) NOT NULL,len

12、d_date date NOT NULL,back_date dateCONSTRAINT pk_chk PRIMARY KEY(book_id,borrower_id,lend_date) )231 约束的创建（2）建表完成后，再通过ADD CONSTRAINT语句添加约束 PRIMARY KEY 约束 ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY CLUSTERED|NONCLUSTERED (column_name,.) 指定在PRIMARY KEY约束所在列创建聚集索引还是非聚集索引24 FOREIG

13、N KEY 约束 ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name,.) REFERENCES ref_table (column_name,.) ON DELETE CASCADE|NO ACTION ON UPDATE CASCADE|NO ACTION被FOREIGN KEY约束参照的表的名称25使用FOREIGN KEY 约束时，应注意: 在FOREIGN KEY语句中指定的列数和数据类型必须和在REFERENCES子句中的列数和数据类型相匹配。 在同一张表中相互参照时，可以使用

14、没有FOREIGN KEY子句的REFERENCE子句。 26 UNIQUE 约束ALTER TABLE table_nameADD CONSTRAINT constraint_name UNIQUE CLUSTERED|NONCLUSTERED (column_name ,.) 27例5-2：在数据库Library_DB中，表class上的name列上添加一个UNIQUE约束。 use Library_DBALTER TABLE classADD CONSTRAINT U_classname UNIQUE (name)go28 CHECK 约束ALTER TABLE table_nameAD

15、D CONSTRAINT constraint_name CHECK （logical_expression） ,.检查约束的逻辑表达式29例5-3：在数据库Library_DB中，表lend_list上的back_date列上添加一个CHECK约束。使得归还日期大于等于借书日期 use Library_DBALTER TABLE lend_listADD CONSTRAINT backdate_chkCHECK (back_date=lend_date)go30 DEFAULT 约束ALTER TABLE table_nameADD CONSTRAINT constraint_name DE

16、FAULT default_value FOR column_name,.默认约束的默认值31例5-4：在Library_DB数据库中建立用于记录图书数量的表book_state。该表中记录的信息有：图书编号，总数量，剩余库存量。 创建book_state表： CREATE TABLE Library_DB.dbo.book_state ( book_id nvarchar(15) NOT NULL, number int NOT NULL, leftnum int NOT NULL )32对book_id加入外键约束，参考book_info表的book_id列 ： use Library_D

17、B ALTER TABLE book_state ADD CONSTRAINT bookid_chk FOREIGN KEY(book_id) REFERENCES book_info(book_id) 33对number和leftnum两列加入CHECK约束 ： use Library_DB ALTER TABLE book_state ADD CONSTRAINT number_chk CHECK (number=0), CONSTRAINT leftnum_chk CHECK (leftnum=0 AND leftnum=number) 34对number和leftnum两列加入DEFA

18、ULT约束 ： use Library_DB ALTER TABLE book_state ADD CONSTRAINT DF_number DEFAULT 0 FOR number, CONSTRAINT DF_leftnum DEFAULT 0 FOR leftnum352 查看约束的定义信息（1）使用系统存储过程用户可以通过使用系统存储过程sp_help，sp_helptext，sp_helpconstraint来查看约束的相关信息。 EXEC sp_help (sp_helptext,sp_helpconstraint) 需要查看的约束的名称36例5-5：使用系统存储过程sp_help

19、text 查看表book_state 中，约束leftnum（剩余库存量）的leftnum _chk定义文本信息。 use Library_DBEXEC sp_helptext leftnum_chkgo372 查看约束的定义信息（2）使用规划图用户可以通过查询系统规划视图check_constraints，referential_constraints，table_constraints得到约束的相关信息。 38例5-6：使用系统规划视图table _constraints查看数据库Library_DB上存在的所有约束的相关信息。 use Library_DBSELECT * from IN

20、FORMATION_SCHEMA.TABLE_CONSTRAINTSgo 392 查看约束的定义信息（3）使用Microsoft SQL Server Management Studio管理界面查看约束 403 删除约束（1）利用Microsoft SQL Server Management Studio管理界面删除约束 在“对象资源管理器”中“选择”约束”，并选中backdate_chk约束，右击，然后删除即可。 413 删除约束（2）利用系统函数drop删除约束 ALTER TABLE table_nameDROP CONSTRAINT constraint_name 42例5-7：使用系

21、统命令删除表lend_list上定义的backdate_chk约束。 use Library_DBALTER TABLE lend_listDROP CONSTRAINT backdate_chkgo可利用系统存储过程对约束的删除确认EXEC sp_helpconstraint backdate_chk 434 创建约束的其它选项（1）使用WITH NOCHECK选项 当用户需要在一个已经存在大量数据的表上定义约束时，SQL Server将自动检验存在数据以证明它们满足约束的要求。此时，用户可以通过使用“WITH NOCHECK”选项来禁止SQL Server对现有数据的约束检查。 能禁止约束检查的只有CHCEK和FOREIGN KEY约束。 444 创建约束的其它选项（1）使用WITH NOCHECK选项 ALTER TABLE table_nameWITH NOCHECKADD CONSTRAINT constraint_nameCHECK (logic