07-第7章-数据库完整性

第七章数据库完整性学习要点数据库完整性概述SQL2005完整性实现触发器概述创建、修改、查看、删除运用说明AFTER触发器INSTEADOF触发器数据库完整性概述数据完整性（DataIntegrity）定义指数据的精确性和一样性。目的防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息分类实体完整性：用于保证数据库中数据表的每一个特定实体的记录都是唯一的。域完整性：用于保证指定列的数据具有正确的数据类型、格式和有效的数据范围。参照完整性：确保同一键值在全部相关联表之间都一样。用户定义完整性：由用户自定义的不属于其他任何完整性分类的特定业务规则。数据库完整性概述数据完整性（DataIntegrity）为维护数据库的完整性，DBMS必需：供应定义完整性约束条件的机制供应完整性检查的方法违约处理学习要点完整性类型实现技术实体完整性UNIQUE约束、PRIMARYKEY约束、IDENTITY属性域完整性CHECK约束、FOREIGNKEY约束、DEFAULT定义、NOTNULL定义、默认对象、规则对象参照完整性FOREIGNKEY约束、CHECK约束、触发器用户定义完整性CREATETABLE中的所有列级和表级约束、存储过程、触发器完整性实现SQLServer2005完整性实现DEFAULT约束CHECK约束PRIMARYKEY约束UNIQUE约束FOREIGNKEY约束NOTNULL约束标识列默认值规则学习要点约束DEFAULT约束定义在用户未输入某些列的数据时，数据库系统为用户供应的默认值。（默认值必需与所约束列的数据类型相一样）是可以避开不允许为空值的数据错误可以加快用户的输入速度创建SSMS方式T-SQL方式约束列表运用SSMS工具DEFAULT约束DEFAULT约束CREATETABELBookInfo(b_IDVARCHAR(16),……b_DetailVARCHAR(100)DEFAULT‘暂无描述’)运用T-SQL定义（一）DEFAULT约束CREATETABEL数据表名(列名数据类型[CONSTRAIN约束名]DEFAULT默认值

[，...])列级约束示例NEXTCREATETABELBookInfo(……b_DetailVARCHAR(100)，

……CONSTRAINTde_DetailDEFAULT‘暂无描述’FORb_Detail

)运用T-SQL定义（二）DEFAULT约束NEXTCREATETABEL数据表名(

列名1数据类型，

……

[CONSTRAIN约束名]DEFAULT

默认值FOR

(列名[,…n])

[,...])表级约束示例运用T-SQL定义(三）DEFAULT约束ALTERTABEL数据表名(ADD[CONSTRAIN约束名]DEFAULT

默认值FOR

(列名[,…n])[，...])修改表：添加约束ALTERTABELBookInfo(……ADDCONSTRAINTde_DetailDEFAULT‘暂无描述’FORb_Detail

……)示例返回约束CHECK约束定义限制用户输入数据的取值范围、格式等。一个列可以定义多个CHECK约束。当往表中插入记录或修改相应列的值时，系统检查约束条件是否满足，若不满足则拒绝执行数据操作。创建SSMS方式T-SQL方式约束列表运用SSMS工具CHEECK约束CHECK约束CREATETABEL数据表名(列名数据类型[CONSTRAIN约束名]CHECK(逻辑表达式)[，...])CREATETABELBookInfo(……b_QuantitySMALLINTCHECK(b_Quantity>=1ANDb_Quantity<=100),……)运用T-SQL定义（一）CHECK约束列级约束示例NEXT运用T-SQL定义（二、三）CHECK约束返回CREATETABEL数据表名(

列名1数据类型，

……

[CONSTRAIN约束名]CHECK(逻辑表达式)

[,...])表级约束ALTERTABEL数据表名(ADD[CONSTRAIN约束名]CHECK(逻辑表达式)[，...])修改表：添加约束约束PRIMARYKEY约束定义若是单列作为主键，可以在列级定义或表级定义若是列集合作为主键，必需在表级定义一个表只能有一个PRIMARYKEY约束，受约束的列或列集合的值不能为空和重复，必需能唯一标识表中的行。定义了PRIMARYKEY约束的列的列宽不能更改。创建SSMS方式T-SQL方式约束列表运用SSMS工具返回PRIMARY约束运用T-SQL定义（一）NEXTPRIMARY约束CREATETABEL数据表名(列名数据类型[CONSTRAIN约束名]PRIMARYKEY[，...])CREATETABELBookInfo(b_IDVARCHAR(16)PRIMARYKEY，

……)列级约束示例默认的约束名为：PK_BookInfo运用T-SQL定义（二）PRIMARY约束CREATETABEL数据表名(列名1数据类型，……[CONSTRAIN约束名]PRIMARYKEY(列名1[,…n])[,...])CREATETABELBorrowReturn(s_IDCHAR(8)

NOTNULL,r_IDCHAR(8)NOTNULL,……

PRIMARYKEY(s_ID,r_ID))表级约束示例组合键必需在表级定义默认的约束名为：PK_BorrowReturnNEXT运用T-SQL定义（三）返回PRIMARY约束ALTERTABEL数据表名(ADD[CONSTRAIN约束名]PRIMARYKEY(列名[,…])[，...])ALTERTABLEBorrowReturnADDCONSTRAINTPK_BorrowReturnPRIMARYKEY(s_ID,r_ID)示例修改表：添加约束约束UNIQUE约束定义也称唯一值约束，确保在非主键列中不输入重复值。一个表可以定义多个UNIQUE约束。在允许空值的列上保证唯一性时，应运用UNIQUE约束。当往表中插入记录或修改相应列的值时，系统检查是否有重复值，若有则拒绝执行数据操作。创建SSMS方式T-SQL方式约束列表运用SSMS工具返回UNIQUE约束运用T-SQL定义（一）NEXTUNIQUE约束CREATETABEL数据表名(列名数据类型[CONSTRAIN约束名]UNIQUE[，...])CREATETABELReaderType(……rt_NameVARCHAR(10)UNIQUE,……)列级约束示例运用T-SQL定义（二、三）UNIQUE约束返回CREATETABEL数据表名(

列名1数据类型，

……

[CONSTRAIN约束名]UNIQUE

(列名1[,…n])

[,...])表级约束ALTERTABEL数据表名(ADD[CONSTRAIN约束名]UNIQUE(列名1[,…n])[，...])修改表：添加约束约束FOREIGNKEY约束定义也称为外键约束，用于强制实现参照完整性，建立两个表之间的关系。可以规定表中的某列参照同一个表或另外一个表中已有的PRIMARYKEY约束或UNIQUE约束的列。一个表可以有多个FOREIGNKEY约束。创建SSMS方式T-SQL方式约束列表运用SSMS工具Step1NEXTFOREIGNKEY约束运用SSMS工具Step2NEXTFOREIGNKEY约束运用SSMS工具Step3NEXTFOREIGNKEY约束运用SSMS工具Step4返回FOREIGNKEY约束运用T-SQL定义（一）示例CREATETABEL数据表名(列名数据类型[CONSTRAIN约束名][FOREIGNKEY]

REFERENCES参照主键表[(参照列)]

[ONDELETECASCADE|ONUPDATECASCADE][，...])列级约束FOREIGNKEY约束NOACITON：默认选项。将引发错误，撤销对父表的删除（更新）操作

CASCADE：当在主表删除（更新）一行，则自动将子表中的对应行删除（更新）

SETNULL：删除（更新）父表的一行，子表相应行的外键值被置为NULL

SETDEFAULT：删除（更新）父表的一行，子表相应行的外键值被置为外键的默认值运用T-SQL定义（一）FOREIGNKEY约束CREATETABELBookInfo(b_IDVARCHAR(16)PRIMARYKEY, b_NameVARCHAR(50)NOTNULL,bt_IDCHAR(10)REFERENCESBookType(bt_ID),……)示例NEXT运用T-SQL定义（二）示例CREATETABEL数据表名(列名1数据类型，……[CONSTRAIN约束名]FOREIGNKEY(列名)

REFERENCES

参照主键表[(参照列)]

[ONDELETECASCADE|ONUPDATECASCADE][,...])表级约束FOREIGNKEY约束运用T-SQL定义（二）NEXTFOREIGNKEY约束CREATETABLEBorrowReturn(br_IDCHAR(6)PRIMARYKEY,s_IDCHAR(8)REFERENCESNOTNULL,r_IDCHAR(8)REFERENCESNOTNULL,……

FOREIGNKEY(s_ID)REFERENCES

BookStore(s_ID)

，

FOREIGNKEY(r_ID)REFERENCES

ReaderInfo(r_ID)

)示例运用T-SQL定义（三）UNIQUE约束返回ALTERTABEL数据表名(ADD[CONSTRAIN约束名]FOREIGNKEY(列名)

REFERENCES参照主键表[(参照列)]

[ONDELETECASCADE|ONUPDATECASCADE][，...])修改表：添加约束ALTERTABELBookInfo(ADDFOREIGNKEY(bt_ID)REFERENCESBookType(bt_ID)

ONDELETECASCADE

ONUPDATECASCADE,)示例约束NOTNULL约束实质表中列可以定义为允许或不允许空值。默认状况下，创建表列允许空值。一个列中出现NULL值，意味着用户还没有为该列输入值。若设置为NOTNULL，用户没有输入该列值，则引发错误。示例SSMS方式T-SQL方式约束列表运用SSMS工具返回NOTNULL约束运用T-SQL定义NOTNULL约束CREATETABEL数据表名(列名数据类型[CONSTRAIN约束名]NULL|NOTNULL[，…])表级约束CREATETABELBookInfo(b_IDvarchar(16)PRIMARYKEY, b_Namevarchar(50)UNIQUENOTNULL, bt_IDchar(10)REFERENCESBookType(bt_ID), b_Authorvarchar(20)NOTNULL,b_Translatorvarchar(20)NULL, …… )示例返回约束标识列（IDENTITY）概述当用户向表中插入新的数据行时，系统自动为该行的IDENTITY列赋值，并保证其值在表中的唯一性。常与PRIMARYKEY约束一起运用。每个表中只能有一个IDENTITY列，其列值不能由用户更新，也不允许绑定默认值或建立DEFAULT约束标识列的有效数据类型可以是任何整数数据类型分类的数据类型(bit数据类型除外)，也可以是decimal数据类型，但不允许出现小数。创建SSMS方式T-SQL方式约束列表运用SSMS工具返回标识列（IDENTITY）

运用T-SQL定义返回CREATETABEL数据表名(列名数据类型IDENTITY[(种子,增量)]，[，...])CREATETABELBorrowReturn(……br_IDINTIDENTITY(1,1)PRIMARYKEY，)语法示例标识列（IDENTITY）

DEFAULT（默认值）概述“默认值”是一种数据库对象，可以被绑定到列。假如绑定的列或者数据类型没有明确供应值，则将默认指定的数据插入。“默认值”的执行与DEFAULT约束功能相同，但是DEFAULT约束定义和表存储在一起，当除去表时，将自动除去它。当在多个列中，特殊是不同表中的列中多次运用同一默认值时，适合接受“默认”技术。管理管理“默认值”创建绑定解绑示例CREATE

DEFAULT

默认名称

AS

常数表达式sp_bindefault‘默认名称’,‘数据表名.列名’sp_unbindefault‘数据表名.列名’DEFAULT（默认值）示例：创建绑定解绑返回sp_bindefault‘d_Ssex’,‘Student.Ssex’sp_unbindefault

‘Student.Ssex’USEStudyGOCREATEDEFAULTd_SsexAS‘女’DEFAULT（默认值）RULE（规则）概述“规则”是一种数据库对象，可以绑定到列，定义之后可以反复运用。执行“规则”功能与CHECK约束相同。每个列只能有一个绑定的规则。但列可以同时具有规则和多个CHECK约束。约束列表RULE（规则）管理“规则”创建绑定解绑示例CREATE

RULE

规则名称

AS

条件表达式sp_bindrule‘规则名’,‘数据表名.列名’sp_unbindrule‘数据表名.列名’RULE（规则）示例：创建绑定解绑返回sp_bindrule‘r_Ssex’,‘Student.Ssex’sp_unbindrule

‘Student.Ssex’USEStudyGOCREATERULEr_SsexAS@sex=‘女’OR@sex=‘男’触发器概述触发器触发器是特殊类型的存储过程，主要由操作事务(INSERT、UPDATE、DELETE)触发而被自动执行。与表相关联：必需定义在表上。不能干脆调用，也不能传递或接受参数触发器可以实现比约束更困难的数据完整性，常常用于加强数据的完整性约束和业务规则。触发器本身是一个特殊的事务单位。触发器概述触发器的类型按触发时刻分

AFTER触发器执行触发操作和处理完约束后激发，只能定义于表上，同一个表上可定义多个。

INSTEAD触发器

由触发器的程序代替触发语句的执行，在处理约束之前激发，可在表或视图上创建，同一个表或视图只可定义一个。按触发语句分

INSERT触发器

UPDATE触发器、

DELETE触发器触发器Inserted表和deleted表触发器触发时，系统自动在内存创建deleted表或inserted表。它们是只读的，不允许修改；触发器执行完成后，自动删除。inserted表临时保存了插入或更新后的记录行可以从inserted表中检查插入的数据是否满足业务需求，假如不满足，则向用户报告错误消息，并回滚插入操作。deleted表临时保存了删除或更新前的记录行可以从deleted表中检查被删除的数据是否满足业务需求，假如不满足，则向用户报告错误消息，并回滚插入操作。触发器触发器运用的限制CREATETRIGGER必需是批处理中的第一条语句。在同一个表中约束的执行优先于触发器，若在操作中触发器与约束发生冲突，触发器将不执行。不能在临时表上建立触发器，但是在触发器定义中可以引用临时表。触发器中不允许以下T-SQL语句：ALTERDATABASECREATEDATABASEDISKINITDISKRESIZEDROPDATABASELOADDATABASELOADLOGRECONFIGURERESTOREDATABASERESTORELOG

触发器Inserted表和deleted表

inserted表和deleted表存放的信息修改操作inserted表deleted表增加(INSERT)记录存放新增的记录------删除(DELETE)记录-----存放被删除的记录修改(UPDATE)记录存放更新后的记录存放更新前的记录触发器创建（SSMS）触发器创建（SSMS）创建触发器的模板，只需修改相应的参数就可创建触发器触发器创建（T－SQL）基本语句格式CREATETRIGGER

触发器名ON

表名或视图名{[FOR|AFTER]|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}AS

[IFUPDATE(列名1)[{AND|OR}UPDATE(列名2)][...n]]SQL语句触发器创建（T－SQL）基本语句格式CREATETRIGGER

触发器名ON

表名或视图名{[FOR|AFTER]|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}AS

[IFUPDATE(列名1)[{AND|OR}UPDATE(列名2)][...n]]SQL语句触发器创建（T－SQL）参数说明AFTER:执行指定操作语句和处理完约束后激发触发器。FOR:假如仅指定FOR关键字，则AFTER是默认设置。INSTEADOF:指定语句激发触发器并被代替执行。DELETE、INSERT、UPDATE：指定激活触发器的数据修改语句，必需至少指定一个选项，若指定的选项多于一个，需用逗号分隔这些选项。AS：指定触发器要执行的操作。IFUPDATE(列名1)[{AND|OR}UPDATE(列名2)]:指定何列数据修改时激发触发器。不能用于DELETE语句。触发器创建（T－SQL）示例1CREATETRI