数据库实现数据完整性ppt课件

1、1第第5章章 实现数据完好性实现数据完好性数据完好性的类型约束禁用约束默许值和规那么决议运用何种强迫方法引荐操作2数据完好性的类型数据完好性的类型域完好性域完好性(列列)实体完好性实体完好性 行行参照完好性参照完好性(表之间表之间)3数据完好性的类型数据完好性的类型4.1 数据完好性的类型数据完好性的类型数据完好性数据完好性指的是数据库中存储的数据的一致性和准确性指的是数据库中存储的数据的一致性和准确性数据完好性的类型：域完好性、实体完好性、数据完好性的类型：域完好性、实体完好性、援用完好性援用完好性域完好性域完好性域或列完好性是指对列指定一组有效的值域或列完好性是指对列指定一组有效的值并决议

2、能否可为空值并决议能否可为空值实体完好性实体完好性实体或表完好性要求表中的一切行都有一实体或表完好性要求表中的一切行都有一个独一的标识符，称为主键值个独一的标识符，称为主键值援用完好性援用完好性援用完好性确保主键在被援用表中和外键援用完好性确保主键在被援用表中和外键在援用表中之间的关系得到坚持在援用表中之间的关系得到坚持4第第5章章 实现数据完好性实现数据完好性数据完好性的类型约束禁用约束默许值和规那么决议运用何种强迫方法引荐操作5决议运用何种约束约束的类型约束约束4.2 约束约束6决议运用何种约束决议运用何种约束4.2.1 决议运用何种约束决议运用何种约束完整性类型约束类型描述域域DEFAU

3、LT如果在INSERT语句中未显式提供值，则指定为列提供的值CHECK指定列中可接受的数据值REFERENTIAL(通常使用外键)基于其他表中的列的值，指定可接受的用于更新的数据值实体实体PRIMARY KEY惟一标识每一列，确保用户没有输入重复的值。同时创建一个索引以增强性能。不允许空值UNIQUE确保在非主键列中不输入重复值，并创建一个索引以增强性能。允许空值引用引用FOREIGN KEY定义一列或多列的值与同表或其他表中主键的值匹配CHECK基于同表中其他列的值，指定列中可接受的数据值7创建约束创建约束运用 CREATE TABLE 或者 ALTER TABLECREATE TABLE

4、是在创建表时创建约束ALTER TABLE 是在一个已有的表上创建约束可以添加约束到已有数据的表上可添加单列或多列约束假设约束运用于单列，称为列级约束假设约束援用了多列，称为表级约束，即使它并没有援用表中的一切列4.3.2 创建约束创建约束8运用约束的本卷须知运用约束的本卷须知 可直接在表上创建、更改和删除约束，而不用删可直接在表上创建、更改和删除约束，而不用删除并重建表除并重建表 应该在运用程序和事务内建立错误检查逻辑，以应该在运用程序和事务内建立错误检查逻辑，以测试能否违反了某个约束测试能否违反了某个约束 SQL Server 里的约束只是里的约束只是“最后防线最后防线 当给一个表添加约束

5、的时候，当给一个表添加约束的时候，SQL Server 将检查将检查现有数据能否违反约束现有数据能否违反约束 建议创建约束的时候指定称号，否那么系统将为建议创建约束的时候指定称号，否那么系统将为约束自动产生一个复杂的称号约束自动产生一个复杂的称号 称号必需独一，且符合称号必需独一，且符合 SQL Server 标识符的规那标识符的规那么么 查看约束的信息查看约束的信息 系统存储过程：系统存储过程：sp_helpconstraint、sp_help 信息方式视图：信息方式视图：check_constraints、referential_constraints、table_constraints

6、系统表：系统表：syscomments、sysreferences、sysconstraints9约束的类型约束的类型DEFAULT 约束CHECK 约束PRIMARY KEY 约束UNIQUE 约束FOREIGN KEY 约束级联援用完好性4.2.2 约束的类型约束的类型10PRIMARY KEY 约束约束PRIMARY KEY 约束在表中定义了一个独一标识每一列的主键语法：CONSTRAINT 约束名 PRIMARY KEY CLUSTERED | NONCLUSTERED (列,.n)4.2.2.3 PRIMARY KEY 约束约束11 PRIMARY KEY约束利用表中的一列或多列数据

7、独一地标识某一行数据每个表只需一个PRIMARY KEY约束PRIMARY KEY约束的值必需是独一的不允许有空值SQL Server中最多可定义 16 列作为主键PRIMARY KEY 约束约束12n直接在列名后添加关键字直接在列名后添加关键字 PRIMARY KEYn CREATE TABLE Studentn( sno char(5) PRIMARY KEY,n sname varchar(20) not null, n );n列级主键列级主键 创建创建sc表，其主码为表，其主码为sno,cnoPRIMARY KEY 约束约束13n在在CREATE TABLECREATE TABLE语句

8、各列定义的最后加语句各列定义的最后加: :nPRIMARY KEY(PRIMARY KEY()n CREATE TABLE sc CREATE TABLE scn (sno char(5) (sno char(5) ，n cno char(1) cno char(1) ， n grade decimal(4,1), grade decimal(4,1),n PRIMARY KEY(sno,cno); PRIMARY KEY(sno,cno);n CREATE TABLE sc1n ( .,n constraint PK_SC PRIMARY KEY(sno,cno);表级主键表级主键约束名约束

9、名PRIMARY KEY 约束约束14n删除表上已定义的主键删除表上已定义的主键n ALTER TABLE SC1n DROP CONSTRAINT PK_SC;n在没有定义主键的表上，加上一个主键在没有定义主键的表上，加上一个主键n ALTER TABLE SCn ADD CONSTRAINT PK_SC primary key (sno,cno);删除和添加主键删除和添加主键15DEFAULT 约束约束假设一个列的值在 INSERT 语句中没有指定，DEFAULT 约束将自动输入一个值，可以是预先指定的常量、NULL 或者一个系统函数运转时的值语法： CONSTRAINT 约束名 DEFA

10、ULT 约束表达式 创建 DEFAULT 约束的两种方法创建一个默许对象CREATE DEFAULT，然后运用存储过程 sp_bindefault 将默许绑定到一个列CREATE TABLE 或 ALTER TABLE 时运用 DEFAUTL 约束4.2.2.1 DEFAULT 约束约束16DEFAULT 约束续约束续l 在创建表时运用default属性4.2.2.1 DEFAULT 约束约束CREATE TABLE userInfo( . country varchar(50) not null DEFAULT China , )l 为曾经创建好的表添加default属性USE Northw

11、indALTER TABLE dbo.CustomersADDCONSTRAINT DF_contactname DEFAULT UNKNOWN FOR ContactName 运用 DEFAULT 约束的几种情况17DEFAULT 约束续约束续 运用 DEFAULT 约束的本卷须知 允许运用常量、函数、系统提供的值：USER、CURRENT_USER、SESSION_USER、SYSTEM_USER 及 CURRENT_TIMESTAMP 例如： DEFAULT USER DEFAULE (getdate() 有能够会和 CHECK 约束冲突 常量值外面可以加或者不加括号，字符或者日期常量必

12、需加上单引号或双引号4.2.2.1 DEFAULT 约束约束忽略，不显式地插入值插入 NULL 插入 DEFAULT无默认值有默认值无默认值有默认值无默认值有默认值NULLNULL默认值默认值NULLNULLNULL默认值默认值NOT NULL错误错误默认值默认值错误错误错误错误错误错误默认值默认值18CHECK 约束约束限制输入到指定列的值只能为某些特定值语法： CONSTRAINT 约束名 CHECK (逻辑表达式) 两种强迫域完好性的方法：CHECK 约束和规那么CHECK 约束定义了一个表达式，假设数据修正语句使得表达式值为 FALSE 的话，将回绝语句执行规那么的功能和 CHECK

13、约束根本一样，除了语法不同，才干稍弱。规那么可定义一次，然后对多个列分别绑定；而 CHECK 约束那么需求对每个列定义。但 CHECK 约束的功能略强一些例如援用同行中其他列的值和调用系统函数等4.2.2.2 CHECK 约束约束19CHECK 约束续约束续运用CHECK约束的几种情况4.2.2.2 CHECK 约束约束USE NorthwindALTER TABLE dbo.EmployeesADD CONSTRAINT CK_birthdateCHECK (BirthDate 01-01-1900 AND BirthDate 01-01-1900 AND BirthDate =0 and

14、grade=0 and grade=0 and grade=100); 创建创建CHECK 约束约束表级表级CHECK约束约束22lCreate Table Studentl ( sno char(5) not null,l birthdate datetime not nulll schooldate datetime not null , l primary key (sno),l l ); 创建创建CHECK 约束约束表级表级CHECK约束约束check(birthdate getdate(),check(birthdate =0 and gmark=100) 添加和删除添加和删除CHE

15、CK 约束约束24CHECK 约束续约束续运用CHECK 约束的本卷须知一个表可以定义多个CHECK约束，一个列上只允许创建一个列级CHECK约束。列级 CHECK 约束只能援用被约束的列，表级 CHECK 约束只能援用同一表中的列。当列上存在规那么和一个或多个 CHECK 约束时，将验证一切限制。 4.2.2.2 CHECK 约束约束25UNIQUE 约束约束UNIQUE 约束指明列中的恣意两行不能有一样的值语法：CONSTRAINT 约束名 UNIQUE CLUSTERED | NONCLUSTERED (列,.n ) 运用 UNIQUE 约束的本卷须知允许空值的列上定义 UNIQUE 约

16、束 在一个表上允许多个 UNIQUE 约束可在一个或者多个列上定义是经过一个独一索引强迫约束的4.2.2.4 UNIQUE 约束约束26UNIQUE 约束续约束续PRIMARY KEY 和和 UNIQUE 约束约束声明声明 PRIMARY KEY 或或 UNIQUE 约束的结果只是约束的结果只是自动创建了一个指定列上的独一索引，经过独一索自动创建了一个指定列上的独一索引，经过独一索引来确保值的独一性引来确保值的独一性可空性可空性PRIMARY KEY 的各个列必需声明为的各个列必需声明为 NOT NULL，而而 UNIQUE 的各个列可以声明为允许的各个列可以声明为允许 NULL 值值在独一索

17、引中，以为一切的在独一索引中，以为一切的 NULL 值是相等的值是相等的索引的属性索引的属性PRIMARY KEY 约束所创建的独一索引默以为约束所创建的独一索引默以为 CLUSTERED，除非表中另外一列曾经声明为，除非表中另外一列曾经声明为 CLUSTEREDUNIQUE 约束所创建的独一索引默以为约束所创建的独一索引默以为 NONCLUSTERED选择键选择键坚持键的长度尽能够短，必要时可另外创建一个替坚持键的长度尽能够短，必要时可另外创建一个替代键代键不要运用不要运用 float 或或 real 数据类型的列作为主键数据类型的列作为主键27n在创建表时定义在创建表时定义UNIQUE约束

18、约束nCREATE TABLE test2n ( even_id int primary key,n even_name char(20),n even_type char(20),n even_time datetime,n n ) CONSTRAINT UNIQ_EVEN UNIQUE(even_type,even_time)CONSTRAINT UNIQ_EVEN UNIQUE(even_type,even_time)要求：要求：在在even_type和和even_time上共同上共同建立独一约束建立独一约束表级表级UNIQUEUNIQUE约束约束创建创建UNIQUE 约束约束28n向向

19、 test1表中的表中的 tname 列添加列添加 UNIQUE 约束约束n ALTER TABLE test1n ADD CONSTRAINT UNIQ_ TNAME UNIQUE (tname) n删除表上已定义的删除表上已定义的UNIQUE约束约束nALTER TABLE test1n DROP CONSTRAINT UNIQ_ TNAME 添加和删除添加和删除UNIQUE 约束约束29FOREIGN KEY 约束约束 FOREIGN KEY 约束：定义到同表或其他表中具有 PRIMARY KEY 或者 UNIQUE 约束的列的援用语法：CONSTRAINT 约束名FOREIGN KEY

20、(列,n)REFERENCES 援用表 (援用列 ,n) 具有 FOREIGN KEY 约束的列的取值范围只能是被援用的列的列值4.2.2.5 FOREIGN KEY 约束约束30FOREIGN KEY 约束续约束续运用 FOREIGN KEY 约束的本卷须知FOREIGN KEY 子句中指定的列的个数和数据类型必需和 REFERENCES 子句中指定的列的个数和数据类型匹配并不自动创建索引修正数据的时候，用户必需在被 FOREIGN KEY 约束援用的表上具有 SELECT 或 REFERENCES 权限假设援用的是同表中的列，那么可只用 REFERENCES 子句而省略 FOREIGN K

21、EY 子句4.2.2.5 FOREIGN KEY 约束约束31FOREIGN KEY 约束续约束续运用运用FOREIGN KEY约束的几种情况：约束的几种情况：创建表的同时创建创建表的同时创建FOREIGN KEY 约束约束 create table stuscore( id int not null, courseID int not null, score int not null, constraint fk_student_stuscore foreign key (id) references student(id)32FOREIGN KEY 约束续约束续创建完表后经过修正表添加创建

22、完表后经过修正表添加FOREIGN KEY 约束约束运用运用 FOREIGN KEY 约束，确保约束，确保 Orders 表中的表中的客户标识与客户标识与 Customers 表中的有效的客户标识相表中的有效的客户标识相关联关联4.2.2.5 FOREIGN KEY 约束约束alter table stuscoreadd constraint fk_student_stuscoreforeign key (id) references student(id)33FOREIGN KEY 约束续约束续FOREIGN KEY 约束的删除alter table stuscore drop constr

23、aint fk_student_stuscore34级联援用完好性级联援用完好性4.2.2.6 级联援用完好性级联援用完好性 FOREIGN KEY 约束包含一个 CASCADE 选项，允许对一个定义了 UNIQUE 或者 PRIMARY KEY 约束的列的值的修正自动传播到援用它的外键上，这个动作称为级联援用完好性语法：CONSTRAINT 约束名 FOREIGN KEY (列,n)REFERENCES 援用表 (援用列 ,n) ON DELETE CASCADE | NO ACTION ON UPDATE CASCADE | NO ACTION NO ACTION：任何企图删除或者更新被其

24、他表的外键所援用的键都将引发一个错误，对数据的改动会被回滚。NO ACTION 是默许值 CASCADE：假设父表中的行变化了，那么援用表中相应的行也自动变化35级联援用完好性续级联援用完好性续4.2.2.6 级联援用完好性级联援用完好性运用 CASCADE 选项的本卷须知可在多个具有援用关系的表之间组合 CASCADE 和 NO ACTION 选项。假设 SQL Server 遇到 NO ACTION，那么中断并回滚一切相关的 CASCADE 动作CASCADE 选项不能对定义为 rowversion 数据类型的外键或主键列指定36第第5章章 实现数据完好性实现数据完好性数据完好性的类型约束

25、禁用约束默许值和规那么决议运用何种强迫方法引荐操作37禁用约束禁用约束禁用现有数据上的约束检查在加载新数据时禁用约束检查4.3 禁用约束禁用约束38禁用现有数据上的约束检查禁用现有数据上的约束检查 当在表上添加约束的时候，我们可以禁用对已有数据的约束检查语法：ALTER TABLE 表名 WITH CHECKWITH NOCHECK ADD CONSTRAINT 约束名 FOREIGN KEY (column,n) REFERENCES 援用表 (援用列 ,n) CHECK (搜索条件) 4.3.1 禁用现有数据上的约束检查禁用现有数据上的约束检查39禁用现有数据上的约束检查续禁用现有数据上的

26、约束检查续对已有数据禁用约束检查的本卷须知只能禁用 CHECK 和 FOREIGN KEY 约束当为一个已有数据的表添加 CHECK 或 FOREIGN KEY 约束的时候，运用 WITH NOCHECK 选项来禁用对已有数据的约束检查4.3.1 禁用现有数据上的约束检查禁用现有数据上的约束检查40在加载新数据时禁用约束检查在加载新数据时禁用约束检查为了防止约束检查的开销，有时候能够希望禁用约束曾经确保数据符合约束数据并不符合约束，但稍后可以更改其值并重用约束语法：ALTER TABLE 表名 CHECK|NOCHECK CONSTRAINT ALL|约束名,.n查看约束是启用还是禁用的形状系

27、统存储过程 sp_help系统函数 OBJECTPROPERTY 的 CnstIsDisabled 属性4.3.2 在加载新数据时禁用约束检查在加载新数据时禁用约束检查41第第5章章 实现数据完好性实现数据完好性数据完好性的类型约束禁用约束默许值和规那么决议运用何种强迫方法引荐操作42运用默许值和规那么运用默许值和规那么语法：创建：CREATE DEFAULT 默许值 AS 常量表达式 删除：DROP DEFAULT 默许值 ,.n 绑定一个默许： sp_bindefault 解除默许值绑定： sp_unbindefault创建默许值的本卷须知列的默许值必需符合此列上的任何规那么或CHECK约

28、束4.4 运用默许值和规那么运用默许值和规那么43运用默许和规那么续运用默许和规那么续规那么：规那么指定了能插入列的可接受的值。它确保数据在指定值域内，匹配某个方式，或者匹配指定列表中的项。语法：创建：CREATE RULE 规那么 AS 条件表达式 删除：DROP RULE 规那么 ,.n 绑定规那么： sp_bindrule 分别已绑定的规那么 ：sp_unbindrule关于规那么的本卷须知规那么定义可以包含任何在 WHERE 子句中有效的表达式一个列或者用户定义数据类型只能被一个规那么绑定4.4 运用默许和规那么运用默许和规那么44第第5章章 实现数据完好性实现数据完好性数据完好性的类型约束禁用约束默许值和规那么决议运用何种强迫方法引荐操作45决议运用何种强迫方法决议运用何种强迫方法4.1 决议运用何种强迫方法决议运用何种强迫方法应综合思索功能性和性能开销对于根本的完好性逻辑，例如有效值和维护表间的关系，最好运用声明式完好性约束假设要维护复杂的