第8章xcz数据完整性.ppt

1、第8章 数据完整性,数据库中的数据是从外界输入的，而数据的输入由于种种原因，会发生输入无效或信息错误。怎样保证输入的数据符合规定，并成为数据库系统，尤其是多用户数据库系统成为关注的问题，因此而提出数据完整性问题。,本章内容,8.1 数据完整性概述 8.2 使用规则实施数据完整性 8.3 使用默认值实施数据完整性 8.4 使用约束实施数据完整性,8.1 数据完整性概述,数据完整性防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。 数据完整性有4种类型： 实体完整性(Entity Integrity)、 域完整性(Domain Integrity)、 参

2、照完整性(Referential Integrity)、 用户定义的完整性(User-defined Integrity)。 在SQL Server中可以通过各种规则(Rule)、默认(Default)、约束(Constraint)和触发器(Trigger)等数据库对象来保证数据的完整性。,8.2 使用规则实施数据完整性,8.2.1 创建规则 8.2.2 查看和修改规则 8.2.3 规则的绑定与松绑 8.2.4 删除规则,8.2.1 创建规则,规则(Rule)就是数据库中对存储在表的列或用户定义数据类型中的值的规定和限制。 规则是单独存储的独立的数据库对象。 规则和约束可以同时使用，表的列可以

3、有一个规则及多个约束。规则与检查约束在功能上相似，但在使用上有所区别。 检查约束是在CREATE TABLE或ALTER TABLE语句中定义的，嵌入了被定义的表结构，即删除表的时候检查约束也就随之被删除。 而规则需要用CREATE RULE语句定义后才能使用，是独立于表之外的数据库对象，删除表并不能删除规则，需要用DROP RULE语句才能删除。 相比之下，使用在CREATE TABLE或ALTER TABLE语句中定义的检查约束是更标准的限制列值的方法，但检查约束不能直接作用于用户定义数据类型。,8.2 使用规则实施数据完整性,1用企业管理器创建规则,8.2.1 创建规则,在企业管理器中选

4、择数据库对象“规则”，单击右键从快捷菜单中选择“新建规则”选项，即会弹出如图所示的“规则属性”对话框。 输入规则名称和表达式之后，单击“确定”按钮，即完成规则的创建。,2用CREATE RULE语句创建规则,8.2.1 创建规则,CREATE RULE语句用于在当前数据库中创建规则，其语法格式如下： CREATE RULE rule_name AS condition_expression,8.2.1 创建规则,CREATE RULE hire_date_rule AS hire_date=1980-01-01 and hire_date=getdate(),例8-1 创建雇佣日期规则 hir

5、e_date_rule。,例8-2 创建性别规则sex_rule。 CREATE RULE sex_rule AS sex in (男,女),可通过企业管理器来查看规则,8.2.1 创建规则,CREATE RULE grade_rule AS value between 1 and 100 例8-4 创建字符规则my_character_rule。 Create rule my_character_rule As value like a-z%0-9,例8-3 创建评分规则grade_rule。,1用企业管理器查看和修改规则 在企业管理器的数据库对象中选择“规则”对象，即可从右边的任务板中看到

6、规则的大部分信息，包括规则的名称、所有者、创建时间等。,8.2.2 查看和修改规则,8.2.2 查看和修改规则,使用sp_helptext 系统存储过程可以查看规则的文本信息。 例8-5 查看规则hire_date_rule的文本信息 EXECUTE sp_helptext hire_date_rule 运行结果如图所示,2用系统存储过程sp_helptext 查看规则,8.2.3 规则的绑定与松绑,需要将规则与数据库表或用户定义对象联系起来，才能发生作用。联系的方法称为绑定，所谓绑定就是指定规则作用于哪个表的哪一列或哪个用户定义数据类型。 表的一列或一个用户定义数据类型只能与一个规则相绑定，

7、而一个规则可以绑定多对象，这正是规则的魅力所在。 解除规则与对象的绑定称为松绑。,8.2 使用规则实施数据完整性,8.2.3 规则的绑定与松绑,在企业管理器中，展开数据库（Sales）文件夹，鼠标单击“规则”选项，在右窗格中选择要进行绑定的规则（hire_date），单击鼠标右键，从快捷菜单中选择“属性” 菜单项，打开“规则属性”对话框，如图8-4所示。 图中的“绑定UDT(U)”按钮用于绑定规则到用户定义的数据类型，“绑定列(B)”按钮用于绑定规则到表的列。,1用企业管理器管理规则的绑定和松绑,8.2.3 规则的绑定与松绑,在图8-4中单击“绑定UDT(U)”按钮，则出现“将绑定规则到用户定

8、义的数据类型”对话框，如图8-5所示；,8.2.3 规则的绑定与松绑,单击“绑定列(B)”按钮，则出现如图8-6所示的“将绑定规则到列”对话框。 在“将规则绑定列”对话框的左边“未绑定的列”列表框中选择一列“添加”到右边“绑定列”列表框中，就实现规则绑定了。 同样，去掉“将规则绑定到用户定义的数据类型”对话框的列表框的“绑定”列下的标识或删除“将规则绑定列”对话框的右边“绑定列”列表框的列，就实现了规则的松绑操作。,8.2.3 规则的绑定与松绑,2用系统存储过程sp_bindrule绑定规则,系统存储过程sp_bindrule 可以绑定一个规则到表的一个列或一个用户定义数据类型上。其语法格式如

9、下： sp_bindrule rulename = rule, objname = object_name , futureonly= futureonly ,sp_bindrule rulename = rule：指定规则名称 objname = object_name：指定绑定对象，可以是表列或用户定义的数据类型， 如果是表列，则采用table.column书写，否则认为是用户数据类型 , futureonly= futureonly ：仅在绑定规则到用户定义数据类型上时使用，当指定此选项时，仅以后使用此用户数据类型的列会应用新规则，而当前已经使用的数据类型的列则不受影响。,8.2.3 规

10、则的绑定与松绑,EXEC sp_bindrule hire_date_rule, employee.hire_date,例8-6 将例8-1创建的规则hire_date_rule绑定到employee表的hire_date列上。,例8-7 定义用户定义数据类型pat_char，将例8-4创建的规则my_character_rule绑定到pat_var上。 EXEC sp_addtype pat_char,varchar(10),NOT NULL GO EXEC sp_bindrule my_character_rule, pat_char, futureonly 运行结果如下：,8.2.3 规

11、则的绑定与松绑,EXEC sp_bindrule sex_rule, employee.sex 运行结果如下： 已将规则绑定到表的列上。,例8-8 绑定例8-2创建的规则sex_rule 到employee 表的字段sex。,8.2.3 规则的绑定与松绑,系统存储过程sp_unbindrule可解除规则与列或用户定义数据类型的绑定，其语法格式如下： sp_unbindrule objname = object_name , futureonly = futureonly 注：futureonly选项指定现有的由此用户定义数据类型定义的列仍然保持与此规则的绑定，如果不指定此项，所有由此用户定义数

12、据类型定义的列也将随之解除与此规则的绑定。,3用系统存储过程sp_unbindrule 解除规则的绑定,8.2.3 规则的绑定与松绑,例8-9 解除例8-6和例8-7绑定在employee表的hire_date列和用户定义数据类型pat_char上的规则。,EXEC sp_unbindrule employee.hire_date 运行结果如下： （所影响的行数为 1 行） 已从表的列上解除了规则的绑定。 EXEC sp_unbindrule pat_char,futureonly 运行结果如下： （所影响的行数为 1 行） 已从数据类型上解除了规则的绑定。,8.2.4 删除规则,使用DROP

13、 RULE语句删除当前数据库中的一个或多个规则。其语法格式如下： DROP RULE rule_name ,.n 注意：在删除一个规则前，必须先将与其绑定的对象解除绑定。 例8-10 删除例8-1和8-2中创建的规则。 DROP RULE sex_rule,hire_date_rule,8.2 使用规则实施数据完整性,EXEC sp_unbindrule employee.sex,8.3.1 创建默认值 8.3.2 查看默认值 8.3.3 默认值的绑定与松绑 8.3.4 删除默认值,8.3 使用默认值实施数据完整性,8.3 使用默认值实施数据完整性,8.3.1 创建默认值,默认值(Defaul

14、t)是用户输入记录时往没有指定具体数据的列中自动插入的数据。默认值对象与CREATE TABLE或ALTER TABLE语句操作表时用默认约束指定的默认值功能相似，两者的区别类似于规则与检查约束在使用上的区别。 默认值对象可以用于多个列或用户定义数据类型。表的一列或一个用户定义数据类型只能与一个默认值相绑定。 默认值的创建、查看、绑定、松绑和删除等操作可在企业管理器中进行，也可利用Transact-SQL语句进行。,8.3.1 创建默认值,8.3.1 创建默认值,1用企业管理器创建默认值 在企业管理器中选择数据库对象的“默认值”对象，单击右键，从快捷菜单中选择“新建默认值”选项，打开“默认属性

15、”对话框，如图8-7所示。输入默认值名称和值表达式之后，单击“确定”按钮，即完成默认值的创建。,8.3.1 创建默认值,2用CREATE DEFAULT语句创建默认值,CREATE DEFAULT语句用于在当前数据库中创建默认值对象，其语法格式如下： CREATE DEFAULT default_name AS constant_expression,8.3.1 创建默认值,CREATE DEFAULT birthday_defa AS 1978-1-1,例8-11 创建生日默认值birthday_defa。,例8-12 创建当前日期默认值today_defa。 CREATE DEFAULT

16、today_defa AS getdate(),1用企业管理器查看默认值 在企业管理器中选择数据库对象的“默认值”对象，即可从右边的任务板中看到默认值的大部分信息，如图8-8所示。,8.3.2 查看默认值,8.3.2 查看默认值,选择要查看的默认值，单击右键，从快捷菜单中选择“属性”选项，就会出现图8-9所示的“默认属性”对话框，可以从中编辑默认值的值表达式。,2用系统存储过程sp_helptext 查看默认值,使用sp_helptext系统存储过程可以查看默认值的细节。 例8-13 查看默认值today_defa。 EXEC sp_helptext today_defa 运行结果如图8-10

17、所示。,8.3.2 查看默认值,8.3.3 默认值的绑定与松绑,1用企业管理器管理默认值的绑定和松绑 在企业管理器中，选择要进行绑定设置的默认值，单击右键，从快捷菜单中选择“属性”选项，打开“默认属性”对话框，参见图8-9。 图8-9中的“绑定UDT(U)” 按钮用于将默认值绑定到用户定义数据类型，“绑定列(B)”按钮用于将默认值绑定到表的列。单击“绑定UDT(U)”按钮，则出现如图8-11所示的“将绑定默认值到用户定义的数据类型”对话框,8.3 使用默认值实施数据完整性,8.3.3 默认值的绑定与松绑,单击“绑定列(B)”按钮，则出现如图8-12所示的“将绑定默认值到表的列”对话框。管理默认

18、值与用户定义数据类型以及表的列之间的绑定和松绑与规则相同。,将默认值绑定到bith_date字段效果,8.3.3 默认值的绑定与松绑,2用sp_bindefault 绑定默认值,系统存储过程sp_bindefault 可以绑定一个默认值到表的一个列或一个用户定义数据类型上。其语法格式如下： sp_bindefault defname = default, objname = object_name , futureonly= futureonly ,8.3.3 默认值的绑定与松绑,例8-14 绑定默认值today_defa 到employee表的hire_date列上。,EXEC sp_bin

19、default today_defa, employee.hire_date 运行结果如下： 已将默认值绑定到列。,8.3.3 默认值的绑定与松绑,3用sp_unbindefault 解除默认值的绑定,系统存储过程sp_unbindefault 可以解除默认值与表的列或用户定义数据类型的绑定，其语法格式如下： sp_unbindefault objname = object_name , futureonly= futureonly ,8.3.3 默认值的绑定与松绑,例8-15 解除默认值today_defa与表employee 的hire_date 列的绑定。,EXEC sp_unbinde

20、fault employee.hire_date 运行结果如下： （所影响的行数为 1 行） 已从表的列上解除了默认值的绑定。,8.3 使用默认值实施数据完整性,8.3.4 删除默认值,可以在企业管理器中选择默认值，单击右键，从快捷菜单中选择“删除”选项删除默认值，也可以使用DROP DEFAULT语句删除当前数据库中的一个或多个默认值。其语法格式如下： DROP DEFAULT default_name ,.n 例8-16 删除生日默认值birthday_defa。 DROP DEFAULT birthday_defa,8.4.1 主键约束 8.4.2 外键约束 8.4.3 惟一性约束 8.

21、4.4 检查约束 8.4.5 默认约束,8.4 使用约束实施数据完整性,8.4 使用约束实施数据完整性,8.4.1 主键约束,约束(Constraint)是SQL Server提供的自动保持数据库完整性的一种机制，它定义了可输入表或表的单个列中的数据的限制条件。使用约束优先于使用触发器、规则和默认值。 约束独立于表结构，作为数据库定义部分在CREATE TABLE语句中声明，可以在不改变表结构的基础上，通过ALTER TABLE语句添加或删除。当表被删除时，表所带的所有约束定义也随之被删除。,8.4.1 主键约束,在SQL Server中有6种约束,主键约束 外键约束 惟一性约束 检查约束 默

22、认约束 非空值约束 其中非空值约束已在第5章中做了详细介绍，本节介绍其他5种约束。,8.4.1 主键约束,主键,表的一列或几列的组合的值在表中惟一地指定一行记录，这样的一列或多列称为表的主键（Primary Key，PK），通过它可强制表的实体完整性。 主键不允许为空值，且不同两行的键值不能相同。表中可以有不止一个键惟一标识行，每个键都称为侯选键，只可以选一个侯选键作为表的主键，其他侯选键称作备用键。 如果一个表的主键由单列组成，则该主键约束可以定义为该列的列约束。如果主键由两个以上的列组成，则该主键约束必须定义为表约束。,8.4.1 主键约束,定义列级主键约束的语法格式如下： CONSTRA

23、INT constraint_name PRIMARY KEY CLUSTERED | NONCLUSTERED 定义表级主键约束的语法格式如下： CONSTRAINT constraint_name PRIMARY KEY CLUSTERED | NONCLUSTERED (column_name , n ),CONSTRAINT constraint_name：指定约束的名称，如果不指定则系统会自动生成一个约束名。 PRIMARY KEY CLUSTERED | NONCLUSTERED：指定索引的类别，即聚集索引或非聚集索引。默认为Clustered。聚焦索引只能通过primary ke

24、y约束进行删除，不能使用Drop index删除。 (column_name , n )：指定组成主键的列名。,8.4.1 主键约束,例8-17 在Sales数据库中创建customer表，并声明主键约束。,CREATE TABLE Sales.dbo.customer ( customer_id bigint NOT NULL IDENTITY(0,1) PRIMARY KEY, customer_name varchar(50) NOT NULL, linkman_name char(8), address varchar(50), telephone char(12) NOT NULL

25、),8.4.1 主键约束,非聚集主键约束,若要定义customer_id列为非聚集主键约束，并指定约束名为PK_customer，使用以下语句： customer_id char(5) CONSTRAINT PK_customer PRIMARY KEY NONCLUSTERED,8.4.1 主键约束,例8-18 创建一个产品信息表goods1，将产品编号goods_id列声明为主键。,CREATE TABLE goods1 ( goods_id char(6) NOT NULL, goods_name varchar(50) NOT NULL, classification_id char(

26、6) NOT NULL, unit_price money NOT NULL, stock_quantity float NOT NULL, order_quantity float NULL CONSTRAINT pk_p_id PRIMARY KEY (goods_id) )ON PRIMARY,8.4.1 主键约束,例8-19 根据商品销售的时间和商品类别来确定销售的商品的数量。,CREATE TABLE g_order ( good_type int, order_time datetime, order_num int, CONSTRAINT g_o_key PRIMARY KEY

27、(good_type, order_time) ),8.4 使用约束实施数据完整性,8.4.2 外键约束,外键约束定义了表与表之间的关系。通过将一个表中一列或多列添加到另一个表中，创建两个表之间的连接，这个列就成为第二个表的外键(Foreign Key，FK)，即外键是用于建立和加强两个表数据之间的连接的一列或多列，通过它可以强制参照完整性。,8.4.2 外键约束,例如，Sales数据库中的employee、sell_order、goods这3个表之间存在以下逻辑联系： （1）sell_order (销售订单)表中employee_id(员工编号)列的值必须是employee表employee

28、_id列中的某一个值，因为签订销售订单的人必须是当前公司员工； （2）而sell_order表中goods_id(货物编号)列的值必须是goods (货物)表的goods_id列中的某一个值，因为销售订单上售出的只能是已知货物。 因此，在sell_order表上应建立两个外键约束FK_sell_order_employee和FK_sell_order_goods来限制sell_order表employee_id列和goods_id列的值必须分别来自employee表的employee_id列及goods表的goods_id列。,设置表的关系 设置指定两表主键（employee:employee

29、_id,sell_order:orderid） 选择其中某表（sell_order） -右键-设计表-工具栏-表和索引-属性框-关系 主键表：所选字段为主键的表（employee） 外键表：所选字段为外键的表（sell_order） 关系名：可自己指定,注：在保存时将检查外键，因此如果保存不成功，则原表中数据在相应 主表中不存在。,使用数据库设计器（关系图） 选择数据库(sales)-选择关系图-右键关系图-新建数据关系图-按提示操作,8.4.2 外键约束,级联操作,SQL Server提供了两种级联操作以保证数据完整性： (1) 级联删除确定当主键表中某行被删除时，外键表中所有相关行将被删除

30、。 (2) 级联修改确定当主键表中某行的键值被修改时，外键表中所有相关行的该外键值也将被自动修改为新值。,8.4.2 外键约束,外键约束与主键约束相同，也分为表约束与列约束。,定义表级外键约束的语法格式如下： CONSTRAINT constraint_name FOREIGN KEY (column_name , n ) REFERENCES ref_table (ref_column , n ) ON DELETE CASCADE | NO ACTION ON UPDATE CASCADE | NO ACTION NOT FOR REPLICATION 定义列级外键约束的语法格式如下： C

31、ONSTRAINT constraint_name FOREIGN KEY REFERENCES ref_table NOT FOR REPLICATION ,REFERENCES ref_table (ref_column , n )：指定建立关联的表的信息 ref_table：要建立关联表的名称 ref_column：建立关联的表的相关列的名称 N:指定外键列数，最多为16个。 ON DELETE CASCADE | NO ACTION ：指定在删除表中数据时，对关联表的级联删除操作。如果设置为cascade，则当主键表中某行被删除时，外键表中所有相关行将被删除。如果设置为No actio

32、n，则当主键表中某行被删除时，SQL Server将报错，并回滚该删除操作，默认为no action. ON UPDATE CASCADE | NO ACTION NOT FOR REPLICATION :指定列的外键约束在把从其他表中复制的数据插入到表中时不发生作用。,8.4.2 外键约束,例8-20 创建一个订货表sell_order1，与例8-18创建的产品表goods1相关联。,CREATE TABLE sell_order1 ( order_id1 char(6) NOT NULL, goods_id char(6) NOT NULL, employee_id char(4) NOT

33、 NULL, customer_id char(4) NOT NULL, transporter_id char(4) NOT NULL, order_num float NULL, discount float NULL, order_date datetime NOT NULL, send_date datetime NULL, arrival_date datetime NULL, cost money NULL, CONSTRAINT pk_order_id PRIMARY KEY (order_id1), FOREIGN KEY (goods_id) REFERENCES goods

34、1(goods_id) ),8.4.2 外键约束,CREATE TABLE sell_order2 ( order_id1 char(6) PRIMARY KEY, goods_id char(6) NOT NULL CONSTRAINT FK_goods_id FOREIGN KEY (goods_id) REFERENCES Goods1(goods_id) ON DELETE NO ACTION ON UPDATE CASCADE, employee_id char(4) NOT NULL FOREIGN KEY (employee_id) REFERENCES employee(emp

35、loyee_id) ON UPDATE CASCADE, customer_id char(4) NOT NULL, transporter_id char(4) NOT NULL, order_num float, discount float, order_date datetime NOT NULL, send_date datetime, arrival_date datetime, cost money, CONSTRAINT FK_customer_id FOREIGN KEY (customer_id) REFERENCES customer(customer_id),例8-21

36、 创建表sell_order2，并为goods_id、employee_id、custom_id三列定义外键约束。,8.4.2 外键约束,employee_id列的外键应当定义如下： CONSTRAINT FK_sell_order_employee /* 可省略 */ FOREIGN KEY (department_id,employee_id) REFERENCES employee(department_id,employee_id),例如，如果employee表的主键是由department_id和employee_id组成的复合主键，则sell_order表中应增加相同类型的depa

37、rtment_id列定义,8.4 使用约束实施数据完整性,8.4.3 惟一性约束,惟一性(Unique)约束指定一个或多个列的组合的值具有惟一性，以防止在列中输入重复的值，为表中的一列或者多列提供实体完整性。 惟一性约束指定的列可以有NULL属性。主键也强制执行惟一性，但主键不允许空值，故主键约束强度大于惟一约束。因此主键列不能再设定惟一性约束。,8.4.3 惟一性约束,定义列级惟一性约束的语法格式如下： CONSTRAINT constraint_name UNIQUE CLUSTERED | NONCLUSTERED 惟一性约束应用于多列时的定义格式： CONSTRAINT constra

38、int_name UNIQUE CLUSTERED | NONCLUSTERED (column_name , n ),8.4.3 惟一性约束,例8-22 创建goods2表，使goods_name具有惟一性约束。,CREATE TABLE goods2 ( goods_id char(6) NOT NULL PRIMARY KEY, goods_name varchar(50) NOT NULL CONSTRAINT u_goods_name UNIQUE NONCLUSTERED, classification_id char(6) NOT NULL, unit_price money N

39、OT NULL, stock_quantity float NOT NULL, order_quantity float ),8.4.3 惟一性约束,CREATE TABLE employees ( emp_id char(8), emp_name char(10) , emp_cardid char(18), CONSTRAINT pk_emp_id PRIMARY KEY (emp_id), CONSTRAINT uk_emp_cardid UNIQUE (emp_cardid) ),例8-23 定义一个员工信息表employees，其中员工的身份证号emp_cardid列具有惟一性。,8

40、.4 使用约束实施数据完整性,8.4.4 检查约束,检查(Check)约束对输入列或整个表中的值设置检查条件，以限制输入值，保证数据库的数据完整性。 当对具有检查约束列进行插入或修改时，SQL Server将用该检查约束的逻辑表达式对新值进行检查，只有满足条件(逻辑表达式返回TRUE)的值才能填入该列，否则报错。可以为每列指定多个CHECK约束。,8.4.4 检查约束,定义检查约束的语法格式：,CONSTRAINT constraint_name CHECK NOT FOR REPLICATION (logical_expression) NOT FOR REPLICATION：指定列的检查约

41、束在把从其他表中复制的数据插入到表中时不发生作用,8.4.4 检查约束,例8-24 更改表employee2以添加未验证检查约束。,ALTER TABLE employee2 WITH NOCHECK ADD CONSTRAINT CK_Age CHECK (DATEDIFF(year, Birth_Date, Hire_Date)18),8.4.4 检查约束,例8-25 创建一个订货表orders，保证各订单的订货量必须不小于10。,CREATE TABLE orders ( order_id char(8), p_id char(8), p_name char(10) , quantity

42、 smallint CONSTRAINT chk_quantity CHECK (quantity=10), CONSTRAINT pk_orders_id PRIMARY KEY (order_id) ),8.4 使用约束实施数据完整性,8.4.5 默认约束,默认(Default)约束通过定义列的默认值或使用数据库的默认值对象绑定表的列，以确保在没有为某列指定数据时，来指定列的值。 默认值可以是常量，也可以是表达式，还可以为NULL值。,8.4.5 默认约束,定义默认约束的语法格式,CONSTRAINT constraint_name DEFAULT constant_expression FOR column_name,8.4.5 默认约束,例8-27 在Sales数据库中，为员工表employee的sex列添加默认约束，默认值是“男”。,ALTER TABLE employee ADD CONSTRAINT sex_default DEFAULT 男 FOR sex 例8-28 更改表employee为hire_date列定义默认约束。 ALTER TABLE employee A