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

1,第五章 数据完整性,2,复习,SELECT查询的基本格式是什么？具有哪些参数？各参数的含义是什么？,3,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欲查询数据的条件,GROUP BY clause根据字段类别做总计函数处理,HAVING clause预查询数据的条件,ORDER BY将查询结果按某字段排序,COMPUTE clause在SELECT查询的同时，作数据总计,4,本章主要内容,5.1 数据完整性的基本概念5.2 定义约束5.3 约束的类型,5,5.1 数据完整性的基本概念,1 数据完整性的类型,2 强制数据完整性,（1）实体完整性（2）域完整性（3）引用完整性（4）用户定义完整性,6,1 数据完整性的类型,数据完整性是指存储在数据库中的数据的一致性和准确性。,（1）实体完整性 实体完整性要求表中所有的行具有唯一的标识符。,例：图书信息表book_info中字段book_id取值必须唯一。,7,1 数据完整性的类型,（2）域完整性 域完整性是指数据库表中对指定列有效的输入值。,例：图书馆管理系统中，输入某本图书的数量时不应当出现小于0本的情况。,8,1 数据完整性的类型,（3）引用完整性 引用完整性用于在输入或删除记录时，保持表之间已定义的关系。,例：对于 Library_DB数据库中的 book_info 表和 class 表，引用完整性基于 book_info 表中的外键(class_id)与 class 表中的主键(class_id)之间的关系。,9,1 数据完整性的类型,（4）用户定义完整性 用户定义完整性使用户能够定义不属于其他任何完整性分类的特定业务规则。,10,2 强制数据完整性,强制数据完整性就是数据完整性的实现。,SQL Server 2008 通过下列机制来强制列中数据的完整性 PRIMARY KEY 约束 FOREIGN KEY 约束 UNIQUE 约束 CHECK 约束 DEFAULT 约束 允许空值,11,2 强制数据完整性,（1）声明数据完整性 声明数据完整性是指定义数据标准规定数据必须作为对象定义的一部分，SQL Server将自动确保数据符合标准。,12,2 强制数据完整性,（2）过程定义数据完整性 过程定义数据完整性是指通过编写用来定义数据必须满足的标准和强制该标准的脚本来实现数据完整性。,13,5.2 约束的类型,1 PRIMARY KEY 约束,2 FOREIGN KEY 约束,3 UNIQUE 约束,4 CHECK 约束,5 DEFAULT 约束,14,主键：表中的一列或列的组合，其值能唯一地标识表中的每一行。PRIMARY KEY约束：不允许数据库表在指定主键约束的列（或组合列）上具有相同的值，且不允许有空值，一个表只能有一个PRIMARY KEY约束。,例：图书编号(book_id)，借阅人编号(borrower_id)以及借书日期(lend_date)均有重复的数据记录，如果将三个字段组合起来作为主键，就能保证主键的唯一性。,1 PRIMARY KEY 约束（主键约束）,约束是一种强制数据完整性的标准机制。,15,外键：用于建立和加强两个表数据之间的链接的一列或多列，通过将保存表中主键值的一列或多列添加到另一个表中，可创建两个表之间的链接，这个列就成为第二个表的外键。被FOREIGN KEY 参照的列在表中应该具有PRIMARY KEY约束或 UNIQUE 约束。,例：lend_list表中的 book_id 列是链接到 book_info 表的外键。,2 FOREIGN KEY 约束（外键约束）,16,尽管外键约束的主要目的是控制存储在外键表中的数据，但它还可以控制对主键表中数据的修改。,例：如果在 book_info表中删除一本图书(book_id)，而这本书的book_id在 lend_list 表中记录借书信息时使用了，那么这两个表之间关联的完整性将被破坏，外键约束防止这种情况的发生。,2 FOREIGN KEY 约束（外键约束）,17,UNIQUE 约束：用以确保在非主键列中不输入重复值，但允许有空值。可以在单一指定列和多个指定列上创建UNIQUE约束。,在强制下面的唯一性时应使用 UNIQUE 约束而不是 PRIMARY KEY 约束：非主键的一列或列组合 允许空值的列,3 UNIQUE 约束,18,CHECK 约束：通过逻辑表达式判断限制插入到列中的值。CHECK 约束在执行INSERT语句或UPDATE语句时作用。,例：记录图书数量的book_state表，图书总数量number列必须满足大于等于0，剩余图书数量leftnum列下限为0，上限为 number列中所存值。,4 CHECK 约束（检查约束）,19,DEFAULT 约束：当用户在向数据库表中插入数据时，如果没有明确的提供输入值时，SQL Server自动为该列输入指定值。,5 DEFAULT 约束（默认约束）,使用DEFAULT约束时，应注意：创建DEFAULT约束时，SQL Server将对表中现有的数据进行数据完整性验证。表中的每一列上只能定义一个 DEFAULT约束。DEFAULT 约束只在执行INSERT语句时起作用。,例：通常将数字型列的默认值指定为零，将字符串列的默认值指定为暂缺。,20,5.3 定义约束,4 创建约束的其他选项,1 约束的创建,2 查看约束的定义信息,3 删除约束,5 使用默认,6 使用规则,21,1 约束的创建,（1）通过使用CREATE 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)|DEFAULT constant_expression|CHECK(logical_expression),.),22,例5-1：在Library_DB数据库中建立用于记录图书借阅记录的表lend_list。该表中记录的信息有：图书编号，借阅者编号，借阅日期以及归还日期。,23,CREATE TABLE(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,lend_date date NOT NULL,back_date dateCONSTRAINT pk_chk PRIMARY KEY(book_id,borrower_id,lend_date),24,1 约束的创建,（2）建表完成后，再通过ADD CONSTRAINT语句添加约束,PRIMARY KEY 约束,ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY CLUSTERED|NONCLUSTERED(column_name,.),指定在PRIMARY KEY约束所在列创建聚集索引还是非聚集索引,25,FOREIGN 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约束参照的表的名称,26,使用FOREIGN KEY 约束时，应注意:,在FOREIGN KEY语句中指定的列数和数据类型必须和在REFERENCES子句中的列数和数据类型相匹配。在同一张表中相互参照时，可以使用没有FOREIGN KEY子句的REFERENCE子句。,27,UNIQUE 约束,ALTER TABLE table_nameADD CONSTRAINT constraint_name UNIQUE CLUSTERED|NONCLUSTERED(column_name,.),28,例5-2：在数据库Library_DB中，表class上的name列上添加一个UNIQUE约束。,use Library_DBALTER TABLE classADD CONSTRAINT U_classname UNIQUE(name)go,29,CHECK 约束,ALTER TABLE table_nameADD CONSTRAINT constraint_name CHECK（logical_expression）,.,检查约束的逻辑表达式,30,例5-3：在数据库Library_DB中，表lend_list上的back_date列上添加一个CHECK约束。使得归还日期大于等于借书日期,use Library_DBALTER TABLE lend_listADD CONSTRAINT backdate_chkCHECK(back_date=lend_date)go,31,DEFAULT 约束,ALTER TABLE table_nameADD CONSTRAINT constraint_name DEFAULT default_value FOR column_name,.,默认约束的默认值,32,例5-4：在Library_DB数据库中建立用于记录图书数量的表book_state。该表中记录的信息有：图书编号，总数量，剩余库存量。,创建book_state表：CREATE TABLE(book_id nvarchar(15)NOT NULL,number int NOT NULL,leftnum int NOT NULL),33,对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),34,对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),35,对number和leftnum两列加入DEFAULT约束：use Library_DB ALTER TABLE book_state ADD CONSTRAINT DF_number DEFAULT 0 FOR number,CONSTRAINT DF_leftnum DEFAULT 0 FOR leftnum,36,2 查看约束的定义信息,（1）使用系统存储过程,用户可以通过使用系统存储过程sp_help，sp_helptext，sp_helpconstraint来查看约束的相关信息。,EXEC sp_help(sp_helptext,sp_helpconstraint),需要查看的约束的名称,37,例5-5：使用系统存储过程sp_helptext 查看表book_state 中，约束leftnum（剩余库存量）的leftnum _chk定义文本信息。,use Library_DBEXEC sp_helptext leftnum_chkgo,38,2 查看约束的定义信息,（2）使用规划图,用户可以通过查询系统规划视图check_constraints，referential_constraints，table_constraints得到约束的相关信息。,39,例5-6：使用系统规划视图table _constraints查看数据库Library_DB上存在的所有约束的相关信息。,use Library_DBSELECT*from INFORMATION_SCHEMA.TABLE_CONSTRAINTSgo,40,2 查看约束的定义信息,（3）使用Microsoft SQL Server Management Studio管理界面查看约束,41,3 删除约束,（1）利用Microsoft SQL Server Management Studio管理界面删除约束,在“对象资源管理器”中“选择”约束”，并选中backdate_chk约束，右击，然后删除即可。,42,3 删除约束,（2）利用系统函数drop删除约束,ALTER TABLE table_nameDROP CONSTRAINT constraint_name,43,例5-7：使用系统命令删除表lend_list上定义的backdate_chk约束。,use Library_DBALTER TABLE lend_listDROP CONSTRAINT backdate_chkgo,可利用系统存储过程对约束的删除确认EXEC sp_helpconstraint backdate_chk,44,4 创建约束的其它选项,（1）使用WITH NOCHECK选项,当用户需要在一个已经存在大量数据的表上定义约束时，SQL Server将自动检验存在数据以证明它们满足约束的要求。此时，用户可以通过使用“WITH NOCHECK”选项来禁止SQL Server对现有数据的约束检查。,能禁止约束检查的只有CHCEK和FOREIGN KEY约束。,45,4 创建约束的其它选项,（1）使用WITH NOCHECK选项,ALTER TABLE table_nameWITH NOCHECKADD CONSTRAINT constraint_nameCHECK(logical_expression)FOREIGN KEY(column_name,.)REFERENCES reftable_name(refcol_name,.),46,例5-8：假设数据库表lend_list中已经存在大量记录，我们需要在back_date列上添加一个CHECK约束，以保证还书日期(back_date)大于等于借书日期(lend_date)。,use Library_DBALTER TABLE lend_list WITH NOCHECKADD CONSTRAINT backdate_chk CHECK(back_date=lend_date)go,47,4 创建约束的其它选项,（2）使用NOCHECK选项,当用户需要向在一张定义有约束的表中插入新记录或修改记录时，SQL Server将自动检验新数据以确定它们满足表上约束的要求。此时，用户可以通过使用“NOCHECK”选项来禁止SQL Server对新数据的约束检查。,能禁止约束检查的只有CHCEK和FOREIGN KEY约束。,48,4 创建约束的其它选项,（2）使用NOCHECK选项,ALTER TABLE table_nameCHECK|NOCHECKCONSTRAINT ALL|constraint_name,.,49,例5-9：设置数据库表lend_list中约束backdate_chk的NOCHECK选项，使得SQL Server对即将插入的大量数据不作backdate_chk的约束检查。,use Library_DBALTER TABLE lend_list NOCHECK CONSTRAINT backdate_chk go,