数据库完整ppt课件

1、第五章第五章 数据库完好性数据库完好性数据库完好性数据库完好性v 数据是数据库的中心，数据的正确和完好与否将直接影响数据库的运用。v 数据的完好性和平安性是两个不同概念；v 数据的平安性v 维护数据库防止恶意的破坏和非法的存取v 防备对象：非法用户和非法操作v 数据的完好性v 防止数据库中存在不符合语义的数据，也就是防止数据库中存在不正确的数据v 防备对象：不合语义的、不正确的数据本次课内容本次课内容v数据完好性概述v在SQL Server 2005中实现数据完好性v其他相关知识v触发器v1、DBMS必需可以提供v提供定义完好性约束条件的机制v完好性约束条件 = 完好性规那么，由SQL的ddl

2、语句来实现；v提供完好性检查的机制v检查数据能否满足完好性约束条件的机制；v违约处置v假设用户操作违反完好性约束条件，采取一定的动作，如回绝执行NO ACTION、级联执行或其他操作；一、数据库完好性概述一、数据库完好性概述v2、数据库完好性约束机制分类v完好性约束条件作用的对象有关系、元组、列三种；可分为：v实体完好性：实体完好性是为了保证表中的数据独一；v参照完好性：参照完好性用于确保相关联的表间的数据应坚持一致，防止因一个表的记录修正，呵斥另一个表的内容变为无效的值普通来说，参照完好性是经过外键和主键来维护的；v域完好性：域完好性可以保证数据的取值在有效的范围内；v用户定义完好性：由用户

3、自行定义的，不同于前面种的完好性，也可以说一种强迫数据定义；1实体完好性实体完好性v 实体Entity：是数据逻辑模型中的一个概念。现实世界是一组实体的根本对象和这些对象的联络Relation构成的。在数据库中，我们可以了解一条记录就是一个实体。v 实体完好性：在现实世界中，任何一个实体都有区别于其他实体的特征，即在数据库中，一切的记录都应该有独一的标识，这就是实体完好性的含义规那么2.1：实体完好性规那么。v 常见的实体完好性机制包括：v 主键Primary Key；独一码Unique；2参照完好性参照完好性v 参照完好性是指在两个表的主键和外键之间数据的完好性，其含义包括：v 参照完好性保

4、证被参照表和参照表之间数据的一致性；v 可以防止数据丧失或者无意义的数据；v 可以制止在从表中插入被参照表中不存在的关键字的记录。v 参照完好性的常见实现机制包括：v 外键Foreign Key、检查Check、触发器Trigger；存储过程Stored Procedurev 在输入或删除记录时，可以用来坚持一切表之间定义的关系，以确保键值在一切表中一致。v 关系模型的参照完好性定义v 在CREATE TABLE中用FOREIGN KEY短语定义哪些列为外码v 用REFERENCES短语指明这些外码参照哪些表的主码3域完好性域完好性v 域：指列字段，所以域完好性就是指列的完好性；v 域完好性要

5、求列字段的数据具有正确的数据类型、格式和有效的数值范围。v 域完好性的常见实现机制包括：v 默许值Defaultv 检查Checkv 数据类型Data typev 规那么Rule4用户自定义完好性用户自定义完好性v 用户定义的完好性就是针对某一详细运用的数据必需满足的语义要求；可直接由RDBMS提供，而不用由运用程序承当；系统将实现数据完好性的要求直接定义在表上或列上。v 常见的实现机制包括：v 规那么Rule、创建数据表时的一切约束Constraint、触发器Trigger、存储过程Stored Produre；二、在二、在SQL Server 2005中实现数据完好性中实现数据完好性vSQ

6、L Server 2005提供了完善的数据完好性机制，主要包括约束、默许值和规那么3类；v创建及管理约束及约束对象 v默许值约束及默许值对象v创建及管理规那么对象1、创建及管理约束及约束对象、创建及管理约束及约束对象v 主键Primary key约束v 独一值Unique约束v 外键Foreign key约束v 检查Check约束1创建及删除主键约束及独一值约束创建及删除主键约束及独一值约束v单属性构成的码的两种阐明方法单属性构成的码的两种阐明方法 ：v定义为列级约束条件，定义为表级约束条件定义为列级约束条件，定义为表级约束条件v多个属性构成的码只需一种阐明方法：多个属性构成的码只需一种阐明方

7、法：v定义为表级约束条件定义为表级约束条件v创建约束方式：创建约束方式：v创建表时同时创建创建表时同时创建primary key约束或约束或unique约束约束v修正表时同时创建修正表时同时创建primary key约束或约束或unique约束约束i创建表时同时创建创建表时同时创建primary key约束或约束或unique约束约束语法构造：语法构造：CREATE TABLE table_name(column_name data_type (NULL| NOT NULL) CONSTRAINT constraint_name PRIMARY KEY | unique, .n例例1：定义数据

8、表：定义数据表student时，为学号时，为学号sno字段创建主键约束，对字段创建主键约束，对姓名姓名sname字段定义独一值约束；字段定义独一值约束；方法方法1：在列级定义主码：在列级定义主码Use stuCREATE TABLE studentSno char9 constraint sno_pk primary key,Sname char(8) constraint sname_uk unique,Ssex char(2) ，Sage smallint ，Sdept varchar(20)方法2：在表级定义主码CREATE TABLE Student (Sno CHAR(9) ， Sn

9、ame CHAR(20) ， Ssex CHAR(2) ， Sage SMALLINT， Sdept CHAR(20)， PRIMARY KEY (Sno) )实体完好性检查：插入或对主码列进展更新操作时，RDBMS按照实体完好性规那么自动进展检查。包括：1. 检查主码值能否独一，假设不独一那么回绝插入或修正2. 检查主码的各个属性能否为空，只需有一个为空就回绝插入或修正检查方法：全表扫描、索引ii经过修正表时同时创建经过修正表时同时创建primary key约束或约束或unique约束约束语法格式：语法格式：ALTER TABLE table_nameADD CONSTRAINT const

10、raint_name primary key | unique(column ,.n)例例1：对数据表：对数据表student学号学号sno字段添加主键约束，对学生姓名字段添加主键约束，对学生姓名添加独一值约束；添加独一值约束；Alter table studentAdd constraint sno_pk primary key snoAlter table studentAdd constraint sname_uk unique sname删除删除primary key约束或约束或unique约束约束语法格式：语法格式：ALTER TABLE table_nameDROP CONSTRA

11、INT constraint_name , .n例例1：删除：删除student中的主键约束和独一值约束中的主键约束和独一值约束ALTER TABLE studentDrop constraint sno_pk，sname_ukv 查看主键约束的定义文本vEXEC sp_helptext v 查看表中主键约束vEXEC sp_helpconstraint v 查看数据库中一切主键约束vSELECT name from sysobjects WHERE type=Kv 备注： K：primary key或unique约束；F：foreign keyvC：check约束；D：默许值；R：规那么；T

12、R：触发器；vU：用户表； V：视图2创建及管理外键约束创建及管理外键约束v 对于两个相关联的表主表与从表进展数据插入和删除时，经过参照完好性保证他们之间数据的一致性。v 利用Foreign Key定义从表的外码，Primary Key或者Unique定义主表中的主码或独一码不允许为空，可实现主表与从表之间的参照完好性。v 定义表间参照关系：先定义主表主码或独一码，再对从表定义外码约束。能够破坏参照完好性的情况及违约处置被参照表例如Student参照表例如SC违约处置能够破坏参照完好性 插入元组回绝能够破坏参照完好性 修正外码值回绝删除元组 能够破坏参照完好性回绝/级连删除/设置为空值修正主码

13、值 能够破坏参照完好性回绝/级连修正/设置为空值v 当主码外码发生不一致时，系统可采取以下战略：v 1. 回绝(NO ACTION)执行v 默许战略v 2. 级联(CASCADE)操作v 3. 设置为空值SET-NULLv 对于参照完好性，除了应该定义外码，还应定义外码列能否允许空值i创建表时同时定义Foreign key约束语法格式：CREATE TABLE table_name(column_name data_type FOREIGN KEYREFERNCES ref_table(ref_column) on delete CASCADE | no action -cascade表示主从

14、表删除时一致，进展级联删除-no action 表示回滚主表的删除操作,为默许设置 on update CASCADE | no action-主从表更新时一致, .n )-n表示可定义多个字段例例1：在数据库：在数据库stu中创建主表中创建主表student和和course，主键分别为，主键分别为sno和和cno，创建从表，创建从表sc，sc.sno为外码，与数据表为外码，与数据表student主键主键对应，当对主表进展更新或删除操作时，对从表采用级联操作；对应，当对主表进展更新或删除操作时，对从表采用级联操作；sco为外码，与数据表为外码，与数据表course主键对应，当对主表进展更新或主

15、键对应，当对主表进展更新或删除操作时，对从表采用删除操作时，对从表采用NO Action方式。方式。Use stuCreate table studentsno char(9) primary key，snameCreate table course cno char(4) primary key，snameCreate table scsno char(9) foreign key references student(sno) on delete cascade on update cascade，cno char(4) foreign key references course(cno)

16、 on delete no action on update no action ，Grade smallint ii经过修正表定义Foreign key约束语法格式：ALTER TABLE table_nameADD constraint constraint_nameFOREIGN KEY (column,n)REFERNCES ref_table(ref_column ,n) on delete CASCADE | no action on update CASCADE | no action例例1：假设数据库：假设数据库stu中中student和和course为主表，主键分别为为主表，

17、主键分别为sno和和cno， sc为从表，将为从表，将sc.sno定义为外码。定义为外码。Use stuAlter table scADD constraint sc_fk foreign key sno references student(sno) v iii删除表间的参照关系v删除表间的参照关系，删除从表的外码约束即可；v语法格式：vAlter table table_namevDrop constraint constraint_name ,nv iv查看外键约束vsp_helptext 、sp_helpconstraint、vselect name from sysobjects w

18、here type=F3创建及删除检查约束创建及删除检查约束v 对插入列中的值进展限制，以强迫域的完好性；v 该约束在执行INSERT语句或UPDATE时起作用；v 运用该约束可实现当用户在向表中插入或更新数据时，由SQL Server检查新行中的带有该约束的列值使其必需满足约束条件。v CHECK约束作用域v 单列：列级CHECK约束v 多列：表级CHECK约束i创建数据表时创建检查创建数据表时创建检查check约束约束语法格式：语法格式：CREATE TABLE table_name(column_name data_type (NULL|NOT NULL) CONSTRAINT chec

19、k_name CHECK (logical_expression)，n 例例1：定义：定义student表，并限定表，并限定ssex只允许取只允许取男男或或女女；CREATE TABLE student Sno char(9) Primary Key,Sname char(8),Ssex char(2) check ssex in 男男，女女 ，Sage smallint ，Sdept varchar(20)v 例例2：在定义：在定义Student中，要求男生的年龄在中，要求男生的年龄在1525岁之间，女生的岁之间，女生的年龄在年龄在1524岁之间；岁之间；vCREATE TABLE Stud

20、entv (Sno char(9) Primary Key,vSname char(8),vssex char(2),v Sage smallint,v Sdept varchar(20),vCHECK(SAGE=15 AND v ( (SSEX=男男 AND SAGE=25) OR (SSEX=女女 AND SAGE=0 and sage=100) 例例2：在表：在表student的列的列sno上添加一个上添加一个CHECK约束，要求学号必约束，要求学号必需由需由9个数字字符组成，并且不等于个数字字符组成，并且不等于0000000000.Alter table studentAdd CONS

21、TRAINT sno_ckCheck sno like 0-9 0-9 0-9 0-9 0-9 0-9 0-9 0-9 0-9 and sno000000000)iii查看约束的定义信息查看约束的定义信息查看约束的定义文本查看约束的定义文本EXEC sp_helptext 查看表中约束查看表中约束EXEC sp_helpconstraint 查看数据库中一切约束查看数据库中一切约束SELECT name from sysobjects WHERE type=C例1：查看表student上约束sno_ck的定义文本USE stuEXEC sp_helptext sno_ckGo例2：查看数据库s

22、tu中一切的check约束 USE stu SELECT name from sysobjects WHERE type=Civ删除检查check约束语法格式：ALTER TABLE table_nameDROP CONSTRAINT check_name例1：删除数据表student中sno字段的check约束ALTER TABLE studentDROP CONSTRAINT sno_ck2、默许值约束、默许值约束v 默许值约束的作用：就是当用户没有为定义为default约束的字段输入数据时, 由default 约束提供默许的数据。v 默许值对象：作为一种单独的数据库对象，它是独立于表的，

23、删除表并不能删除默许值，而是需求运用DROP DEFAULT命令才干删除。v 对于某些字段，可在程序中指定默许值，以方便用户，一个字段默许值的建立可经过如下两种方式实现：v 在定义表或修正表时，定义默许值约束；v 先定义默许值对象，然后将对象绑定到表的相应字段；1在表中定义及删除默许值约束在表中定义及删除默许值约束v默许值约束的定义v1定义表时定义字段的默许值约束v2修正表时添加一个字段的同时定义相应约束v3对表中指定的列定义默许值v默许值约束的删除i定义表时定义字段的默许值约束定义表时定义字段的默许值约束v 定义默许值约束语法格式：vCreate table table_namev( col

24、umn_name datatype not null / nullv Constraint constraint_name v DEFAULT (constraint_expression)v,n-参数n表示可定义多个数据字段v)-定义列名、数据类型、能否空值及默许值约束例1：定义数据表student中字段ssex的默许值为女；CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY， Sname CHAR(8) NOT NULL， Ssex CHAR(2) default (女) ， Sage SMALLINT， Sdept CHAR(20) )ii修正表时

25、添加一个字段的同时定义相应约束修正表时添加一个字段的同时定义相应约束v 修正默许值约束语法格式：vAlter table table_namev( ADD column column_name datatype not null | nullvConstraint constraint_name -指定约束名v DEFAULT (constraint_expression) with values -默许值约束表达式v -with values 仅用于在对表添加新字段的情况下v) v 例2：在数据表student中添加字段入学日期ruxue_date，默许值为当前日期；Alter table

26、studentAdd ruxue_date smalldatetime null Constraint date Default getdate() with valuesiii对表中指定的列定义默许值约束对表中指定的列定义默许值约束v 语法格式：vAlter table table_namev( ADD Constraint constraint_name -指定约束名v DEFAULT constraint_expression -默许值约束表达式vFor columnv)v例3：为数据表sc中字段grade添加默许值为0；Alter table scAdd Constraint grad

27、e_defaultDefault 0For gradeiv默许值约束的删除默许值约束的删除v 语法格式：vAlter table table_namevDROP Constraint constraint_name -删除指定约束名v 例1：删除表sc中字段grade的默许值；vAlter table scvDROP Constraint grade_default三、其他知识三、其他知识v规那么v触发器1、创建及管理规那么对象、创建及管理规那么对象v 规那么：对录入数据列中的数据所实施的完好性约束条件，它指定了插入到数据列中的能够值。v 规那么是SQL Server 2005数据库中独立于表

28、和视图的数据对象，与其作用的表是独立的，删除表时不会删除规那么。v 规那么就相当于数据表的数据验收员，当插入或修正的数据放入数据表时，它就检查数据能否符合要求，符合要求的放入数据表，不符合的拒之门外。这样就保证了放入数据表的数据是正确的。v 假设列同时有与之相关联的默许值和规那么，那么默许值必需在规那么定义的范围内。与规那么冲突的默许值永远不能被插入。1定义规那么对象定义规那么对象v 规那么对象的运用方法与默许值对象运用步骤类型；v 定义规那么对象v 将规那么对象绑定到列或用户自定义类型v 定义规那么对象的语法构造vCREATE RULE rule AS condition_expressio

29、nv 将规那么对象绑定到自定义类型或列语法格式：vsp_bindrule rulename = rule ,v objname = object_namev , futureonly = futureonly_flag 例例1：创建一个规那么，用于指定性别为：创建一个规那么，用于指定性别为男男或者或者女女，并绑，并绑定到数据表定到数据表student的的ssex字段。字段。 CREATE RULE ssex_ruleAS sex =男男 or sex =女女EXEC sp_bindrule ssex_rule , student.ssex例例2：创建一个规那么，用于指定学号首位以：创建一个规那

30、么，用于指定学号首位以1-9开头，其他位以开头，其他位以0-9结尾，并绑定到数据表结尾，并绑定到数据表student的的sno字段。字段。CREATE RULE sno_ruleAS num like 1-90-90-9 0-9 0-9 0-9 0-9 0-9 0-9EXEC sp_bindrule sno_rule , student.sno2删除规那么对象删除规那么对象v 删除规那么对象之前，首先应运用系统存储过程sp_unbindrule解除被绑定对象与规那么对象之间的绑定关系。v解除规那么绑定语法格式：v sp_unbindrule objname = object_namev , f

31、utureonly = futureonly_flagv删除规那么语法格式：vDROP rule rule , n例1：解除规那么对象sdept_rule与数据表student的sdept字段的绑定，然后删除规那么对象sdept_rule;Use stuIf exists (select name from sysobjects where name = sdept_rule and type=RBeginExec sp_unbindrule student.sdeptDrop rule sdept_ruleEnd v对比：v约束:定义表中有效的数据，在事务处置前执行，性能较好；v默许和规那么

32、:提供独立的可以与多个表联络的对象实现数据完好性，在事务处置前执行2、触发器、触发器v 触发器是一种特殊类型的存储过程，只需对它所维护的数据进展修正，它就会自动触发。触发器主要是经过事件进展触发而被执行的，而存储过程可以经过存储过程称号而被直接调用。v 普通情况下，对表数据的操作有插入、修正和删除，对数据的更新、插入及删除被视为数据修正事件。因此维护数据的触发器也可分为3种类型：insert,update和delete。一个触发器由三部分组成：事件。事件是指对数据库的插入、删除修正等操作。触发器在这些事件发生时将开场任务。条件。触发器将测试条件能否成立。假设条件成立，就执行相应的动作，否那么什

33、么也不作。动作。假设触发器测试满足预定的条件，那么就由DBMS执行这些动作即对数据库的操作。1定义触发器定义触发器v 语法格式：vCREATE TRIGGER ON v FOR | AFTER | INSTEAD OF vAS v - AFTER用于阐明触发器在指定操作都胜利执行后触发，是默许设置；v - INSTEAD OF指定用触发器中的操作替代触发语句的操作；v - INSERT、DELETE、UPDATE；v 例例1：对于数据库：对于数据库stu，创建一个触发器，创建一个触发器update_student，当用，当用户试图向户试图向student表中添加或修正数据时，该触发器向客户端表

34、中添加或修正数据时，该触发器向客户端显示一条显示一条“插入或更新了数据表插入或更新了数据表xs！的音讯！的音讯; vUSE stuvIF EXISTS (SELECT name FROM sysobjectsvWHERE name = update_student AND type = TR)vDROP TRIGGER update_studentvvCreate trigger update_student ON studentv FOR INSERT, UPDATEvAS PRINT 插入或更新了数据表插入或更新了数据表student！v 测试：向测试：向student表插入一条记录表插入

35、一条记录v 结果：客户端显示结果：客户端显示插入或更新了数据表插入或更新了数据表student！v 例例2 2：对于数据库：对于数据库stustu，创建一个触发器，不允许对，创建一个触发器，不允许对studentstudent删删除或修正记录时，同时客户端显示除或修正记录时，同时客户端显示不允许添加或修正记录不允许添加或修正记录。vCREATE TRIGGER student_tr1 ON studentCREATE TRIGGER student_tr1 ON studentvINSTEAD OF DELETE, UPDATEINSTEAD OF DELETE, UPDATEvAS AS vPRINT PRINT 不允许添加或修正记录不允许添加或修正