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

第5章 实现数据完整性,数据完整性的类型约束禁用约束默认值和规则决定使用何种强制方法推荐操作,数据完整性的类型,数据完整性的类型,4.1 数据完整性的类型,数据完整性指的是数据库中存储的数据的一致性和准确性数据完整性的类型：域完整性、实体完整性、引用完整性域完整性域（或列）完整性是指对列指定一组有效的值并决定是否可为空值实体完整性实体（或表）完整性要求表中的所有行都有一个惟一的标识符，称为主键值引用完整性引用完整性确保主键（在被引用表中）和外键（在引用表中）之间的关系得到保持,第5章 实现数据完整性,数据完整性的类型约束禁用约束默认值和规则决定使用何种强制方法推荐操作,决定使用何种约束约束的类型,约束,4.2 约束,决定使用何种约束,4.2.1 决定使用何种约束,创建约束,使用 CREATE TABLE 或者 ALTER TABLECREATE TABLE 是在创建表时创建约束ALTER TABLE 是在一个已有的表上创建约束可以添加约束到已有数据的表上可添加单列或多列约束若约束应用于单列，称为列级约束若约束引用了多列，称为表级约束，即使它并没有引用表中的所有列,4.3.2 创建约束,使用约束的注意事项,可直接在表上创建、更改和删除约束，而不必删除并重建表应该在应用程序和事务内建立错误检查逻辑，以测试是否违反了某个约束SQL Server 里的约束只是“最后防线”当给一个表添加约束的时候，SQL Server 将检查现有数据是否违反约束建议创建约束的时候指定名称，否则系统将为约束自动产生一个复杂的名称名称必须惟一，且符合 SQL Server 标识符的规则查看约束的信息系统存储过程：sp_helpconstraint、sp_help信息模式视图：check_constraints、referential_constraints、table_constraints系统表：syscomments、sysreferences、sysconstraints,约束的类型,DEFAULT 约束CHECK 约束PRIMARY KEY 约束UNIQUE 约束FOREIGN KEY 约束级联引用完整性,4.2.2 约束的类型,PRIMARY KEY 约束,PRIMARY KEY 约束在表中定义了一个惟一标识每一列的主键语法：CONSTRAINT 约束名 PRIMARY KEY CLUSTERED|NONCLUSTERED(列,.n),4.2.2.3 PRIMARY KEY 约束,PRIMARY KEY约束利用表中的一列或多列数据唯一地标识某一行数据每个表只有一个PRIMARY KEY约束PRIMARY KEY约束的值必须是唯一的不允许有空值SQL Server中最多可定义 16 列作为主键,PRIMARY KEY 约束,直接在列名后增加关键字 PRIMARY KEYCREATE TABLE Student(sno char(5)PRIMARY KEY,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 约束,在CREATE TABLE语句各列定义的最后加:PRIMARY KEY()CREATE TABLE sc(sno char(5)，cno char(1)，grade decimal(4,1),PRIMARY KEY(sno,cno);,CREATE TABLE sc1(.,constraint PK_SC PRIMARY KEY(sno,cno);,表级主键,约束名,PRIMARY KEY 约束,删除表上已定义的主键 ALTER TABLE SC1 DROP CONSTRAINT PK_SC;在没有定义主键的表上，加上一个主键 ALTER TABLE SC ADD CONSTRAINT PK_SC primary key(sno,cno);,删除和添加主键,DEFAULT 约束,如果一个列的值在 INSERT 语句中没有指定，DEFAULT 约束将自动输入一个值，可以是预先指定的常量、NULL 或者一个系统函数运行时的值语法：CONSTRAINT 约束名 DEFAULT 约束表达式 创建 DEFAULT 约束的两种方法创建一个默认对象（CREATE DEFAULT），然后使用存储过程 sp_bindefault 将默认绑定到一个列CREATE TABLE 或 ALTER TABLE 时使用 DEFAUTL 约束,4.2.2.1 DEFAULT 约束,DEFAULT 约束（续）,在创建表时使用default属性,4.2.2.1 DEFAULT 约束,CREATE TABLE userInfo(.country varchar(50)not null DEFAULT China,),为已经创建好的表添加default属性,USE NorthwindALTER TABLE dbo.CustomersADDCONSTRAINT DF_contactname DEFAULT UNKNOWN FOR ContactName,应用 DEFAULT 约束的几种情况,DEFAULT 约束（续）,应用 DEFAULT 约束的注意事项允许使用常量、函数、系统提供的值：USER、CURRENT_USER、SESSION_USER、SYSTEM_USER 及 CURRENT_TIMESTAMP 例如：DEFAULT USER DEFAULE(getdate()有可能会和 CHECK 约束冲突常量值外面可以加或者不加括号，字符或者日期常量必须加上单引号或双引号,4.2.2.1 DEFAULT 约束,CHECK 约束,限制输入到指定列的值只能为某些特定值语法：CONSTRAINT 约束名 CHECK(逻辑表达式)两种强制域完整性的方法：CHECK 约束和规则CHECK 约束定义了一个表达式，若数据修改语句使得表达式值为 FALSE 的话，将拒绝语句执行规则的功能和 CHECK 约束基本相同，除了语法不同，能力稍弱。规则可定义一次，然后对多个列分别绑定；而 CHECK 约束则需要对每个列定义。但 CHECK 约束的功能略强一些（例如引用同行中其他列的值和调用系统函数等）,4.2.2.2 CHECK 约束,CHECK 约束（续）,应用CHECK约束的几种情况,4.2.2.2 CHECK 约束,在现有表中创建CHECK约束,在创建表时使用CHECK约束,在创建SC表时定义CHECK约束 Create Table SC(sno char(5)not null,cno char(1)not null,grade decimal(4,1)primary key(sno,cno);,创建CHECK 约束,列级CHECK约束,check(grade=0 and grade=100),Create Table SC(sno char(5)not null,cno char(1)not null,grade decimal(4,1),primary key(sno,cno),check(grade=0 and grade=0 and grade=100);,创建CHECK 约束,表级CHECK约束,Create Table Student(sno char(5)not null,birthdate datetime not null schooldate datetime not null,primary key(sno),);,创建CHECK 约束,表级CHECK约束,check(birthdate getdate(),check(birthdate getdate(),列级CHECK约束,删除SC表中的 CHECK 约束ALTER TABLE SC DROP CONSTRAINT chk_gmk 向SC表中添加CHECK 约束ALTER TABLE SC ADD CONSTRAINT chk_gmk check(gmark=0 and gmark=100),添加和删除CHECK 约束,CHECK 约束（续）,应用CHECK 约束的注意事项一个表可以定义多个CHECK约束，一个列上只允许创建一个列级CHECK约束。列级 CHECK 约束只能引用被约束的列，表级 CHECK 约束只能引用同一表中的列。当列上存在规则和一个或多个 CHECK 约束时，将验证所有限制。,4.2.2.2 CHECK 约束,UNIQUE 约束,UNIQUE 约束指明列中的任意两行不能有相同的值语法：CONSTRAINT 约束名 UNIQUE CLUSTERED|NONCLUSTERED(列,.n)应用 UNIQUE 约束的注意事项允许空值的列上定义 UNIQUE 约束 在一个表上允许多个 UNIQUE 约束可在一个或者多个列上定义是通过一个惟一索引强制约束的,4.2.2.4 UNIQUE 约束,UNIQUE 约束（续）,PRIMARY KEY 和 UNIQUE 约束声明 PRIMARY KEY 或 UNIQUE 约束的结果只是自动创建了一个指定列上的惟一索引，通过惟一索引来确保值的惟一性可空性PRIMARY KEY 的各个列必须声明为 NOT NULL，而 UNIQUE 的各个列可以声明为允许 NULL 值在惟一索引中，认为所有的 NULL 值是相等的索引的属性PRIMARY KEY 约束所创建的惟一索引默认为 CLUSTERED，除非表中另外一列已经声明为 CLUSTEREDUNIQUE 约束所创建的惟一索引默认为 NONCLUSTERED选择键保持键的长度尽可能短，必要时可另外创建一个代替键不要使用 float 或 real 数据类型的列作为主键,在创建表时定义UNIQUE约束CREATE 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),要求：在even_type和even_time上共同建立惟一约束,表级UNIQUE约束,创建UNIQUE 约束,向 test1表中的 tname 列添加 UNIQUE 约束 ALTER TABLE test1 ADD CONSTRAINT UNIQ_ TNAME UNIQUE(tname)删除表上已定义的UNIQUE约束ALTER TABLE test1 DROP CONSTRAINT UNIQ_ TNAME,添加和删除UNIQUE 约束,FOREIGN KEY 约束,FOREIGN KEY 约束：定义到同表或其他表中具有 PRIMARY KEY 或者 UNIQUE 约束的列的引用语法：CONSTRAINT 约束名FOREIGN KEY(列,n)REFERENCES 引用表(引用列,n)具有 FOREIGN KEY 约束的列的取值范围只能是被引用的列的列值,4.2.2.5 FOREIGN KEY 约束,FOREIGN KEY 约束（续）,应用 FOREIGN KEY 约束的注意事项FOREIGN KEY 子句中指定的列的个数和数据类型必须和 REFERENCES 子句中指定的列的个数和数据类型匹配并不自动创建索引修改数据的时候，用户必须在被 FOREIGN KEY 约束引用的表上具有 SELECT 或 REFERENCES 权限若引用的是同表中的列，那么可只用 REFERENCES 子句而省略 FOREIGN KEY 子句,4.2.2.5 FOREIGN KEY 约束,FOREIGN 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),FOREIGN KEY 约束（续）,创建完表后通过修改表增加FOREIGN KEY 约束使用 FOREIGN KEY 约束，确保 Orders 表中的客户标识与 Customers 表中的有效的客户标识相关联,4.2.2.5 FOREIGN KEY 约束,alter table stuscoreadd constraint fk_student_stuscoreforeign key(id)references student(id),FOREIGN KEY 约束（续）,FOREIGN KEY 约束的删除alter table stuscore drop constraint fk_student_stuscore,级联引用完整性,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：任何企图删除或者更新被其他表的外键所引用的键都将引发一个错误，对数据的改变会被回滚。NO ACTION 是默认值CASCADE：若父表中的行变化了，则引用表中相应的行也自动变化,级联引用完整性（续）,4.2.2.6 级联引用完整性,应用 CASCADE 选项的注意事项可在多个具有引用关系的表之间组合 CASCADE 和 NO ACTION 选项。若 SQL Server 遇到 NO ACTION，则中断并回滚所有相关的 CASCADE 动作CASCADE 选项不能对定义为 rowversion 数据类型的外键或主键列指定,第5章 实现数据完整性,数据完整性的类型约束禁用约束默认值和规则决定使用何种强制方法推荐操作,禁用约束,禁用现有数据上的约束检查在加载新数据时禁用约束检查,4.3 禁用约束,禁用现有数据上的约束检查,当在表上添加约束的时候，我们可以禁用对已有数据的约束检查语法：ALTER TABLE 表名 WITH CHECKWITH NOCHECK ADD CONSTRAINT 约束名 FOREIGN KEY(column,n)REFERENCES 引用表(引用列,n)CHECK(搜索条件),4.3.1 禁用现有数据上的约束检查,禁用现有数据上的约束检查（续）,对已有数据禁用约束检查的注意事项只能禁用 CHECK 和 FOREIGN KEY 约束当为一个已有数据的表添加 CHECK 或 FOREIGN KEY 约束的时候，使用 WITH NOCHECK 选项来禁用对已有数据的约束检查,4.3.1 禁用现有数据上的约束检查,在加载新数据时禁用约束检查,为了避免约束检查的开销，有时候可能希望禁用约束已经确保数据符合约束数据并不符合约束，但稍后可以更改其值并重用约束语法：ALTER TABLE 表名 CHECK|NOCHECK CONSTRAINT ALL|约束名,.n查看约束是启用还是禁用的状态系统存储过程 sp_help系统函数 OBJECTPROPERTY 的 CnstIsDisabled 属性,4.3.2 在加载新数据时禁用约束检查,第5章 实现数据完整性,数据完整性的类型约束禁用约束默认值和规则决定使用何种强制方法推荐操作,使用默认值和规则,语法：创建：CREATE DEFAULT 默认值 AS 常量表达式 删除：DROP DEFAULT 默认值,.n 绑定一个默认：sp_bindefault 解除默认值绑定：sp_unbindefault创建默认值的注意事项列的默认值必须符合此列上的任何规则或CHECK约束,4.4 使用默认值和规则,使用默认和规则（续）,规则：规则指定了能插入列的可接受的值。它确保数据在指定值域内，匹配某个模式，或者匹配指定列表中的项。语法：创建：CREATE RULE 规则 AS 条件表达式 删除：DROP RULE 规则,.n 绑定规则：sp_bindrule 分离已绑定的规则：sp_unbindrule关于规则的注意事项规则定义可以包含任何在 WHERE 子句中有效的表达式一个列或者用户定义数据类型只能被一个规则绑定,4.4 使用默认和规则,第5章 实现数据完整性,数据完整性的类型约束禁用约束默认值和规则决定使用何种强制方法推荐操作,决定使用何种强制方法,4.1 决定使用何种强制方法,应综合考虑功能性和性能开销对于基本的完整性逻辑，例如有效值和维护表间的关系，最好使用声明式完整性约束如果要维护复杂的、大量的、非主键或外键关系一部分的数据，必须使用触发器或存储过程,第5章 实现数据完整性,数据完整性的类型约束禁用约束默认值和规则决定使用何种强制方法推荐操作,推荐操作,4.6 推荐操作,目标定义和使用 DEFAULT 和 CHECK 约束定义和使用 PRIMARY KEY 和 FOREIGN KEY 约束创建和使用 SQL Server 2000 规则和默认值练习 1 定义 DEFAULT 约束练习 2 定义 CHECK 约束练习 3 定义 PRIMARY KEY 约束练习 4 定义 FOREIGN KEY 约束可选 创建默认值和规则,实验 实现数据完整性,回顾,学习完本章后，将能够：描述数据完整性的类型描述强制数据完整性的方法决定使用何种约束和创建约束定义和使用 DEFAULT、CHECK、PRIMARY KEY、UNIQUE 和 FOREIGN KEY 约束禁用约束检查描述和使用默认值和规则决定使用何种数据完整性强制方法,