《SQLServer数据库应用基础教程》第五章SQLServer的数据完整性

第五章SQLServer的数据完整性1主要内容数据完整性基本概念定义约束约束类型的应用创建约束的其他选项使用默认与规则2数据的一致性和准确性5.1数据完整性基本概念

5.1.1数据完整性的类型

数据完整性实体完整性域完整性引用完整性用户定义完整性3主键实体完整性：要求表中所有的行具有唯一的标志符。即所有记录在某一字段上必须取值唯一实体完整性还可以通过索引、UNIQUE约束或指定IDENTITY

属性来实现4域完整性：是指表中对指定列有效的输入值强制域有效性的方法限制数据类型通过CHECK约束FOREIGNKEY约束CHECK约束DEFAULT定义NOT

NULL定义规定可能值的范围0<=学生成绩<=1005引用完整性：在输入或删除记录时，可以用来保持所有表之间定义的关系，以确保键值在所有表中一致它为基于外键与主键之间或外键与唯一键之间的关系（通过FOREIGN

KEY

和CHECK约束）6表Orders表OrderDetailsOrderID都拥有字段当修改了表Orders中某条记录的OrderID时要同时修改Order

Details中对应记录的OrderID7如下操作被禁止：当主表中没有关联的记录时，将记录添加到相关表中更改主表中的值并导致相关表中的记录孤立从主表中删除记录，但仍存在与该记录匹配的相关记录8用户定义完整性：用户定义的特定业务规则所有的完整性类型都支持用户定义完整性CREATE

TABLE

中的所有列级和表级约束、存储过程和触发器95.1.2强制数据完整性声明数据完整性过程定义数据完整性本章讨论内容10系统将实现数据完整性的要求直接定义在表上或列上。在SQLServer中可以通过使用约束，默认和规则实现声明数据完整性

11在SQLServer中可以通过使用触发器和存储过程来实现过程定义数据完整性

125.2定义约束

约束类型DEFAULT约束(缺省约束)CHECK约束(检查约束)PRIMARYKEY约束(主键约束)FOREIGNKEY约束(外键约束)UNIQUE约束(唯一约束)13使用CREATETABLE创建约束CREATETABLEtable_name(column_namedata_type (NULL|NOTNULL)[[CONSTRAINTconstraint_name]14{PRIMARYKEY[CLUSTERED|NONCLUSTERED]|UNIQUE[CLUSTERED|NONCLUSTERED]|[FOREIGNKEY]REFERENCES ref_table[(ref_column)]|DEFAULTconstant_expression|CHECK(logical_expression)}][,...])15例：在My_DB1数据库中创建表teachers，表中包括教师的：编号、姓名、性别、出身年月、所在系代号、职称、办公电话、科研方向以及工作状态，在创建时定义有列级和表级约束16USEMy_DB1CREATETABLEMy_DB1.dbo.teachers(TeacherID

intNOTNULL,namenvarchar(5) NOTNULL,

gendernchar(1)NULL,birthdaydatetimeNULL,

DeptCode

tinyint NOTNULL,Titlenvarchar(5)NULL,

TelCodechar(8)NOTNULL,aspectnvarchar(200) NULL,

statusnvarchar(5)NOTNULL

17

CONSTRAINTDF_Status DEFAULT(‘在职’),

CONSTRAINTPK_TeacherPRIMARYKEY

CLUSTERED(TeacherID),

CONSTRAINTFK_DeptCode18FOREIGNKEY(DeptCode)

REFERENCESdbo.

departments(DeptCode),

CONSTRAINTCK_TelCode

CHECK(TelcodeLIKE'627 [0-9][0-9][0-9][0-9][0-9]'),)go19此例子包含了所有的约束，但运行结果出现错误提示，这是由于还没有在数据库My_DB1中创建外键约束FK_DeptCode所需表departments20CREATETABLEMy_DB1.

dbo.Departments(DeptCode

tinyint

NOTNULLPrimaryKey,DeptNamenchar(20)NOTNULL,TelCodechar(8)NULL)创建表Department21(3)查看约束的定义信息

使用系统存储过程sp_helpsp_helptextsp_helpconstraint查询系统规划视图check_constraintsreferential_constraintstable_constraints图形化界面查看约束的相关信息22需要查看的约束名查看约束的定义信息可以使用系统存储过程如：EXECsp_help(sp_helptext,sp_helpconstraint)<constraint_name>23例：使用sp_helptext

查看表teachers上约束CK_TelCode的定义文本信息USEMy_DB1EXEC

sp_helptext

CK_TelCodeGo24例：用系统规划视图table_constraints查看pubs表上的所有约束的信息USEpubsSELECT*from

INFORMATION_SCHEMA.

TABLE_CONSTRAINTSgo2526(4)删除约束

用图形化界面删除用系统函数drop删除ALTERTABLEtable_nameDROPCONSTRAINTconstraint_name需删除约束的表名需删除的约束名27USEMy_DB1ALTERTABLEteachersDROPCONSTRAINTDF_statusgo

EXECsp_helpconstraintDF_status

由于已经通过drop语句删除了约束，再用sp_…执行时，就会出错---〉没有约束存在了285.3约束类型的应用(1)DEFAULT约束

创建该约束时，将对表进行完整性验证，表中每一列只能定义一个DEFAULT约束，DEFAULT

约束只在执行INSERT时起作用

29DEFAULT约束

当向数据库表中插入数据时，如果没有明确的提供输入值，SQLServer自动为该列输入指定值ALTERTABLEMy_DB1.dbo.stud_infoADDdormitorynvarchar(20)NULLDEFAULT(‘紫荆公寓8号楼’)30例：恢复表teachers上的DF_status约束，并在列Title上添加一个输入“未知”值的DEFAULT约束31USEMy_DB1ALTERTABLEteachersADDCONSTRAINTDF_statusDEFAULT‘在职’FORstatus，CONSTRAINTDF_TitleDEFAULT‘未知’FORtitlego32(2)CHECK约束

使用该约束可实现当用户在向表中插入或更新数据时，由SQLServer检查新行中的带有该约束的列值使其必须满足约束条件该约束在执行INSERT语句或UPDATE时起作用33

CHECK约束通过逻辑表达式判断限制插入到列中的值。通常记录中的每一列均有值，即使它是NULLCHECK(zipcodeLIKE'[0-9][0-9][0-9][0-9][0-9][0-9]'),34CHECK约束作用域单列：列级CHECK约束多列：表级CHECK约束35例：在表teachers的列Birthday上添加一个CHECK约束，以保证输入的数据大于1900年1月1日并且小于当天的实际日期

列级约束36USEMy_DB1ALTERTABLEteachersADDCONSTRAINTCK_Birthday

CHECK(Birthday>’01/01/1900’ANDBirthday<getdate())go

约束名37继续插入一个不符合约束的记录

INSERTDepartmentsvalues(20,'清华大学计算中心')go38向表中插入如下数据,试试看INSERTteachersvalues(41356,‘黄轩’,‘男’，‘5/1/1851’,20，‘教授’,'62788888',‘规划部’,‘在职’)go39(3)PRIMARYKEY约束

该约束能确保数据的唯一性

若PRIMARYKEY约束定义在多列上，则一列中的值可以重复，但PRIMARYKEY约束定义中的所有列的组合的值必须唯一40

PRIMARY约束

强制表的实体性。不允许数据库表在指定列上具有相同的值，且不允许有空值。author_idtitle_idbookprice100001Prof_1Computer25100002Prof_2Nature35100001Prof_3English28100003Prof_4English31主键41例：在My_DB1中建表Subjects以记录学校学科数据。表中包括一级学科代码(加主键约束)一级学科名称二级学科代码(加主键约束)二级学科名称42

USEMy_DB1CREATETABLEMy_DB1.

dbo.Subjects(PrimaryCode

tinyintNOTNULL,PrimaryNamenvarchar(20)NULL,43SecondaryCode

tinyintNOTNULL,SecondaryNamenvarchar(20)NULL)GO44ALTERTABLESubjectsADDCONSTRAINTPK_SubjectPRIMARYKEYCLUSTERED(PrimaryCode,SecondaryCode)GO45(4)UNIQUE约束

该约束可确保在非主键列中不输入重复值，可以实现在一张表上定义多个UNIQUE约束，且在定义有UNIQUE约束的列上允许有一个空值46

UNIQUE约束

不允许数据库表在指定列上具有相同的值，但允许有空值可以对一个表定义多个UNIQUE约束，但只能定义一个PRIMARYKEY约束UNIQUE约束允许NULL值，这一点与PRIMARYKEY约束不同，当与参与UNIQUE约束的任何值一起使用时，每列只允许一个空值FOREIGNKEY约束可以引用UNIQUE约束47例：在表Departments上的TelCode列上添加一个UNIQUE约束USEMy_DB1ALTERTABLEDepartmentsADDCONSTRAINTU_TelCodeUNIQUE(TelCode)GO

48(5)FOREIGNKEY约束

该约束用于建立两个表一列或多列数据之间的链接的将主键值(一列或多列)添加到另一个表中，可创建两个表之间的链接，这个列就成为第二个表的外键该约束能强制引用完整性49FOREIGNKEY约束

外健用于建立和加强两个表数据之间的链接的一列或多列50被FOREIGN

KEY

参照的列在表中应该具有PRIMARY

KEY约束或UNIQUE

约束

IDNameTelIDsalarytax主键外键51ALTER

TABLEtable_name

ADD

CONSTRAINT

constraint_name

FOREIGN

KEY{(column_name[,...])}

REFERENCESref_table {(column_name[,...])}

[ON

DELETE{CASCADE|NO

ACTION}]

[ON

UPDATE{CASCADE|NO

ACTION}]

52ONDELETENOACTIONCASCADE若要删除某行，但该行某列被其它表引用，则产生错误并回滚DELETE

若要删除某行，而该行某列被其它表引用，则也将删除所有包含那些外键的行，若含有级联引用，则全部删除53ONUPDATENOACTIONCASCADE若要更新某行中的键值，而该行被其他所引用，则产生错误并回滚若要更新某行中的键值，而该行被其它表引用，则所有外键值也被更新。若有级联引用，则全部更新545.4创建约束的其他选项

创建约束可能会遇到的问题

在已存有大量记录的表上建约束

在建有约束的表中一次性插入大量数据

55若能确保即将添加的数据不会违背约束规定的规则，那么这种数据检验将是一种不必要的开销，为此在创建约束时可以WITHNOCHECK和NOCHECK选项56(1)WITHNOCHECK选项能用WITHNOCHECK选项禁止约束检查的只有CHCEK和FOREIGNKEY约束例：假设表Departments中已有大量记录，现需在TelCode列上添加CHECK约束，以保证所有电话号码都以6278开头57USEMy_DB1ALTERTABLEDepartments

WITHNOCHECKADDCONSTRAINT

CK_Dept_TelCode

CHECK(Telcodelike'6278

[0-9][0-9][0-9][0-9]')go58(2)使用NOCHECK选项

当向定义有约束的表中插入新记录或修改记录时，用户可使用NOCHECK选项来禁止对新数据的约束检查59例：在表teachers中设FK_DeptCode的NOCHECK选项，使得SQLServer对即将插入的大量数据不作FK_DeptCode的约束检查

USEMy_DB1ALTER

TABLEteachersNOCHECK

CONSTRAINT

FK_DeptCodego605.5使用默认与规则

类似DEFAULT约束,使用默认也可实现当用户在向表中插入数据时，若未明确给出某列的值，则由SQLServer自动为该列输入缺省值61默认与DEFAULT约束不同默认是一种数据库对象在数据库中只需定义一次可被一次或多次应用于任意表中的一列或多列和用户定义的数据类型62创建默认的命令如下：

CREATE

DEFAULTdefault_name

AS

constant_expression

指定默认的常数值

63在创建默认后还要执行系统存储过程sp_binddefault将其绑定于列或用户自定义的数据类型上，从而将默认用于任意表的一列或多列以及用户自定义的数据类型，执行默认绑定的命令如下：EXEC

sp_bindefault

default_name,‘table_name.[column_name[,...]|user_datetype]’

64例：在数据库My_DB1上创建默认DeptCode_default，并将其绑定在表Departments中的DeptCode列上，从而实现默认电话为6278000165USEMy_DB1goCREATE

DEFAULT

DeptCode_defaultAS‘62780001’goEXEC

sp_bindefault

DeptCode_default,‘Departments.DeptCode’go66若删除默认，执行sp_unbindefault

EXEC

sp_unbindefault‘table_name.[column_name[,...]|user_datetype]’

67使用规则

规则是实现声明数据完整性的一种方法，它也是一种数据库对象，因此和默认一样在数据库中只需定义一次68

规则可以指定插入表上的有效值，以确保数据在指定的取值范围内，并与特定的模式或特定表中的实体匹配69CREATErulerule_name

AS