07-第7章　数据库完整性

null　　　　数据库完整性第七章　　　　数据库完整性学习要点学习要点 数据库完整性概述 SQL2005完整性实现 触发器 概述 创建、修改、查看、删除 使用 说明 AFTER触发器 INSTEAD　OF触发器数据库完整性概述数据库完整性概述数据完整性 （Data Integrity） 定义 指数据的准确性和一致性。 目的 防止数据库中存在不符合语义规定的数据和防止因错误 信息的输入输出造成无效操作或错误信息 分类 实体完整性：用于保证数据库中数据表的每一个特定实体的记 录都是唯一的。 域完整性：用于保证指定列的数据具有正确的数据类型、格 式和有效的数据范围。 参照完整性：确保同一键值在所有相关联表之间都一致。 用户定义完整性 ：由用户自定义的不属于其他任何完 整性分类的特定业务规则。数据库完整性概述数据库完整性概述数据完整性 （Data Integrity） 为维护数据库的完整性，DBMS必须： 提供定义完整性约束条件的机制 提供完整性检查的方法 违约处理学习要点完整性实现　完整性实现　SQL Server 2005完整性实现 DEFAULT约束 CHECK约束 PRIMARY KEY约束 UNIQUE约束 FOREIGN KEY约束 NOT NULL约束 标识列 默认值 规则 学习要点约束　约束　DEFAULT约束 定义 在用户未输入某些列的数据时，数据库系统为用户提供的默认值。 （默认值必须与所约束列的数据类型相一致） 是可以避免不允许为空值的数据错误 可以加快用户的输入速度 创建　 SSMS方式 T-SQL方式 约束列表DEFAULT约束　使用SSMS工具 DEFAULT约束DEFAULT约束　DEFAULT约束　CREATE TABEL BookInfo ( b_ID VARCHAR(16), …… b_Detail VARCHAR(100) DEFAULT ‘暂无描述’ )使用T-SQL定义（一） DEFAULT约束　CREATE TABEL 数据表名 ( 列名 数据类型 [CONSTRAIN 约束名] DEFAULT 默认值 [，...] )列级约束 示 例NEXTDEFAULT约束　CREATE TABEL BookInfo ( …… b_Detail VARCHAR(100)， …… CONSTRAINT de_Detail DEFAULT ‘暂无描述’ FOR b_Detail )使用T-SQL定义（二） DEFAULT约束　NEXTCREATE TABEL 数据表名 ( 列名1 数据类型， …… [CONSTRAIN 约束名] DEFAULT 默认值 FOR (列名 [,…n]) [,...] )表级约束示 例DEFAULT约束　使用T-SQL定义(三） DEFAULT约束　ALTER TABEL 数据表名 ( ADD [CONSTRAIN 约束名] DEFAULT 默认值 FOR (列名 [,…n]) [，...] )修改表：添加约束ALTER TABEL BookInfo ( …… ADD CONSTRAINT de_Detail DEFAULT ‘暂无描述’ FOR b_Detail …… )示 例返回约束　约束　CHECK约束 定义 限制用户输入数据的取值范围、格式等。 一个列可以定义多个CHECK 约束。 当往表中插入记录或修改相应列的值时，系统检查约束条件是否满足，若不满足则拒绝执行数据操作。 创建　 SSMS方式 T-SQL方式约束列表CHECK约束使用SSMS工具 CHEECK约束CHECK约束CHECK 约束　CREATE TABEL 数据表名 ( 列名 数据类型 [CONSTRAIN 约束名] CHECK (逻辑表达式) [，...] )CREATE TABEL BookInfo ( …… b_Quantity SMALLINT CHECK (b_Quantity>=1 AND b_Quantity <=100), …… )使用T-SQL定义（一）CHECK 约束　列级约束 示 例NEXTCHECK 约束　使用T-SQL定义（二、三）CHECK 约束　返回CREATE TABEL 数据表名 ( 列名1 数据类型， …… [CONSTRAIN 约束名] CHECK (逻辑表达式) [,...] )表级约束 ALTER TABEL 数据表名 ( ADD [CONSTRAIN 约束名] CHECK (逻辑表达式) [，...] )修改表：添加约束约束　约束　PRIMARY KEY约束 定义 若是单列作为主键，可以在列级定义或表级定义 若是列集合作为主键，必须在表级定义 一个表只能有一个PRIMARY KEY约束，受约束的列或列集合的值不能为空和重复，必须能唯一标识表中的行。 定义了PRIMARY KEY 约束的列的列宽不能更改。 创建　 SSMS方式 T-SQL方式约束列表PRIMARY约束　使用SSMS工具返回PRIMARY约束　PRIMARY约束　使用T-SQL定义（一）NEXTPRIMARY约束　CREATE TABEL 数据表名 ( 列名 数据类型 [CONSTRAIN 约束名] PRIMARY KEY [，...] )CREATE TABEL BookInfo ( b_ID VARCHAR(16) PRIMARY KEY， …… )列级约束 示 例默认的约束名为：PK_BookInfo PRIMARY约束　使用T-SQL定义（二）PRIMARY约束　CREATE TABEL 数据表名 ( 列名1 数据类型， …… [CONSTRAIN 约束名] PRIMARY KEY (列名1[,…n]) [,...] )CREATE TABEL BorrowReturn ( s_ID CHAR(8) NOT NULL, r_ID CHAR(8) NOT NULL, …… PRIMARY KEY (s_ID,r_ID) )表级约束 示 例组合键必须在表级定义 默认的约束名为： PK_ BorrowReturn NEXTPRIMARY约束　使用T-SQL定义（三）返回PRIMARY约束　ALTER TABEL 数据表名 ( ADD [CONSTRAIN 约束名] PRIMARY KEY( 列名[, …] ) [，...] )ALTER TABLE BorrowReturn ADD CONSTRAINT PK_ BorrowReturn PRIMARY KEY(s_ID,r_ID)示 例修改表：添加约束约束　约束　UNIQUE约束 定义 也称唯一值约束，确保在非主键列中不输入重复值。 一个表可以定义多个UNIQUE 约束。 在允许空值的列上保证唯一性时，应使用UNIQUE约束。 当往表中插入记录或修改相应列的值时，系统检查是否有重复值，若有则拒绝执行数据操作。 创建　 SSMS方式 T-SQL方式约束列表UNIQUE约束使用SSMS工具返回UNIQUE约束UNIQUE约束　使用T-SQL定义（一）NEXTUNIQUE约束　CREATE TABEL 数据表名 ( 列名 数据类型 [CONSTRAIN 约束名] UNIQUE [，...] )CREATE TABEL ReaderType ( …… rt_ Name VARCHAR(10) UNIQUE , …… )列级约束 示 例UNIQUE约束　使用T-SQL定义（二、三）UNIQUE约束　返回CREATE TABEL 数据表名 ( 列名1 数据类型， …… [CONSTRAIN 约束名] UNIQUE (列名1[,…n]) [,...] )表级约束 ALTER TABEL 数据表名 ( ADD [CONSTRAIN 约束名] UNIQUE (列名1[,…n]) [，...] )修改表：添加约束约束　约束　FOREIGN KEY约束 定义 也称为外键约束，用于强制实现参照完整性，建立两个表之间的关系。 可以规定表中的某列参照同一个表或另外一个表中已有的PRIMARY KEY 约束或UNIQUE 约束的列。 一个表可以有多个FOREIGN KEY 约束。 创建 SSMS方式 T-SQL方式约束列表FOREIGN KEY约束使用SSMS工具 Step1NEXTFOREIGN KEY约束FOREIGN KEY约束使用SSMS工具 Step2NEXTFOREIGN KEY约束FOREIGN KEY约束使用SSMS工具 Step3NEXTFOREIGN KEY约束FOREIGN KEY约束使用SSMS工具 Step4返回FOREIGN KEY约束FOREIGN KEY约束使用T-SQL定义（一）示例CREATE TABEL 数据表名 ( 列名 数据类型 [CONSTRAIN 约束名] [FOREIGN KEY]   REFERENCES 参照主键表[ (参照列) ] [ ON DELETE CASCADE | ON UPDATE CASCADE ] [，...] )列级约束 FOREIGN KEY约束 NO ACITON：默认选项。将引发错误，撤销对父表的删除（更新）操作 CASCADE：当在主表删除（更新）一行，则自动将子表中的对应行删除（更新） SET NULL：删除（更新）父表的一行，子表相应行的外键值被置为NULL SET DEFAULT：删除（更新）父表的一行，子表相应行的外键值被置为 外键的默认值FOREIGN KEY约束使用T-SQL定义（一）FOREIGN KEY约束CREATE TABEL BookInfo ( b_ID VARCHAR(16) PRIMARY KEY, b_Name VARCHAR(50) NOT NULL, bt_ID CHAR(10) REFERENCES BookType(bt_ID), …… )示 例NEXTFOREIGN KEY约束使用T-SQL定义（二）示例CREATE TABEL 数据表名 ( 列名1 数据类型， …… [CONSTRAIN 约束名] FOREIGN KEY(列名)   REFERENCES 参照主键表[ (参照列) ]   [ ON DELETE CASCADE | ON UPDATE CASCADE ] [,...] )表级约束 FOREIGN KEY约束FOREIGN KEY约束使用T-SQL定义（二）NEXTFOREIGN KEY约束CREATE TABLE BorrowReturn ( br_ID CHAR(6) PRIMARY KEY , s_ID CHAR(8) REFERENCES NOT NULL, r_ID CHAR(8) REFERENCES NOT NULL, …… FOREIGN KEY (s_ID) REFERENCES BookStore(s_ID) ， FOREIGN KEY (r_ID) REFERENCES ReaderInfo(r_ID) ) 示 例UNIQUE约束　使用T-SQL定义（三）UNIQUE约束　返回ALTER TABEL 数据表名 ( ADD [CONSTRAIN 约束名] FOREIGN KEY( 列名 )   REFERENCES 参照主键表[ (参照列) ]  [ ON DELETE CASCADE | ON UPDATE CASCADE ] [，...] )修改表：添加约束ALTER TABEL BookInfo ( ADD FOREIGN KEY (bt_ID) REFERENCES BookType(bt_ID) ON DELETE CASCADE ON UPDATE CASCADE , )示 例约束　约束　NOT NULL约束 实质 表中列可以定义为允许或不允许空值。 默认情况下，创建表列允许空值。一个列中出现NULL 值，意味着用户还没有为该列输入值。 若设置为NOT NULL，用户没有输入该列值，则引发错误。 示例　 SSMS方式 T-SQL方式 约束列表NOT NULL约束使用SSMS工具返回NOT NULL约束NOT NULL 约束　使用T-SQL定义NOT NULL 约束　CREATE TABEL 数据表名 ( 列名 数据类型 [CONSTRAIN 约束名] NULL | NOT NULL [，…] )表级约束 CREATE TABEL BookInfo ( b_ID varchar(16) PRIMARY KEY, b_Name varchar(50) UNIQUE NOT NULL, bt_ID char(10) REFERENCES BookType(bt_ID), b_Author varchar(20) NOT NULL, b_Translator varchar(20) NULL, …… )示 例返回约束　约束　标识列（IDENTITY） 概述 当用户向表中插入新的数据行时，系统自动为该行的IDENTITY 列赋值，并保证其值在表中的唯一性。 常与PRIMARY KEY 约束一起使用。 每个表中只能有一个IDENTITY 列，其列值不能由用户更新，也不允许绑定默认值或建立DEFAULT约束 标识列的有效数据类型可以是任何整数数据类型分类的数据类型(bit 数据类型除外)，也可以是decimal 数据类型，但不允许出现小数。 创建　 SSMS方式 T-SQL方式约束列表标识列（IDENTITY）　使用SSMS工具返回标识列（IDENTITY）　标识列（IDENTITY）　使用T-SQL定义返回CREATE TABEL 数据表名 ( 列名 数据类型 IDENTITY [(种子, 增量)] ， [，...] )CREATE TABEL BorrowReturn ( …… br_ID INT IDENTITY( 1 ,1 ) PRIMARY KEY， )语法 示 例标识列（IDENTITY）　DEFAULT（默认值） 　DEFAULT（默认值） 　概 述 “默认值”是一种数据库对象，可以被绑定到列。如果绑定的列或者数据类型没有明确提供值，则将默认指定的数据插入。 “默认值”的执行与DEFAULT 约束功能相同，但是DEFAULT 约束定义和表存储在一起，当除去表时，将自动除去它。 当在多个列中，特别是不同表中的列中多次使用同一默认值时，适合采用“默认”技术。管理DEFAULT（默认值） 　管理“默认值” 创建 绑定 解绑示例CREATE DEFAULT 默认名称 AS 常数表达式 sp_bindefault ‘默认名称’, ‘数据表名.列名’sp_unbindefault ‘数据表名.列名’DEFAULT（默认值） 　DEFAULT（默认值） 　示例： 创建 绑定 解绑返回sp_bindefault ‘d_Ssex’ , ‘Student.Ssex’sp_unbindefault ‘Student.Ssex’USE Study GO CREATE DEFAULT d_Ssex AS ‘女’DEFAULT（默认值） 　RULE（规则） 　RULE（规则） 　概 述 “规则”是一种数据库对象，可以绑定到列，定义之后可以反复使用。 执行“规则”功能与CHECK约束相同。 每个列只能有一个绑定的规则。但列可以同时具有规则和多个CHECK 约束。约束列表RULE（规则） 　RULE（规则） 　管理“规则” 创建 绑定 解绑示例CREATE RULE 规则名称 AS 条件表达式 sp_bindrule ‘规则名’, ‘数据表名.列名’sp_unbindrule ‘数据表名.列名’RULE（规则） 　RULE（规则） 　示例： 创建 绑定 解绑返回sp_bindrule ‘r_Ssex’ , ‘Student.Ssex’sp_unbindrule ‘Student.Ssex’USE Study GO CREATE RULE r_Ssex AS @sex=‘女’ OR @sex=‘男’触发器　触发器　概述 触发器 触发器是特殊类型的存储过程，主要由操作事件(INSERT、UPDATE、DELETE) 触发而被自动执行。 与表相关联：必须定义在表上。 不能直接调用，也不能传递或接受参数 触发器可以实现比约束更复杂的数据完整性，经常用于 加强数据的完整性约束和业务规则。 触发器本身是一个特殊的事务单位。触发器　触发器　概述 触发器的类型 按触发时刻分 AFTER触发器 执行触发操作和处理完约束后激发，只能定义于表上，同一个表上可定义多个。 INSTEAD触发器 由触发器的程序代替触发语句的执行，在处理约束之前激发，可在表或视图上创建，同一个表或视图只可定义一个。 按触发语句分 INSERT触发器 UPDATE触发器、 DELETE触发器 触发器　触发器　Inserted表和deleted表 触发器触发时，系统自动在内存创建deleted表或inserted表。它们是只读的，不允许修改；触发器执行完成后，自动删除。 inserted 表 临时保存了插入或更新后的记录行 可以从inserted表中检查插入的数据是否满足业务需求，如果不满足，则向用户报告错误消息，并回滚插入操作。 deleted 表 临时保存了删除或更新前的记录行 可以从deleted表中检查被删除的数据是否满足业务需求，如果不满足，则向用户报告错误消息，并回滚插入操作。触发器　触发器　触发器使用的限制 CREATE TRIGGER 必须是批处理中的第一条语句。 在同一个表中约束的执行优先于触发器，若在操作中触发器与约束发生冲突，触发器将不执行。 不能在临时表上建立触发器，但是在触发器定义中可以引用临时表。 触发器中不允许以下 T-SQL 语句：触发器　触发器　Inserted表和deleted表 inserted表和deleted表存放的信息触发器　触发器　创建（SSMS）触发器　触发器　创建（SSMS）创建触发器的 模板 个人简介word模板免费下载关于员工迟到处罚通告模板康奈尔office模板下载康奈尔 笔记本 模板 下载软件方案模板免费下载 ，只需修改相应的参数就可创建触发器触发器　触发器　创建（T－SQL） 基本语句格式 CREATE TRIGGER 触发器名 ON 表名或视图名 { [FOR | AFTER] | INSTEAD OF } { [INSERT] [,] [UPDATE] [,] [DELETE]} AS [IF UPDATE(列名1) [{AND|OR} UPDATE(列名2)] [...n] ] SQL语句触发器　触发器　创建（T－SQL） 基本语句格式 CREATE TRIGGER 触发器名 ON 表名或视图名 { [FOR | AFTER] | INSTEAD OF } { [INSERT] [,] [UPDATE] [,] [DELETE]} AS [IF UPDATE(列名1) [{AND|OR} UPDATE(列名2)] [...n] ] SQL语句触发器　触发器　创建（T－SQL） 参数说明 AFTER:执行指定操作语句和处理完约束后激发触发器。 FOR:如果仅指定 FOR 关键字，则 AFTER 是默认设置。 INSTEAD OF:指定语句激发触发器并被代替执行。 DELETE、INSERT、UPDATE：指定激活触发器的数据修改语句，必须至少指定一个选项，若指定的选项多于一个，需用逗号分隔这些选项。 AS：指定触发器要执行的操作。 IF UPDATE(列名1) [{AND|OR} UPDATE(列名2)]:指定何列数据修改时激发触发器。不能用于DELETE语句。触发器　触发器　创建（T－SQL） 示例1 CREATE TRIGGER tr_addbook ON BookInfo FOR INSERT,UPDATE AS BEGIN DECLARE @p_no CHAR(4) ,DECLARE @bt_no CHAR(10) SELECT @p_no=Publisher.p_ID FROM Publisher,inserted WHERE Publisher.p_ID=inserted.p_ID SELECT @bt_no=BookType.bt_ID FROM BookType,inserted WHERE BookType.bt_ID=inserted.bt_ID IF @p_no is NULL OR @bt_no is NULL BEGIN ROLLBACK TRANSACTION RAISERROR('不存在这样的出版社或图书类别！',16,10) END END触发器　触发器　创建（T－SQL） 示例2 CREATE TRIGGER tr_change ON BorrowReturn FOR INSERT AS BEGIN UPDATE BookStore SET s_Status='借出' WHERE s_ID=(SELECT s_ID FROM inserted) END 触发器　触发器　禁用或启用（SSMS）触发器　触发器　启用或启用触发器（T-SQL） 基本语句格式 示例 学习要点ALTER TABLE 表名 { ENABLE | DISABLE } TRIGGER { ALL | 触发器名[,……n] }ALTER TABLE BookInfo DISABLE TRIGGER tr_addbook触发器　触发器　修改触发器（T-SQL） 修改 删除 学习要点DROP TRIGGER 触发器名ALTER TRIGGER 触发器名 ON 表名或视图名 { [FOR | AFTER] | INSTEAD OF } { [INSERT] [,] [UPDATE] [,] [DELETE]} AS [IF UPDATE(列名1) [{AND|OR} UPDATE(列名2)] [...n] ] SQL语句