数据库-第05章-实现数据完整性

第5章实现数据完整性数据完整性的类型约束禁用约束默认值和规则决定使用何种强制方法推荐操作数据完整性的类型域完整性

(列)实体完整性

（行）参照完整性

(表之间)数据完整性的类型4.1数据完整性的类型数据完整性指的是数据库中存储的数据的一致性和准确性数据完整性的类型：域完整性、实体完整性、引用完整性域完整性域（或列）完整性是指对列指定一组有效的值并决定是否可为空值实体完整性实体（或表）完整性要求表中的所有行都有一个惟一的标识符，称为主键值引用完整性引用完整性确保主键（在被引用表中）和外键（在引用表中）之间的关系得到保持第5章实现数据完整性数据完整性的类型约束禁用约束默认值和规则决定使用何种强制方法推荐操作决定使用何种约束约束的类型约束4.2约束决定使用何种约束4.2.1决定使用何种约束完整性类型约束类型描述域DEFAULT如果在INSERT语句中未显式提供

值，则指定为列提供的值CHECK指定列中可接受的数据值REFERENTIAL(通常使用外键)基于其他表中的列的值，指定可接受的用于更新的数据值实体PRIMARYKEY惟一标识每一列，确保用户没有输入重复的值。同时创建一个索引以增强性能。不允许空值UNIQUE确保在非主键列中不输入重复值，并创建一个索引以增强性能。允许空值引用FOREIGNKEY定义一列或多列的值与同表或其他表中主键的值匹配CHECK基于同表中其他列的值，指定列中可接受的数据值创建约束使用CREATETABLE或者ALTERTABLECREATETABLE是在创建表时创建约束ALTERTABLE是在一个已有的表上创建约束可以添加约束到已有数据的表上可添加单列或多列约束若约束应用于单列，称为列级约束若约束引用了多列，称为表级约束，即使它并没有引用表中的所有列4.3.2创建约束使用约束的注意事项可直接在表上创建、更改和删除约束，而不必删除并重建表应该在应用程序和事务内建立错误检查逻辑，以测试是否违反了某个约束SQLServer里的约束只是“最后防线”当给一个表添加约束的时候，SQLServer将检查现有数据是否违反约束建议创建约束的时候指定名称，否则系统将为约束自动产生一个复杂的名称名称必须惟一，且符合SQLServer标识符的规则查看约束的信息系统存储过程：sp_helpconstraint、sp_help信息模式视图：check_constraints、referential_constraints、table_constraints系统表：syscomments、sysreferences、sysconstraints约束的类型DEFAULT约束CHECK约束PRIMARYKEY约束UNIQUE约束FOREIGNKEY约束级联引用完整性4.2.2约束的类型PRIMARYKEY约束PRIMARYKEY约束在表中定义了一个惟一标识每一列的主键

语法：[CONSTRAINT约束名]PRIMARYKEY[CLUSTERED|NONCLUSTERED]{(列[,...n])}4.2.2.3PRIMARYKEY约束

PRIMARYKEY约束利用表中的一列或多列数据唯一地标识某一行数据每个表只有一个PRIMARYKEY约束PRIMARYKEY约束的值必须是唯一的不允许有空值SQLServer中最多可定义16列作为主键PRIMARYKEY约束直接在列名后增加关键字

PRIMARYKEY

CREATETABLEStudent (snochar(5)PRIMARYKEY,

sname

varchar(20)notnull,

……

);

列级主键创建sc表，其主码为（sno,cno）×???

CREATETABLEsc(snochar(5)PRIMARYKEY，cnochar(1)PRIMARYKEY，gradedecimal(4,1)));PRIMARYKEY约束在CREATETABLE语句各列定义的最后加:PRIMARYKEY(<属性名表>)CREATETABLEsc(snochar(5)，

cnochar(1)，gradedecimal(4,1),PRIMARYKEY(sno,cno));

CREATETABLEsc1(……….,

constraintPK_SCPRIMARYKEY(sno,cno));表级主键约束名PRIMARYKEY约束删除表上已定义的主键

ALTERTABLESC1

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

ALTERTABLESC

ADDCONSTRAINTPK_SCprimarykey(sno,cno);删除和添加主键DEFAULT约束如果一个列的值在INSERT语句中没有指定，DEFAULT约束将自动输入一个值，可以是预先指定的常量、NULL或者一个系统函数运行时的值

语法：

[CONSTRAINT约束名]DEFAULT约束表达式

创建DEFAULT约束的两种方法创建一个默认对象（CREATEDEFAULT），然后使用存储过程sp_bindefault将默认绑定到一个列CREATETABLE或ALTERTABLE时使用DEFAUTL约束4.2.2.1DEFAULT约束DEFAULT约束（续）在创建表时使用default属性4.2.2.1DEFAULT约束CREATETABLEuserInfo(...…countryvarchar(50)notnullDEFAULT‘China’,……)为已经创建好的表添加default属性USENorthwind

ALTERTABLEdbo.Customers

ADD

CONSTRAINTDF_contactnameDEFAULT'UNKNOWN'

FORContactName应用DEFAULT约束的几种情况DEFAULT约束（续）应用DEFAULT约束的注意事项允许使用常量、函数、系统提供的值：USER、CURRENT_USER、SESSION_USER、SYSTEM_USER及CURRENT_TIMESTAMP

例如：①DEFAULTUSER②DEFAULE(getdate())有可能会和CHECK约束冲突常量值外面可以加或者不加括号，字符或者日期常量必须加上单引号或双引号4.2.2.1DEFAULT约束忽略，

不显式地插入值插入NULL插入DEFAULT无默认值有默认值无默认值有默认值无默认值有默认值NULLNULL默认值NULLNULLNULL默认值NOTNULL错误默认值错误错误错误默认值CHECK约束限制输入到指定列的值只能为某些特定值

语法：[CONSTRAINT约束名]CHECK(逻辑表达式)

两种强制域完整性的方法：CHECK约束和规则CHECK约束定义了一个表达式，若数据修改语句使得表达式值为FALSE的话，将拒绝语句执行规则的功能和CHECK约束基本相同，除了语法不同，能力稍弱。规则可定义一次，然后对多个列分别绑定；而CHECK约束则需要对每个列定义。但CHECK约束的功能略强一些（例如引用同行中其他列的值和调用系统函数等）4.2.2.2CHECK约束CHECK约束（续）应用CHECK约束的几种情况4.2.2.2CHECK约束USENorthwind

ALTERTABLEdbo.Employees

ADD

CONSTRAINTCK_birthdate

CHECK(BirthDate>'01-01-1900'ANDBirthDate<getdate())在现有表中创建CHECK约束CREATETABLEuserInfo(...…birthdayvarchar(50)notnullCONSTRAINTck_birthdayCHECK(BirthDate>'01-01-1900'ANDBirthDate<getdate()),)在创建表时使用CHECK约束在创建SC表时定义CHECK约束

CreateTableSC (snochar(5)notnull,

cnochar(1)notnull, gradedecimal(4,1) primarykey(sno,cno));

创建CHECK约束列级CHECK约束check(grade>=0andgrade<=100),CreateTableSC (snochar(5)notnull,

cnochar(1)notnull, gradedecimal(4,1),primarykey(sno,cno),check(grade>=0andgrade<=100));CreateTableSC(……..,

constraintchk_gmkcheck(grade>=0andgrade<=100));

创建CHECK约束表级CHECK约束CreateTableStudent (snochar(5)notnull,

birthdate

datetimenotnull

schooldate

datetimenotnull,primarykey(sno),

);

创建CHECK约束表级CHECK约束check(birthdate<getdate()),check(birthdate<getdate()),列级CHECK约束删除SC表中的CHECK约束ALTERTABLESC

DROPCONSTRAINT

chk_gmk向SC表中添加CHECK约束ALTERTABLESC

ADDCONSTRAINT

chk_gmk

check(gmark>=0andgmark<=100)

添加和删除CHECK约束CHECK约束（续）应用CHECK约束的注意事项一个表可以定义多个CHECK约束，一个列上只允许创建一个列级CHECK约束。列级CHECK约束只能引用被约束的列，表级CHECK约束只能引用同一表中的列。当列上存在规则和一个或多个CHECK约束时，将验证所有限制。4.2.2.2CHECK约束UNIQUE约束UNIQUE约束指明列中的任意两行不能有相同的值

语法：[CONSTRAINT约束名]UNIQUE[CLUSTERED|NONCLUSTERED]{(列[,...n])}

应用UNIQUE约束的注意事项允许空值的列上定义UNIQUE约束在一个表上允许多个UNIQUE约束可在一个或者多个列上定义是通过一个惟一索引强制约束的4.2.2..4UNIQUE约束UNIQUE约束（续）PRIMARYKEY和UNIQUE约束声明PRIMARYKEY或UNIQUE约束的结果只是自动创建了一个指定列上的惟一索引，通过惟一索引来确保值的惟一性可空性PRIMARYKEY的各个列必须声明为NOTNULL，而UNIQUE的各个列可以声明为允许NULL值在惟一索引中，认为所有的NULL值是相等的索引的属性PRIMARYKEY约束所创建的惟一索引默认为CLUSTERED，除非表中另外一列已经声明为CLUSTEREDUNIQUE约束所创建的惟一索引默认为NONCLUSTERED选择键保持键的长度尽可能短，必要时可另外创建一个代替键不要使用float或real数据类型的列作为主键在创建表时定义UNIQUE约束CREATETABLEtest2

(even_idintprimarykey,even_namechar(20),even_typechar(20),even_timedatetime,

)

CONSTRAINTUNIQ_EVENUNIQUE(even_type,even_time)要求：在even_type和even_time上共同建立惟一约束表级UNIQUE约束创建UNIQUE约束向test1表中的tname

列添加UNIQUE约束

ALTERTABLEtest1

ADDCONSTRAINTUNIQ_TNAME

UNIQUE(tname)删除表上已定义的UNIQUE约束ALTERTABLEtest1

DROPCONSTRAINTUNIQ_TNAME

添加和删除UNIQUE约束FOREIGNKEY约束FOREIGNKEY约束：定义到同表或其他表中具有PRIMARYKEY或者UNIQUE约束的列的引用

语法： [CONSTRAINT约束名][FOREIGNKEY][(列[,…n])]

REFERENCES引用表[(引用列[,…n])]具有FOREIGNKEY约束的列的取值范围只能是被引用的列的列值4.2.2.5FOREIGNKEY约束FOREIGNKEY约束（续）应用FOREIGNKEY约束的注意事项FOREIGNKEY子句中指定的列的个数和数据类型必须和REFERENCES子句中指定的列的个数和数据类型匹配并不自动创建索引修改数据的时候，用户必须在被FOREIGNKEY约束引用的表上具有SELECT或REFERENCES权限若引用的是同表中的列，那么可只用REFERENCES子句而省略FOREIGNKEY子句4.2.2.5FOREIGNKEY约束FOREIGNKEY约束（续）应用FOREIGNKEY约束的几种情况：创建表的同时创建FOREIGNKEY约束

createtablestuscore(idintnotnull,courseIDintnotnull,scoreintnotnull,constraintfk_student_stuscoreforeignkey(id)referencesstudent(id))FOREIGNKEY约束（续）创建完表后通过修改表增加FOREIGNKEY约束使用FOREIGNKEY约束，确保Orders表中的客户标识与Customers表中的有效的客户标识相关联4.2.2.5FOREIGNKEY约束altertablestuscoreaddconstraintfk_student_stuscoreforeignkey(id)referencesstudent(id)FOREIGNKEY约束（续）FOREIGNKEY约束的删除altertablestuscoredropconstraintfk_student_stuscore级联引用完整性4.2.2.6级联引用完整性FOREIGNKEY约束包含一个CASCADE选项，允许对一个定义了UNIQUE或者PRIMARYKEY约束的列的值的修改自动传播到引用它的外键上，这个动作称为级联引用完整性

语法： [CONSTRAINT约束名][FOREIGNKEY][(列[,…n])]

REFERENCES引用表[(引用列[,…n])]

[ONDELETE{CASCADE|NOACTION}]

[ONUPDATE{CASCADE|NOACTION}]NOACTION：任何企图删除或者更新被其他表的外键所引用的键都将引发一个错误，对数据的改变会被回滚。NOACTION是默认值CASCADE：若父表中的行变化了，则引用表中相应的行也自动变化级联引用完整性（续）4.2.2.6级联引用完整性应用CASCADE选项的注意事项可在多个具有引用关系的表之间组合CASCADE和NOACTION选项。若SQLServer遇到NOACTION，则中断并回滚所有相关的CASCADE动作CASCADE选项不能对定义为rowversion数据类型的外键或主键列指定第5章实现数据完整性数据完整性的类型约束禁用约束默认值和规则决定使用何种强制方法推荐操作禁用约束禁用现有数据上的约束检查在加载新数据时禁用约束检查4.3禁用约束禁用现有数据上的约束检查当在表上添加约束的时候，我们可以禁用对已有数据的约束检查

语法：ALTERTABLE表名

[WITHCHECK

WITHNOCHECK]ADDCONSTRAINT约束名

[FOREIGNKEY][(column[,…n])]REFERENCES引用表[(引用列[,…n])][CHECK(搜索条件)]

4.3.1禁用现有数据上的约束检查禁用现有数据上的约束检查（续）对已有数据禁用约束检查的注意事项只能禁用CHECK和FOREIGNKEY约束当为一个已有数据的表添加CHECK或FOREIGNKEY约束的时候，使用WITHNOCHECK选项来禁用对已有数据的约束检查4.3.1禁用现有数据上的约束检查在加载新数据时禁用约束检查为了避免约束检查的开销，有时候可能希望禁用约束已经确保数据符合约束数据并不符合约束，但稍后可以更改其值并重用约束语法：ALTERTABLE表名{CHECK|NOCHECK}CONSTRAINT{ALL|约束名[,...n]}查看约束是启用还是禁用的状态系统存储过程sp_help系统函数OBJECTPROPERTY的CnstIsDisabled属性4.3.2在加载新数据时禁用约束检查第5章实现数据完整性数据完整性的类型约束禁用约束默认值和规则决定使用何种强制方法推荐操作使用默认值和规则语法：创建：CREATEDEFAULT

默认值

AS常量表达式 删除：DROPDEFAULT

默认值[,...n] 绑定一个默认：sp_bindefault 解除默认值绑定：

sp_unbindefault创建默认值的注意事项列的默认值必须符合此列上的任何规则或CHECK约束4.4使用默认值和规则使用默认和规则（续）规则：规则指定了能插入列的可接受的值。它确保数据在指定值域内，匹配某个模式，或者匹配指定列表中的项。

语法：创建：CREATERULE

规则AS

条件表达式 删除：DROPRULE

规则[,...n] 绑定规