数据库 第05章 实现数据完整性课件.ppt

1、1第第5章章 实现数据完整性实现数据完整性数据完整性的类型约束禁用约束默认值和规则决定使用何种强制方法推荐操作2数据完整性的类型数据完整性的类型域完整性域完整性(列列)实体完整性实体完整性（行）（行）参照完整性参照完整性(表之间表之间)3数据完整性的类型数据完整性的类型4.1 数据完整性的类型数据完整性的类型数据完整性数据完整性指的是数据库中存储的数据的一致性和准确性数据完整性的类型：域完整性、实体完整性、引用完整性域完整性域完整性域（或列）完整性是指对列指定一组有效的值并决定是否可为空值实体完整性实体完整性实体（或表）完整性要求表中的所有行都有一个惟一的标识符，称为主键值引用完整性引用完整性

2、引用完整性确保主键（在被引用表中）和外键（在引用表中）之间的关系得到保持4第第5章章 实现数据完整性实现数据完整性数据完整性的类型约束禁用约束默认值和规则决定使用何种强制方法推荐操作5决定使用何种约束约束的类型约束约束4.2 约束约束6决定使用何种约束决定使用何种约束4.2.1 决定使用何种约束决定使用何种约束完整性类型约束类型描述域域DEFAULT如果在INSERT语句中未显式提供值，则指定为列提供的值CHECK指定列中可接受的数据值REFERENTIAL(通常使用外键)基于其他表中的列的值，指定可接受的用于更新的数据值实体实体PRIMARY KEY惟一标识每一列，确保用户没有输入重复的值。

3、同时创建一个索引以增强性能。不允许空值UNIQUE确保在非主键列中不输入重复值，并创建一个索引以增强性能。允许空值引用引用FOREIGN KEY定义一列或多列的值与同表或其他表中主键的值匹配CHECK基于同表中其他列的值，指定列中可接受的数据值7创建约束创建约束使用 CREATE TABLE 或者 ALTER TABLECREATE TABLE 是在创建表时创建约束ALTER TABLE 是在一个已有的表上创建约束可以添加约束到已有数据的表上可添加单列或多列约束若约束应用于单列，称为列级约束若约束引用了多列，称为表级约束，即使它并没有引用表中的所有列4.3.2 创建约束创建约束8使用约束的注意

4、事项使用约束的注意事项可直接在表上创建、更改和删除约束，而不必删可直接在表上创建、更改和删除约束，而不必删除并重建表除并重建表应该在应用程序和事务内建立错误检查逻辑，以应该在应用程序和事务内建立错误检查逻辑，以测试是否违反了某个约束测试是否违反了某个约束SQL Server 里的约束只是“最后防线”当给一个表添加约束的时候，当给一个表添加约束的时候，SQL Server 将检查将检查现有数据是否违反约束现有数据是否违反约束建议创建约束的时候指定名称，否则系统将为约建议创建约束的时候指定名称，否则系统将为约束自动产生一个复杂的名称束自动产生一个复杂的名称名称必须惟一，且符合 SQL Server

5、 标识符的规则查看约束的信息查看约束的信息系统存储过程：sp_helpconstraint、sp_help信息模式视图：check_constraints、referential_constraints、table_constraints系统表：syscomments、sysreferences、sysconstraints9约束的类型约束的类型DEFAULT 约束CHECK 约束PRIMARY KEY 约束UNIQUE 约束FOREIGN KEY 约束级联引用完整性4.2.2 约束的类型约束的类型10PRIMARY KEY 约束约束PRIMARY KEY 约束在表中定义了一个惟一标识每一列的

6、主键语法：语法：CONSTRAINT 约束名约束名 PRIMARY KEY CLUSTERED|NONCLUSTERED (列列,.n)4.2.2.3 PRIMARY KEY 约束约束11 PRIMARY KEY约束利用表中的一列或多列数据唯一地标识某一行数据每个表只有一个PRIMARY KEY约束PRIMARY KEY约束的值必须是唯一的不允许有空值SQL Server中最多可定义 16 列作为主键PRIMARY KEY 约束约束12n直接在列名后增加关键字直接在列名后增加关键字 PRIMARY KEY CREATE TABLE Student(sno char(5)PRIMARY KEY,

7、sname varchar(20)not null,);列级主键列级主键 创建创建sc表，其主码为（表，其主码为（sno,cno）CREATE TABLE sc (sno char(5)PRIMARY KEY，cno char(1)PRIMARY KEY，grade decimal(4,1);PRIMARY KEY 约束约束13n在在CREATE TABLE语句各列定义的最后加语句各列定义的最后加:PRIMARY KEY()CREATE TABLE sc (sno char(5)，cno char(1)，grade decimal(4,1),PRIMARY KEY(sno,cno);n CRE

8、ATE TABLE sc1 (.,constraint PK_SC PRIMARY KEY(sno,cno);表级主键表级主键约束名约束名PRIMARY KEY 约束约束14n删除表上已定义的主键删除表上已定义的主键 ALTER TABLE SC1 DROP CONSTRAINT PK_SC;n在没有定义主键的表上，加上一个主键在没有定义主键的表上，加上一个主键 ALTER TABLE SC ADD CONSTRAINT PK_SC primary key(sno,cno);删除和添加主键删除和添加主键15DEFAULT 约束约束如果一个列的值在 INSERT 语句中没有指定，DEFAULT

9、约束将自动输入一个值，可以是预先指定的常量、NULL 或者一个系统函数运行时的值语法：语法：CONSTRAINT 约束名约束名 DEFAULT 约束表达式约束表达式 创建 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(.

10、country varchar(50)not null DEFAULT China ,)l 为已经创建好的表添加default属性USE NorthwindALTER TABLE dbo.CustomersADDCONSTRAINT DF_contactname DEFAULT UNKNOWN FOR ContactName 应用 DEFAULT 约束的几种情况17DEFAULT 约束（续）约束（续）应用 DEFAULT 约束的注意事项允许使用常量、函数、系统提供的值：USER、CURRENT_USER、SESSION_USER、SYSTEM_USER 及 CURRENT_TIMESTAMP

11、例如：例如：DEFAULT USER DEFAULE (getdate()有可能会和 CHECK 约束冲突常量值外面可以加或者不加括号，字符或者日期常量必须加上单常量值外面可以加或者不加括号，字符或者日期常量必须加上单引号或双引号引号或双引号4.2.2.1 DEFAULT 约束约束忽略，不显式地插入值插入 NULL 插入 DEFAULT无默认值有默认值无默认值有默认值无默认值有默认值NULLNULL默认值默认值NULLNULLNULL默认值默认值NOT NULL错误错误默认值默认值错误错误错误错误错误错误默认值默认值18CHECK 约束约束限制输入到指定列的值只能为某些特定值语法：语法：CON

12、STRAINT 约束名约束名 CHECK(逻辑表达式逻辑表达式)两种强制域完整性的方法：CHECK 约束和规则CHECK 约束定义了一个表达式，若数据修改语句使得表达式值为 FALSE 的话，将拒绝语句执行规则的功能和 CHECK 约束基本相同，除了语法不同，能力稍弱。规则可定义一次，然后对多个列分别绑定；而 CHECK 约束则需要对每个列定义。但 CHECK 约束的功能略强一些（例如引用同行中其他列的值和调用系统函数等）4.2.2.2 CHECK 约束约束19CHECK 约束（续）约束（续）应用CHECK约束的几种情况4.2.2.2 CHECK 约束约束USE NorthwindALTER

13、TABLE dbo.EmployeesADD CONSTRAINT CK_birthdateCHECK(BirthDate 01-01-1900 AND BirthDate 01-01-1900 AND BirthDate=0 and grade=0 and grade=0 and grade=100);创建创建CHECK 约束约束表级表级CHECK约束约束22lCreate Table Student (sno char(5)not null,birthdate datetime not null schooldate datetime not null,primary key(sno),)

14、;创建创建CHECK 约束约束表级表级CHECK约束约束check(birthdate getdate(),check(birthdate=0 and gmark=100)添加和删除添加和删除CHECK 约束约束24CHECK 约束（续）约束（续）应用CHECK 约束的注意事项一个表可以定义多个CHECK约束，一个列上只允许创建一个列级CHECK约束。列级 CHECK 约束只能引用被约束的列，表级 CHECK 约束只能引用同一表中的列。当列上存在规则和一个或多个 CHECK 约束时，将验证所有限制。4.2.2.2 CHECK 约束约束25UNIQUE 约束约束UNIQUE 约束指明列中的任意两

15、行不能有相同的值语法：语法：CONSTRAINT CONSTRAINT 约束名约束名 UNIQUE UNIQUE CLUSTERED|NONCLUSTERED (CLUSTERED|NONCLUSTERED (列列,.,.n n)应用 UNIQUE 约束的注意事项允许空值的列上定义 UNIQUE 约束 在一个表上允许多个 UNIQUE 约束可在一个或者多个列上定义是通过一个惟一索引强制约束的4.2.2.4 UNIQUE 约束约束26UNIQUE 约束（续）约束（续）PRIMARY KEY 和和 UNIQUE 约束约束声明 PRIMARY KEY 或 UNIQUE 约束的结果只是自动创建了一个指

16、定列上的惟一索引，通过惟一索引来确保值的惟一性可空性可空性PRIMARY KEY 的各个列必须声明为 NOT NULL，而 UNIQUE 的各个列可以声明为允许 NULL 值在惟一索引中，认为所有的 NULL 值是相等的索引的属性索引的属性PRIMARY KEY 约束所创建的惟一索引默认为 CLUSTERED，除非表中另外一列已经声明为 CLUSTEREDUNIQUE 约束所创建的惟一索引默认为 NONCLUSTERED选择键选择键保持键的长度尽可能短，必要时可另外创建一个代替键不要使用 float 或 real 数据类型的列作为主键27n在创建表时定义在创建表时定义UNIQUE约束约束lCR

17、EATE TABLE test2 (even_id int primary key,even_name char(20),even_type char(20),even_time datetime,)CONSTRAINT UNIQ_EVEN UNIQUE(even_type,even_time)CONSTRAINT UNIQ_EVEN UNIQUE(even_type,even_time)要求：要求：在在even_type和和even_time上共同上共同建立惟一约束建立惟一约束表级表级UNIQUEUNIQUE约束约束创建创建UNIQUE 约束约束28n向向 test1表中的表中的 tname

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

19、GN KEY(列列,n n)REFERENCES REFERENCES 引用表引用表(引用列引用列,n n)具有 FOREIGN KEY 约束的列的取值范围只能是被引用的列的列值4.2.2.5 FOREIGN KEY 约束约束30FOREIGN KEY 约束（续）约束（续）应用 FOREIGN KEY 约束的注意事项FOREIGN KEY 子句中指定的列的个数和数据类型必须和 REFERENCES 子句中指定的列的个数和数据类型匹配并不自动创建索引修改数据的时候，用户必须在被 FOREIGN KEY 约束引用的表上具有 SELECT 或 REFERENCES 权限若引用的是同表中的列，那么可只

20、用 REFERENCES 子句而省略 FOREIGN KEY 子句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 约束（续

21、）约束（续）创建完表后通过修改表增加创建完表后通过修改表增加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 constraint f

22、k_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：任何企图删除

23、或者更新被其他表的外键所引用的键都将引发一个错误，对数据的改变会被回滚。NO ACTION 是默认值CASCADE：若父表中的行变化了，则引用表中相应的行也自动变化35级联引用完整性（续）级联引用完整性（续）4.2.2.6 级联引用完整性级联引用完整性应用 CASCADE 选项的注意事项可在多个具有引用关系的表之间组合 CASCADE 和 NO ACTION 选项。若 SQL Server 遇到 NO ACTION，则中断并回滚所有相关的 CASCADE 动作CASCADE 选项不能对定义为 rowversion 数据类型的外键或主键列指定36第第5章章 实现数据完整性实现数据完整性数据完整性

24、的类型约束禁用约束默认值和规则决定使用何种强制方法推荐操作37禁用约束禁用约束禁用现有数据上的约束检查在加载新数据时禁用约束检查4.3 禁用约束禁用约束38禁用现有数据上的约束检查禁用现有数据上的约束检查当在表上添加约束的时候，我们可以禁用对已有数据的约束检查语法：语法：ALTER TABLE 表名 WITH CHECK WITH NOCHECK ADD CONSTRAINT 约束名 FOREIGN KEY(column,n)REFERENCES 引用表(引用列,n)CHECK(搜索条件)4.3.1 禁用现有数据上的约束检查禁用现有数据上的约束检查39禁用现有数据上的约束检查（续）禁用现有数据

25、上的约束检查（续）对已有数据禁用约束检查的注意事项只能禁用 CHECK 和 FOREIGN KEY 约束当为一个已有数据的表添加 CHECK 或 FOREIGN KEY 约束的时候，使用 WITH NOCHECK 选项来禁用对已有数据的约束检查4.3.1 禁用现有数据上的约束检查禁用现有数据上的约束检查40在加载新数据时禁用约束检查在加载新数据时禁用约束检查为了避免约束检查的开销，有时候可能希望禁用约束已经确保数据符合约束数据并不符合约束，但稍后可以更改其值并重用约束语法：ALTER TABLE 表名 CHECK|NOCHECK CONSTRAINT ALL|约束名,.n查看约束是启用还是禁用

26、的状态系统存储过程 sp_help系统函数 OBJECTPROPERTY 的 CnstIsDisabled 属性4.3.2 在加载新数据时禁用约束检查在加载新数据时禁用约束检查41第第5章章 实现数据完整性实现数据完整性数据完整性的类型约束禁用约束默认值和规则决定使用何种强制方法推荐操作42使用默认值和规则使用默认值和规则语法：创建：CREATE DEFAULT 默认值 AS 常量表达式 删除：DROP DEFAULT 默认值,.n 绑定一个默认：sp_bindefault 解除默认值绑定：sp_unbindefault创建默认值的注意事项列的默认值必须符合此列上的任何规则或CHECK约束4.

27、4 使用默认值和规则使用默认值和规则43使用默认和规则（续）使用默认和规则（续）规则：规则指定了能插入列的可接受的值。它确保数据在指定值域内，匹配某个模式，或者匹配指定列表中的项。语法：语法：创建：CREATE RULECREATE RULE 规则规则 ASAS 条件表达式条件表达式 删除：DROP RULEDROP RULE 规则规则,.,.n n 绑定规则：sp_bindrule 分离已绑定的规则：sp_unbindrule关于规则的注意事项规则定义可以包含任何在 WHERE 子句中有效的表达式一个列或者用户定义数据类型只能被一个规则绑定4.4 使用默认和规则使用默认和规则44第第5章章

28、实现数据完整性实现数据完整性数据完整性的类型约束禁用约束默认值和规则决定使用何种强制方法推荐操作45决定使用何种强制方法决定使用何种强制方法4.1 决定使用何种强制方法决定使用何种强制方法应综合考虑功能性和性能开销对于基本的完整性逻辑，例如有效值和维护表间的关系，最好使用声明式完整性约束完整性约束如果要维护复杂的、大量的、非主键或外键关系一部分的数据，必须使用触发器触发器或存储过程存储过程数据完整性类型效果功能性性能开销在数据修改之前或之后约束约束和表一起定义，数据在事务开始和表一起定义，数据在事务开始前验证，使得性能更好前验证，使得性能更好中中低低之前之前默认和规则默认和规则作为独立的对象来

29、实现数据完整作为独立的对象来实现数据完整性，可以与一个或多个表关联性，可以与一个或多个表关联低低低低之前之前触发器触发器提供了额外的功能性，例如层叠提供了额外的功能性，例如层叠和复杂的应用逻辑。任何修改必和复杂的应用逻辑。任何修改必须被回滚须被回滚高高中高中高之后（除了之后（除了INSTEAD OF 触发器）触发器）数据类型、数据类型、NULL/NOT NULL提供最底层的数据完整性。当表提供最底层的数据完整性。当表创建时为每个列实现，数据在事创建时为每个列实现，数据在事务开始前验证务开始前验证低低低低之前之前46第第5章章 实现数据完整性实现数据完整性数据完整性的类型约束禁用约束默认值和规则

30、决定使用何种强制方法推荐操作47推荐操作推荐操作使用级联引用完整性代替触发器使用级联引用完整性代替触发器使用约束，因为它们是使用约束，因为它们是 ANSI 兼容的兼容的4.6 推荐操作推荐操作48n目标目标n定义和使用 DEFAULT 和 CHECK 约束n定义和使用 PRIMARY KEY 和 FOREIGN KEY 约束n创建和使用 SQL Server 2000 规则和默认值n练习练习 1 定义定义 DEFAULT 约束约束n练习练习 2 定义定义 CHECK 约束约束n练习练习 3 定义定义 PRIMARY KEY 约束约束n练习练习 4 定义定义 FOREIGN KEY 约束约束n可选可选 创建创建默认值默认值和规则和规则实验实验 实现数据完整性实现数据完整性49回顾回顾学习完本章后，将能够：描述数据完整性的类型描述强制数据完整性的方法决定使用何种约束和创建约束定义和使用 DEFAULT、CHECK、PRIMARY KEY、UNIQUE 和 FOREIGN KEY 约束禁用约束检查描述和使用默认值和规则决定使用何种数据完整性强制方法