第五章SQL Server的数据完整性主要内容数据完整性基本概念 定义约束约束类型的应用创建约束的其他选项使用默认与规则数据的一致性和准确性
5.1 数据完整性基本概念 5.1.1 数据完整性的类型 主键实体完整性：要求表中所有的行具有唯一的标志符。即所有记录在某一字段
上必须取值唯一实体完整性还可以通过索引、UNIQUE 约束或 指定IDENTITY 属性来实现 域完整性：是指表中对指定列有效的输
入值强制域有效性的方法限制数据类型通过CHECK约束FOREIGN KEY约束CHECK约束DEFAULT定义NOT NULL定义
规定可能值的范围0<=学生成绩<=100引用完整性：在输入或删除记录时，可以用来保持所有表之间定义的关系，以确保键值在所有表中一致
它为基于外键与主键之间或外键与唯一键之间的关系（通过FOREIGN KEY 和CHECK约束）表Orders表Order Deta
ilsOrderID都拥有字段当修改了表Orders中某条记录的OrderID时要同时修改Order Details中对应记录的O
rderID如下操作被禁止：当主表中没有关联的记录时，将记录添加到相关表中更改主表中的值并导致相关表中的记录孤立从主表中删除记录，
但仍存在与该记录匹配的相关记录用户定义完整性：用户定义的特定业务规则所有的完整性类型都支持用户定义完整性CREATE TABLE
中的所有列级和表级约束、存储过程和触发器5.1.2 强制数据完整性 声明数据完整性 过程定义数据完整性 本章讨论内容系统将实现数据
完整性的要求直接定义在表上或列上。在SQL Server中可以通过使用约束，默认和规则实现声明数据完整性 在SQL Server中
可以通过使用触发器和存储过程来实现过程定义数据完整性 5.2 定义约束 约束类型 DEFAULT约束(缺省约束) CHECK约束(
检查约束) PRIMARY KEY约束(主键约束) FOREIGN KEY约束(外键约束) UNIQUE约束(唯一约束) 使用CR
EATE TABLE创建约束CREATE TABLE table_name(column_name data_type (N
ULL|NOT NULL)[[CONSTRAINT constraint_name]{PRIMARY KEY [CLUSTERED
|NONCLUSTERED]|UNIQUE [CLUSTERED|NONCLUSTERED]|[FOREIGN KEY] REFE
RENCES ref_table [(ref_column)]|DEFAULT constant_expression|CH
ECK (logical_expression)}][,...])例：在My_DB1数据库中创建表teachers，表中包括教师的
：编号、姓名、性别、出身年月、所在系代号、职称、办公电话、科研方向以及工作状态，在创建时定义有列级和表级约束 USE My_DB1
CREATE TABLE My_DB1.dbo.teachers (TeacherID int NOT NULL, name nv
archar(5) NOT NULL, gender nchar(1) NULL, birthday datetime NULL,
DeptCode tinyint NOT NULL, Title nvarchar(5) NULL, TelCode char
(8) NOT NULL, aspect nvarchar(200) NULL, status nvarchar(5) NOT N
ULL CONSTRAINT DF_Status DEFAULT(‘在职’), CONSTRAINT PK_Teacher
PRIMARY KEY CLUSTERED(TeacherID), CONSTRAINT FK_DeptCodeFOREI
GN KEY (DeptCode) REFERENCES dbo. departments(DeptCode), CONST
RAINT CK_TelCode CHECK(Telcode LIKE''627 [0-9][0-9][0-9] [0-9]
[0-9]''), )go此例子包含了所有的约束，但运行结果出现错误提示，这是由于还没有在数据库My_DB1中创建外键约束FK_De
ptCode所需表departmentsCREATE TABLE My_DB1. dbo.Departments(DeptCo
de tinyint NOT NULL Primary Key,DeptName nchar(20) NOT NULL,Tel
Code char(8) NULL) 创建表Department(3) 查看约束的定义信息 需要查看的约束名查看约束的定义信息 可
以使用系统存储过程如：EXEC sp_help (sp_helptext,sp_helpconstraint) nt_name>例：使用sp_helptext 查看表teachers上约束CK_TelCode的定义文本信息USE My_DB1
EXEC sp_helptext CK_TelCodeGo例：用系统规划视图table _constraints查看pubs表上的
所有约束的信息USE pubsSELECT from INFORMATION_SCHEMA. TABLE_CONSTR
AINTSgo (4)删除约束 用图形化界面删除 用系统函数drop删除 ALTER TABLE table_nameDROP C
ONSTRAINT constraint_name需删除约束的表名需删除的约束名USE My_DB1ALTER TABLE tea
chersDROP CONSTRAINT DF_statusgo EXEC sp_helpconstraint DF_status
由于已经通过drop语句删除了约束，再用sp_…执行时，就会出错---〉没有约束存在了5.3 约束类型的应用(1) DEFAUL
T约束 创建该约束时，将对表进行完整性验证，表中每一列只能定义一个DEFAULT约束，DEFAULT 约束只在执行INSERT时起
作用 DEFAULT约束 当向数据库表中插入数据时，如果没有明确的提供输入值，SQL Server自动为该列输入指定值ALTE
R TABLE My_DB1.dbo.stud_infoADD dormitory nvarchar(20) NULL DEFAU
LT(‘紫荆公寓8号楼’)例：恢复表teachers上的DF_status约束，并在列Title上添加一个输入“未知”值的DEFA
ULT约束USE My_DB1ALTER TABLE teachersADD CONSTRAINT DF_status DEFAU
LT ‘在职’ FOR status，CONSTRAINT DF_Title DEFAULT ‘未知’ FOR titlego(
2) CHECK约束 使用该约束可实现当用户在向表中插入或更新数据时，由SQL Server检查新行中的带有该约束的列值使其必须满
足约束条件该约束在执行INSERT语句或UPDATE时起作用 CHECK约束 通过逻辑表达式判断限制插入到列中的值。通常记
录中的每一列均有值，即使它是NULLCHECK(zipcode LIKE''[0-9][0-9][0-9][0-9][0-9][0-
9]''),CHECK约束作用域单列：列级CHECK约束多列：表级CHECK约束例：在表teachers的列Birthday上添加一
个CHECK约束，以保证输入的数据大于1900年1月1日并且小于当天的实际日期 列级约束USE My_DB1ALTER TABLE
teachersADD CONSTRAINT CK_Birthday CHECK(Birthday>’01/01/1900’
AND Birthday ues(20,''清华大学计算中心'')go向表中插入如下数据,试试看INSERT teachersvalues(41356,‘黄轩’
, ‘男’， ‘5/1/1851’,20，‘教授’, ''62788888'',‘规划部’,‘在职’)go(3) PRIM
ARY KEY约束 该约束能确保数据的唯一性 若PRIMARY KEY约束定义在多列上，则一列中的值可以重复，但PRIMAR
Y KEY约束定义中的所有列的组合的值必须唯一 PRIMARY约束 强制表的实体性。不允许数据库表在指定列上具有相同的
值，且不允许有空值。例：在My_DB1中建表Subjects以记录学校学科数据。表中包括一级学科代码(加主键约束)一级学科名称二级
学科代码(加主键约束)二级学科名称 USE My_DB1CREATE TABLE My_DB1. dbo.Subjects(
PrimaryCode tinyint NOT NULL,PrimaryName nvarchar(20)
NULL,SecondaryCode tinyint NOT NULL,SecondaryName nvarch
ar(20) NULL)GOALTER TABLE SubjectsADD CONSTRAINT PK_Subject
PRIMARY KEY CLUSTERED(PrimaryCode, SecondaryCode)GO(4) UNIQUE约束 该
约束可确保在非主键列中不输入重复值，可以实现在一张表上定义多个UNIQUE约束，且在定义有UNIQUE约束的列上允许有一个空值
UNIQUE约束 不允许数据库表在指定列上具有相同的值，但允许有空值可以对一个表定义多个 UNIQUE 约束，但只能定义一个
PRIMARY KEY 约束UNIQUE 约束允许 NULL 值，这一点与 PRIMARY KEY 约束不同，当与参与 UNIQ
UE 约束的任何值一起使用时，每列只允许一个空值 FOREIGN KEY 约束可以引用 UNIQUE 约束例：在表Departme
nts上的TelCode列上添加一个UNIQUE约束USE My_DB1ALTER TABLE DepartmentsADD CO
NSTRAINT U_TelCode UNIQUE (TelCode)GO (5)FOREIGN KEY约束
该约束用于建立两个表一列或多列数据之间的链接的将主键值(一列或多列)添加到另一个表中，可创建两个表之间的链接，这个列就成为第二个表
的外键该约束能强制引用完整性FOREIGN KEY约束 外健用于建立和加强两个表数据之间的链接的一列或多列被FOREIGN K
EY 参照的列在表中应该具有PRIMARY KEY约束或 UNIQUE 约束 主键外键ALTER TABLE table_name
ADD CONSTRAINT constraint_name FOREIGN KEY{(column_name[,...])
} REFERENCES ref_table {(column_name[,...])} [ON DELETE{CASCAD
E|NO ACTION}] [ON UPDATE{CASCADE|NO ACTION}] ON DELETENO ACTION C
ASCADE 若要删除某行，但该行某列被其它表引用，则产生错误并回滚 DELETE 若要删除某行，而该行某列被其它表引用，则也将删
除所有包含那些外键的行，若含有级联引用，则全部删除ON UPDATE NO ACTION CASCADE 若要更新某行中的键值，而
该行被其他所引用，则产生错误并回滚 若要更新某行中的键值，而该行被其它表引用，则所有外键值也被更新。若有级联引用，则全部更新5.4
创建约束的其他选项 创建约束可能会遇到的问题 在已存有大量记录的表上建约束 在建有约束的表中一次性插入大量数据 若能确保即将
添加的数据不会违背约束规定的规则，那么这种数据检验将是一种不必要的开销，为此在创建约束时可以WITH NOCHECK和NOCHEC
K选项 (1)WITH NOCHECK选项 能用WITH NOCHECK选项禁止约束检查的只有CHCEK和FOREIGN K
EY约束 例：假设表Departments中已有大量记录，现需在TelCode列上添加CHECK约束，以保证所有电话号码都以627
8开头USE My_DB1ALTER TABLE Departments WITH NOCH
ECKADD CONSTRAINT CK_Dept_TelCode CHECK (Telcode like ''6278
[0-9][0-9][0-9][0-9]'')go (2)使用NOCHECK选项 当向定义有约束的表中插入新记录或修改记录时，用户
可使用NOCHECK选项来禁止对新数据的约束检查 例：在表teachers中设FK_DeptCode的NOCHECK选项，使得SQ
L Server对即将插入的大量数据不作FK_DeptCode的约束检查?USE My_DB1ALTER TABLE teache
rs NOCHECK CONSTRAINT FK_DeptCodego5.5 使用默认与规则 类似DEFAULT约束,使用默认
也可实现当用户在向表中插入数据时，若未明确给出某列的值，则由SQL Server自动为该列输入缺省值默认创建默认的命令如下：?CR
EATE DEFAULT default_name AS constant_expression 指定默认的常数值 在创建默认
后还要执行系统存储过程sp_binddefault将其绑定于列或用户自定义的数据类型上，从而将默认用于任意表的一列或多列以及用户自
定义的数据类型，执行默认绑定的命令如下：EXEC sp_bindefault default_name , ‘table_name
.[column_name[,...]|user_datetype]’ 例：在数据库My_DB1上创建默认DeptCode_def
ault，并将其绑定在表Departments中的DeptCode列上，从而实现默认电话为62780001USE My_DB1go
CREATE DEFAULT DeptCode_defaultAS ‘62780001’goEXEC sp_bindefaul
t DeptCode_default, ‘Departments.DeptCode’go若删除默认，执行sp_unbindefau
lt EXEC sp_unbindefault ‘table_name.[column_name[,...]|user_datet
ype]’ 使用规则 规则是实现声明数据完整性的一种方法， 它也是一种数据库对象，因此和默认一样在数据库中只需定义一次
规则可以指定插入表上的有效值，以确保数据在指定的取值范围内，并与特定的模式或特定表中的实体匹配CREATE rule r
ule_name AS condition_expression 规则创建后，要执行sp_bindrule将其绑定于列或用户自定
义的数据类型上：? EXEC sp_bindrule rule_name , ‘table_name.[column_name
[,...]|user_datetype]’ 可以是WHERE子句中的表达式可以包含算术运算符、关系运算符和谓词(如IN/LIKE
/BETWEEN)之类的元素不能引用列或其它数据库对象规则可以包含不引用数据库对象的内置函数condition_expressio
n 包含一个局部变量。每个局部变量的前面都有一个 @ 符号。该表达式引用通过 UPDATE 或 INSERT 语句输入的值。在创建规则时，可以使用任何名称或符号表示值，但第一个字符必须是 @ 符号 例：在数据库My_DB1上创建规则Code_rule，并将其绑定在表stud_info中的stud_id列上，从而使学生标识号stud_id为1至50的自然数（包含1和50） USE My_DB1goCREATE RULE Code_ruleAS @stud_id>=1 AND @stud_id<=50goEXEC sp_bindrule Code_rule, ‘stud_info.stud_id’go 通过图形化界面来查询数据库上规则的有关信息浏览规则的定义文本删除规则, 可以通过图形化界面或使用DROP RULE语句实现 5.6 数据完整性强制方法的选择 约束:定义表中有效的数据，在事务处理前执行，性能较好默认和规则:提供独立的可以与多个表联系的对象实现数据完整性，在事务处理前执行对比数据类型，空/非空约束:提供最低级别的数据完整性。在表创建时实现，在事务处理开始前执行 |
|
