11设计数据的完整性

第9章:

设计数据的完整性本章内容完整性的根本概念使用约束实施数据完整性使用规章实施数据完整性使用默认值实施数据完整性数据完整性一般在表构造创立后，在数据输入之前进展，以避开无效的数据进入到数据库中。数据完整性是指数据的正确性、完备性和全都性，它是衡量数据库质量好坏的重要标准。完整性的概念数据的值正确无误。数据的类型必需正确。数据的值必需处于正确的范围内。数据的存在必需确保同一表格数据之间的和谐关系。数据的存在必需确保不同表格数据之间的和谐关系。满足完整性要求的数据的特点定义功能检查功能操作功能完整性把握机制的功能安全性：防止用户非法使用数据库，包括恶意破坏数据和越权存取数据。完整性：防止合法用户使用数据库时向数据库中参与不合语义的数据。数据安全性与完整性的区分数据完整性的分类域完整性

(列)实体完整性

（行）参照完整性

(表之间)域完整性是指一个字段的输入有效性，是否允许为空值。例：性别〔男，女〕实体完整性是指保证表中全部的记录的唯一。数据完整性的分类〔1〕参照完整性在插入或删除数据时，维护表间数据全都性的手段。一般建立在主码与外部码之间的关系。Salestablestor_idord_num•••title_id6380638070667066706768717228A2976QA74423D4482BU1032PS2091PC8888PS2091PS2091外码主码title_idtitleBU1032BU1111PC8888•••PS2091TheBusyExecutive’sDataCookingwithcomputers••••IsAngertheEnemy?Titlestable数据完整性的分类〔2〕用户定义的完整性表达实际运用的业务规章。例如：学生成绩0~100分职工年龄18~60岁保险金+补贴<根本工资数据完整性的分类〔3〕数据类型〔datatype〕触发器〔trigger〕存储过程〔storedprocedure〕约束〔constraint〕标识列〔identity〕默认值〔default〕规章〔rule〕实施数据完整性的途径使用约束实施数据完整性约束的用途是限制输入到表中的值的范围。约束是实施数据完整性的首选方法。约束的分类列级约束：只对一列起作用的约束。表级约束：对表中的多列起作用的约束。约束的类型PRIMARYKEY约束UNIQUE约束CHECK约束FOREIGNKEY约束级联参考完整性DEFAULT约束预备使用哪种约束类型完整性类型约束类型域完整性DEFAULTCHECKREFERENTIAL实体完整性PRIMARYKEYUNIQUE参照完整性FOREIGNKEYCHECK

PRIMARYKEY约束PRIMARYKEY约束指定表中的哪一列将充当表的主键PRIMARYKEY约束利用表中的一列或多列数据唯一地标识一行数据它强制执行列和引用的完整性每个表只有一个PRIMARYKEY约束。PRIMARYKEY约束的值必需是唯一的。不允许有空值SQLServer中最多可定义16列作为主键USENorthwindALTERTABLEdbo.CustomersADDCONSTRAINTPK_CustomersPRIMARYKEYNONCLUSTERED(CustomerID)主键列的创立和使用指导下面的例如创立一个新的Student表，并向该表中的StudID列添加PRIMARYKEY约束

CREATETABLEStudent ( StudID int PRIMARYKEY, FirstName varchar(20) NOTNULL, LastName varchar(20) NOTNULL， TelNo varchar(20) );

PRIMARYKEY约束例如1CREATETABLEStudent

(StudID int, FirstName varchar(20)NOTNULL, LastName varchar(20)NOTNULL, TelNo varchar(20), primarykey(StudID));CreateTableSC (snochar(6)notnull, cnochar(3)notnull, gmarkdecimal(5,2), primarykey(sno,cno));CreateTableSC (………., constraintPK_SCprimarykey(sno,cno));PRIMARYKEY约束例如2删除表上已定义的主键

ALTERTABLESCDROPCONSTRAINTPK_SC;在没有定义主键的表上，加上一个主键

ALTERTABLESCADDCONSTRAINTPK_SCprimarykey(sno,cno);

删除和添加主键在列中应用UNIQUE约束是为了确保列中不输入重复值列中全部行的值均不一样UNIQUE约束的创立和使用指导：可以向表中的多列应用UNIQUE约束允许该列存在NULL值向现有表应用UNIQUE约束时，会验证现有数据

UNIQUE约束在创立表格时定义UNIQUE约束在已经有数据但没有重复值的列或列的集合上添加UNIQUE约束修改或删除表上已定义的UNIQUE约束

UNIQUE约束的操作方式在创立Student表格时定义UNIQUE约束CREATETABLEStudent (StudID intunique, FirstName varchar(20)NOTNULL, LastName varchar(20)NOTNULL, TelNo varchar(20));CREATETABLEStudent (StudID intconstraintUNI_SIDunique, FirstName varchar(20)NOTNULL, LastName varchar(20)NOTNULL, TelNo varchar(20));

创立UNIQUE约束下面的例如向Student表中的TelNo列添加UNIQUE约束ALTERTABLEStudent ADDCONSTRAINTunq_telnoUNIQUE(TelNo)删除表上已定义的UNIQUE约束ALTERTABLEStudent DROPCONSTRAINTunq_telno添加和删除UNIQUE约束

CHECK约束CHECK约束通过检查输入表列的值来维护值域的完整性。CHECK约束通过对一个规律表达式的结果进展推断来对数据进展检查。ageintCHECK(age>=18andage<=65)可在一列上设置多个CHECK约束，一个CHECK约束可应用多个列。在创立表格时定义CHECK约束在已经建立的表上添加CHECK约束修改或删除表上已定义的CHECK约束

CHECK约束的操作方式在创立SC表格时定义CHECK约束CreateTableSC (snochar(6)notnull, cnochar(3)notnull, gmarkdecimal(5,2)check(gmark>=0andgmark<=100), primarykey(sno,cno));创立CHECK约束CreateTableSC (snochar(6)notnull, cnochar(3)notnull, gmarkdecimal(5,2),primarykey(sno,cno),check(gmark>=0andgmark<=100));CreateTableSC(……..,constraintchk_gmkcheck(gmark>=0andgmark<=100));下面的例如删除SC表中的CHECK约束ALTERTABLESC DROPCONSTRAINTchk_gmk下面的例如向SC表中添加CHECK约束ALTERTABLESC ADDCONSTRAINTchk_gmk check(gmark>=0andgmark<=100)添加和删除CHECK约束FOREIGNKEY约束主要用来维护两个表之间的全都性关系。当一行新的数据参与到表格中，或对表格中已经存在的外键上的数据进展修改时新的数据取值必需：存在于另一张表的主键上为NULL当主键所在表的数据被另一张表的外键引用时，用户无法对主键的数据进展修改或删除。

FOREIGNKEY约束创立一个新的employee表，并向该表中的dno列添加FOREIGNKEY约束CREATETABLEemployee (enochar(4)PRIMARYKEY, enamevarchar(8), sexchar(2),ageint,dnochar(2)REFERENCESdepartment(dno));创立FOREIGNKEY约束表employee和department的参照关系

CREATETABLEemployee (enochar(4)PRIMARYKEY, enamevarchar(8), sexchar(2), ageint, dnochar(2), FOREIGNKEY(dno)REFERENCESdepartment(dno));留意：说明dno为employee的外键时，department中的dno必需已被说明为主键。CREATETABLEemployee(enochar(4)PRIMARYKEY,enamevarchar(8),sexchar(2),ageint,dnochar(2),CONSTRAINTFRNKEY_DNOFOREIGNKEY(dno)REFERENCESdepartment(dno));删除表上已定义的外键

ALTERTABLEEMPLOYEEDROPCONSTRAINTFRNKEY_DNO;在没有定义外键的表上添加外键

ALTERTABLEEMPLOYEEADDCONSTRAINTFRNKEY_DNOFOREIGNKEY(dno)REFERENCESdepartment(dno);删除和添加FOREIGNKEY约束向外键所在表〔employee〕插入一行新元组，如输入外键的值不满足参照完整性约束规章，则系统拒绝。修改外键所在表〔employee〕的元组，如所修改外键的值不满足参照完整性约束规章，则系统拒绝。修改或删除父表〔department〕的主键，REFERENCES子句支持ONDELETE和ONUPDATE子句。参照完整性约束的实现策略ONDELETE和ONUPDATE的两个选项：[ONDELETE{CASCADE|NOACTION}][ONUPDATE{CASCADE|NOACTION}]假设没有指定ONDELETE或ONUPDATE的选项，则默认为NOACTION。ONDELETENOACTIONCREATETABLEemployee(enochar(4)PRIMARYKEY,enamevarchar(8),sexchar(2),ageint,dnochar(2),CONSTRAINTFRNKEY_DNOFOREIGNKEY(dno)REFERENCESdepartment(dno)ONDELETENOACTION);假设试图删除父表〔department〕的某行，而该行的键被其它表〔employee)的现有行中的外键所引用，则产生错误并回滚DELETE。ONUPDATENOACTIONCREATETABLEemployee(enochar(4)PRIMARYKEY,enamevarchar(8),sexchar(2),ageint,dnochar(2),CONSTRAINTFRNKEY_DNOFOREIGNKEY(dno)REFERENCESdepartment(dno)ONUPDATENOACTION);假设试图修改父表〔department〕某行的键值，而该行的键被其它表〔employee)的现有行中的外键所引用，则产生错误并回滚UPDATE。允许对父表某元组主键值删除或更新操作，相应外键所在表的具有该外键值的元组也将被删除或更新，保证参照完整性。ONDELETECASCADEONUPDATECASCADECASCADE〔表间级联〕CASCADENOACTIONCustomers

INSERTnewCustomerIDCustomerID(PK)1Orders

CustomerID(FK)UPDATEoldCustomerIDtonewCustomerID2Customers

CustomerID(PK)UPDATECustomerIDOrders

CustomerID(FK)1CASCADECustomers

DELETEoldCustomerIDCustomerID(PK)3

CREATETABLEemployee( enochar(4)PRIMARYKEY, enamevarchar(8), sexchar(2), ageint, dnochar(2), CONSTRAINTFRNKEY_DNO FOREIGNKEY(dno)REFERENCESdepartment(dno) ONDELETECASCADE);

CREATETABLEemployee(enochar(4)PRIMARYKEY, ……… FOREIGNKEY(dno)REFERENCESdepartment(dno) ONUPDATECASCADE);

DEFAULT

约束只用于INSERT语句每一个列只能有一个DEFAULT约束不能用于有IDENTITY属性的列允许一些系统供给的值〔USERS,CURRENT_USER,SESSION_USER,SYSTEM_USER或CURRENT_TIMESTAMP〕USENorthwind

ALTERTABLEdbo.Customers

ADD

CONSTRAINTDF_contactnameDEFAULT”UNKNOWN”

FORContactName使约束失效使对现有数据的约束检验失效使在载入新数据时的约束检验失效使对现有数据的约束检验失效能使之失效的只有CHECK和FOREIGNKEY约束，其他约束可以先卸载再进展添加。在添加新约束时，需在ALTERTABLE语句中使用WITHNOCHECK选项。假设现有数据不发生变化，使用WITHNOCHECK选项。假设数据被更新了，必需与CHECK约束全都。在添加约束前可以转变现有的数据。USENorthwindALTERTABLEdbo.EmployeesWITHNOCHECKADDCONSTRAINTFK_Employees_EmployeesFOREIGNKEY(ReportsTo)REFERENCESdbo.Employees(EmployeeID)使在载入新数据时的约束检验失效该功能仅用于CHECK和FOREIGNKEY约束在以下状况下使用:数据符合约束载入不符合约束的新数据USENorthwindALTERTABLEdbo.EmployeesNOCHECKCONSTRAINTFK_Employees_Employees规章是一组用T-SQL书写的条件语句Where子句中合法的语句一般都可以用作规章算术运算符关系运算符IN、LIKE、BETWEEN等关键字规章可以和列或者用户自定义数据类型捆绑在一起，检查完整性使用规章语法CREATERULErule

AScondition_expression参数rule:是新规章的名称。规章名称必需符合标识符规章。condition_expression:是定义规章的条件。规章可以是WHERE子句中任何有效的表达式。condition_expression包含一个变量。每个局部变量的前面都有一个@符号。创立规章例：CREATERULErange_ruleAS@range>=$1000AND@range<$20230CREATERULElist_ruleAS@listIN(”1389”,”0736”,”0877”)

CREATERULEpattern_ruleAS@valueLIKE”__-%[0-9]”创立规章存储过程sp_bindrule：将规章绑定到列或用户定义的数据类型。语法sp_bindrule[@rulename=]”rule”,

[@objname=]”object_name”

[,[@futureonly=]”futureonly_flag”]参数[@futureonly=]”futureonly_flag‘：仅当将规章绑定到用户定义的数据类型时才使用。假设futureonly_flag为NULL，那么新规章将绑定到用户定义数据类型的每一列，条件是此数据类型当前无规章或者使用用户定义数据类型的现有规章。绑定规章例1：EXECsp_bindrule”today”,”employees.[hire_date]”EXECsp_bindrule”rule_ssn”,”ssn”EXECsp_bindrule”rule_ssn”,”ssn”,”futureonly”例2：在数据库中创立一个Email的规章对象，其值为包含@的字符串。1.创立email规章2.执行绑定Createrulerl_email as@yLIKE”%@%”sp_bindrule‘rl_email’,’student.Email’从当前数据库中删除一个或多个用户定义的规章。语法DROPRULE{rule}[,...n]参数rule：是要删除的规章。N:是表示可以指定多个规章的占位符。删除规章假设要删除规章，首先需解除绑定。使用sp_unbindrule解除绑定规章。语法sp_unbindrule[@objname=]”object_name”

[,[@futureonly=]”futureonly_flag”]参数[@futureonly=]”futureonly_flag‘：仅用于解除用户定义数据类型规章的绑定。当参数futureonly_flag为futureonly时，现有的属于该数据类型的列不会失去指定规章。解除规章例1：EXECsp_unbindrule”employees.startdate”EXECsp_unbindrulessnEXECsp_unbindrule”ssn”,”futureonly”例2：将上一节创立的规章rl_email删除。1.执行解绑2.删除规章sp_unbindrule‘student.Email’DROPRULErl_email当绑定到列或用户定义数据类型时，假设插入时没有明确供给值，默认值便指定一个值，并将其插入到对象所绑定的列中〔或者，在用户定义数据类型的状况下，插入到全部列中〕。使用默认值语法CREATEDEFAULTdefault_name

ASconstant_expression参数default_name:默认值的名称。constant_expression:只包含常量值的表达式〔不能包含任何列或其它数据库对象的名称〕。默认值必需与列数据类型兼容。创立默认值下面的例如创立字符默认值unknown。CREATEDEFAULTphonedfltAS”unknown”绑定默认值sp_bindefaultphonedflt,”authors.phone”当authors表的phone列没有输入项时，该默认值才起作用。创立默认值在当前数据库中为列或者用户定义数据类型解除〔删除〕默认值绑定。解除默认值绑定。sp_unbindefault[@objname=]”object_name”

[,[@futureonly=]”futureonly_flag”]删除一个或多个用户定义的默认值DROPDEFAULT{default}[,...n]例：EXECsp_unbindefault”authors.phone”DROPDEFAULTphonedfltIDENTITY〔函数〕：只用在SELECTINTO语句中，以将标识列插入到新表中。该函数在表中创立一个列语法：IDENTITY(data_type[,seed,increment])AScolumn_name使用IDENTITY列选用以下方式中的一种在表中指定该IDENTITY列的名称：(1)SELECTIDENTITY(int,1,1)AScolumn_nameINTONewTableFROMOldTable(2)SELECTcolumn_name=IDENTITY(int,1,1)INTONewTableFROMOldTable下面的例如将来自pubs数据库中employee表的全部行都插入到名为employees的新表。SELECTemp_idASemp_num,fnameASfirst,minitASmiddle,lnameASlast,IDENTITY(smallint,100,1)