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

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