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

第5章 实现数据完整性,数据完整性的类型 约束 禁用约束 默认值和规则 决定使用何种强制方法 推荐操作,数据完整性的类型,数据完整性的类型,4.1 数据完整性的类型,数据完整性 指的是数据库中存储的数据的一致性和准确性 数据完整性的类型：域完整性、实体完整性、引用完整性 域完整性 域（或列）完整性是指对列指定一组有效的值并决定是否可为空值 实体完整性 实体（或表）完整性要求表中的所有行都有一个惟一的标识符，称为主键值 引用完整性 引用完整性确保主键（在被引用表中）和外键（在引用表中）之间的关系得到保持,第5章 实现数据完整性,数据完整性的类型 约束 禁用约束 默认值和规则 决定使用何种强制方法 推荐操作,决定使用何种约束 约束的类型,约束,4.2 约束,决定使用何种约束,4.2.1 决定使用何种约束,创建约束,使用 CREATE TABLE 或者 ALTER TABLE CREATE 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 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 约束,在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 Northwind ALTER TABLE dbo.Customers ADD CONSTRAINT 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.24 UNIQUE 约束,UNIQUE 约束（续）,PRIMARY KEY 和 UNIQUE 约束 声明 PRIMARY KEY 或 UNIQUE 约束的结果只是自动创建了一个指定列上的惟一索引，通过惟一索引来确保值的惟一性 可空性 PRIMARY KEY 的各个列必须声明为 NOT NULL，而 UNIQUE 的各个列可以声明为允许 NULL 值 在惟一索引中，认为所有的 NULL 值是相等的 索引的属性 PRIMARY KEY 约束所创建的惟一索引默认为 CLUSTERED，除非表中另外一列已经声明为 CLUSTERED UNIQUE 约束所创建的惟一索引默认为 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 stuscore add constraint fk_student_stuscore foreign 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 使用默认值和规则,使用默认和规则（续）,规则：规则指定了能插入