第7章SQLppt.ppt

1、7.1数据完整性概念 7.2缺省与规则 7.3 数据约束 7.4约束与缺省、规则比较,第七章 数据完整性,7.1数据完整性概念,1、实体完整性 实体完整性又称行完整性，确保 表中行的唯一性。实体完整性要求表 中有一个主键，其值不为空，且能够 唯一标识一行。实体完整性通过 PRIMARY KEY约束、IDENTITY约束、,UNIQUE约束和索引来实现。 如jsy表中将驾照号定义为主 键，则每一个驾照号都唯一确定一个 驾驶员实体，即唯一确定一条驾驶员 记录。输入和修改驾照号列值时，其 值不能为空，也不能重复。 2、域完整性,域完整性又称范围完整性，确保 列的取值的有效性。域完整性通过定 义列的数

2、据类型、默认值、规则和检 查约束来限制列值的取值范围，使输 入到列的数据一定是正确的，并且是 合法的。如jsy表中“是否见习”列的取 值必须为“是”或“否”，不能为其他任,何数据，“出生时间”列的取值不能是 系统当前时间之后的时间等。 3、参照完整性 又称引用完整性，确保父表与子 表中数据的一致性。如果两个表之间 数据有引用关系，则引用数据的表为 子表，被引用数据的表为父表。,如xc表与cl表之间，二者通过车牌号 车牌号产生引用关系，xc表为子 表，cl表为父表，如表7-1和表7-2所 示。,一个子表可能有多个父表，如行 车表xc中主驾、车牌号和出车单号分 别引用驾驶员表jsy、车辆表cl和车

3、单 表cd，所以jsy表、cl表和cd表均为 xc表的父表。 参照完整性通过定义外键与主键 或外键与唯一键之间的对应关系实,现。如子表xc中“车牌号”列定义为外 键，并与父表cl的主键“车牌号”建立 对应关系，则其取值只能是主表中已 有的车牌号，确保了xc表与cl表数据 的一致性。当xc表新增加一行或修改 一行时，如果该行的“车牌号”列取值 不存在于cl表中则出错当删除cl表,中某一行时，如果该行的车牌号在xc 中还有对应记录时，也会发出错误信 息。 根据数据完整性要求，满足数据 完整性的数据一般具有以下特点： (1)数据的取值范围符合要求。 (2)数据在表中唯一标识。,(3)数据与其他表中的

4、相关数据相匹 配。 当数据由客户端传送到SQL Server服务器时，只有满足数据完整 性数据才能真正地被存储到数据库中.,7.2缺省与规则,缺省和规则来源于由Sybase开 发的SQL Server，在老版本的SQL Server或者升级版本中都有缺省和规 则的使用。缺省是为列提供数据的一 种方式，如果用户进行INSERT操作 时不为列输入数据，则使用缺省值。,规则是当用户进行INSERT或PDATE 操作时，对输入列中的数据设定的取 值范围。缺省与规则有以下特点： (1)缺省与规则是数据库对象，它们是 独立于表和列而建立的。 (2)缺省与规则建立后与列或数据类型 产生关联，列和数据类型就具

5、有了缺,省与规则的属性。 (3)缺省与规则定义后，可以重复使 用，可以绑定到多个列或数据类型 上。 (4)缺省与规则不随表同时调入内存， 当用到时才被调入内存，这可能会使 程序执行出现延时。,缺省与规则不是ANSI标准，一般 不提倡使用，应尽可能使用约束，任 何可以使用缺省与规则的地方都有可 以使用约束。缺省和规则对象通常只 在它所创建的数据库中有效，可以用 脚本创建缺省和规则，并复制到其它 数据库。,一、创建缺省和规则,（一）命令方式 可以使用CREATE DEFAULT和 CREATE RULE命令分别创建缺省和 规则，命令格式分别为： CREATE DEFAULT default_nam

6、e,AS constant_expression CREATE RULE rulet_nameAS rule_conditions 其中default_name和rule_name 分别为缺省值对象名和规则对象名， constant_expression为一个常量、,数学表达式或内置函数，不能引用其 列或别的数据库对象。rule_conditions为规则表达式，表 示数据需满足的条件。 例7-1创建缺省对象，默认为字 符型数据“是”，以便将来绑定到是否 见习列，使是否见习列的取值默认为,“是”。 CREATE DEFAULT def_jx AS “是” 例7-2创建规则对象，规定取值 可以为

7、“队长”或“副队长”或“调度 员”。以便将来绑定到职称列，使职 务列的取值只能为其之一。,CREATE RULE rul_zw AS zw IN (队长,副队长,调度员) 其中zw为占位符，当规则绑定到一 个列时，列的名字被占位符替代。这 样可以在不知道列名的情况下建立规 则。 （二）界面方式,在企业管理器中建立缺省和规则 的方法基本相同，这里以创立规则为 例说明操作步骤。 当创立缺省时将规则换成默认值 即可。 第1步 打开企业管理器，在右边窗口 展开层次结构，选择traffic数据库中,的规则对象，单击鼠标右键，在弹 出的快捷菜单中选择“新建规则”。 第2步 在打开的新建规则窗口中，在 名称

8、栏中输入名称，在文本框中输入 规则的条件，如图7-1。,然后单击“确定”按钮即可。此时在企 业管理器右边窗口出现新创建的规则 对象，如图7-2。,二、绑 定,（一）命令方式 通过调用系统过程 SP_BINDEFAULT和 SP_BINDRULE，可以为一个列或用 户自定义数据类型绑定一个缺省和规 则，其语法格式分别为：,SP_BINDEFAULT default_name,object_name ,FUTUREONLY SP_BINDRULE rule_name, object_name ,FUTUREONLY 其中参数说明：,default_name和rule_name分别为缺 省名和规则名

9、。 object_name为对象名，如果 object_name为“表名列名”形式， 则认为object_name是列名，否则认 为是自定义数据类型对象名。 FUTUREONLY表示缺省和规则仅对,在此之后创建的相同自定义数据类型 的列有效。 例如绑定缺省def_jx到是否见 习列，其语句代码为： EXEC SP_BINDEFAULT def_jx, jsy.是否见习 绑定规则rul_zc到职称列，其语句代,码为： EXEC SP_BINDRULE rul_zw ,ddy.职务 （二）界面方式 在企业管理器中向列或用户自定 义数据类型绑定缺省和规则操作也基 本相同，这里以绑定规则rul_zw为

10、例,说明操作步骤。 第1步 打开企业管理器，在左边窗口 展开层次结构，选择traffic数据库中的规则对象，在右边窗口中的 rul_zc对象上单击鼠标右键，在弹出 的快捷菜单中选择“属性”，如图7- 3，或直接双击rul_zc对象。,（二）界面方式 第2步 在打开的规则属性窗口中有两 个按钮，一个是“绑定UDT”即绑定到 用户自定义数据类型，一个是“绑定 列”。单击“绑定列”，如图7-4。,第3步 在打开的规则绑定到列的窗口 中，先选择表ddy，然后选择其中的 “职务”列，添加到绑定列的列表中， 如图7-5。,第4步 单击“确定”按钮即完成绑定。 在企业管理器的表设计模式下也 可以向列或用户自

11、定义数据类型绑定 缺省，当打开一个表设计器时，会看 到窗口下半部分是设置列属性选项 卡，如打开表jsy设计器，选择是否见,习列的定义行，单击下面的默认值 栏，在缺省栏中已创建的缺省对象会 显示在下拉列表中，其中有名为 def_jx的对象，选择其作为缺省就可 以了，如图7-6。,三、取 消 绑 定,（一）命令方式 可调用系统过程SU_UNBIDNEFAULT或 SP_UNBINDRULE取消绑定，其 语法格式为： SU_UNBINDEFAULT,object_name ,FUTUREONLY SP_UNBINDRULE object_name ,FUTUREONLY 其中参数说明： object

12、_name为对象名，如果,object_name为“表名列名”形 式，则认为object_name是列名， 否则认为是自定义数据类型对象 名。当你取消用户自定义数据类型 的绑定时，所有属于该数据类型的 列同时取消绑定。对属于该数据类 型的列，如果其缺省和规则直接绑,定到列上，则该列不受影响。 FUTUREONLY仅用于取消此后的 用户自定义数据类型的绑定，缺省 和规则对现有的属于该数据类型的 列仍有效。 例如取消是否见习列的缺省def_jx 绑定，其语句代码为：,EXEC SP_UNBINDEFAULT , jsy. 是否见习 取消职称列的规则rul_zw绑 定，其语句代码为： EXEC SP

13、_UNBINDRULE ddy.职务 在这里不需要提供缺省名称或,规则名称，因为无论缺省或规则是 否绑定在对象上，每一个列或用户 自定数据类型只能与一个缺省或规 则建立关联。 （二）界面方式 在企业管理器中取消绑定和进 行绑定的界面基本相同，比如在上,一节以界面方式进行绑定操作的第 3步的图7-6中，在绑定列的列表中 选择需取消绑定的列，单击“删除” 按钮，然后单击“确定”按钮，即完 成取消绑定的操作。 在企业管理器的表设计模式下 也可以取消缺省绑定，但在这个界 面中不进行取消规则的绑定。,四、删除缺省和规则,（一）命令方式 使用DROP DEFAULT或 DROP RULE命令删除缺省和规

14、则，其命令格式分别为： DROP DEFAULT default_name,n,（一）命令方式 使用DROP DEFAULT或 DROP RULE命令删除缺省和规 则，其命令格式分别为： DROP DEFAULT default_name,n DROP RULE rule_name,n,其中default_name和rule_name 分别为要删除的缺省名和规则名， 可以包含所有者名。参数n表示可 以同时指定多个缺省或规则一次删 除。 例如删除缺省对象def_jx 语句为： DROP DEFAULT def_jx 删除规则对象rul_zw的语句为：,DROP RULE rul_zw （二）界面

15、方式 打开企业管理器，展开层次结 构，选择数据库的规则对象或默 认值对象，在右边窗口需要删除的 规则对象或默认值对象上单击鼠标 右键，在快捷菜单中选择“删除”， 如图7-3。,7.3数据约束,约束是指在数据进入到数据库 之前应满足的要求或限定的条件。 约束不是一个独立的对象，而是 数据表的一部分。在创建表时，可 以对某一列或多个列的组合设置限 制条件，让SQL Server帮助检查,该列的输入值是否符合要求。当用 户从客户端传送数据到SQL Server服务器，系统也会检查数 据是否符合要求，如果不符合要求 将拒绝接受。 可以对表设置主键 （PRIMARY KEY）约束、唯一 （UNIQUE）

16、约束、外键,（FOREIGN KEY）约束、缺省 （DEFAULT）约束和检查 （CHECK）约束等，对于BYTE和 TEXT列不可以指定主键约束、唯 一约束和外键约束，但可以用检 查约束来检查其值是否为NULL。,约束作为表的一部分，可以在 创建表的同时创建约束，也可以在 建立表之后追加定义或删除定义。 在一个表中可以定义多个约束，甚 至可以在一个列上定义多个约束。,一、列约束与表级约束,列级约束是定义在一个列上的约束，是列定义的一部分，与列 名、数据类型、唯一标识号、默认 值和排序规则等一样，都是列的属 性。严格地说列的数据类型也是一 种列约束，它限定了列的取值的,域。表级约束是在列定义外

17、单独定 义的，它是多列之间的约束，用 CONSTRAINT关键字定义。如果 要对一个表中的多个列定义约束， 必须首先定义每个列，然后在末尾 追加表约束。 例7-3创建含有列约束驾驶员,表jsy_temp1 CREATE TABLE jsy_temp1 ( 驾照号char(8)NOT NULL, 姓名 varchar(8) NOT NULL, 是否见习 char(1) NOT NULL , 所学专业 varchar(8) NULL） 其中NOT NULL为每个列定义中列,级约束，为简单起见，都没有定义 列约束名，由系统自动命名。 例7-4创建含有表约束的行车 表xc_temp。 CREATE T

18、ABLE xc_temp ( 驾照号 char(6) NOT NULL, 车牌号 char(4) NOT NULL,调度号 char(4) NULL, 行程 smallint NULL, CONSTRAINT pkey_xc_temp PRIMARY KEY (驾照号, 车牌号)） 其中表级约束是一个主键约束，约 束名为pkey_xc_temp，主键为 “驾照号”和“车牌号”两列的组合。,作为主键的列不能为NULL，无论 是否定义为NOT NULL，如果列被 定义为主键或主键的一部分，列自 然变为NOT NULL属性。 可以为每个约束设定约束名， 也可以不设定，如果不命名约束， 系统将自动提供

19、一个不重复的名 字。比如：,DF_Szexam_xbD_24516F65就是 SQL Server生成的一个约束名， 名字的最后是十六进制数，尽管它 确保了名字的唯一性，但对数据库 管理员和数据库设计者来说，不便 于认定对象，在删除表中的多列时 是非常麻烦的，所以建议尽量为重 要的约束命名。,二、主 键 约 束,为了确保更新或引用数据时行定 位的唯一性，必须为每一个表设定主 键，主键可以是一个单独的列，也可 以是多个列的联合。如在jsy表中主 键是驾照号，在xc表中主键是主驾、 车牌号和出车单号的,组合。主键具有以下特性： （）每一个表仅能有一个主键。 （）主键值不可为NULL，该值 在表内是

20、唯一的，即没有重复值。 （）主键已经具有UNIQUE的特 性，作为主键的列不能再被定义成 UNIQUE约束。,（）IMAGE和TEXT类型的列不 能作为主键。 （）主键具有自动索引的作用， 不能人为取消。取消索引的唯一方 法是取消主键约束。 在一个已有数据的表中建立主 键约束，那么表中的数据必须符合,约束的要求，否则不能建立索引， 也就不能定义主键约束。如果试图 向表中插入不符合主键约束条件的 数据，SQL Server将拒绝请求， 虽然不需要在应用程序中专门设置 捕获违规操作的代码，但在T-SQL 存储过程或者VC应用中,程序必须,有能够处理这种违规操作的代码。 （二）命令方式定义主键约束

21、用PRIMARY KEY关键字定义 主键约束。 有两种方法，一种是使用CREATE TABLE语句直接建立，例如： CREATE TABLE jsy_temp2,( 驾照号char(8) NOT NULL PRIMARY KEY CLUSTERED, 姓名 varchar(8)NOT NULL,） 另一种方法是在创建表之后用 ALTER TABLE语句设定，例如： CREATE TABLE jsy_temp2,( 驾照号char(8) NOT NULL , 姓名 varchar(8) NOT NULL,） GO ALTER TABLE jsy_temp2 ADD CONSTRAINT pkey

22、_jsy_temp2 PRIMARY KEY GO,（二）界面方式定义主键约束 在企业管理器窗口很容易为一 个表设定主键。在企业管理器中展 开数据库，选择指定的表，打开表 设计器，选中要作为主键的一列或 几列，单击右键，从快捷菜单中选 择“设置主键”，如图7-7。,也可以单击工具栏上设置主键 图标。图7-8将行车表xc的主 驾、车牌号和出车单号设置为多列 组合主键。,表的主键约束可唯一确定表中 的行。但由于在一个表中只能有一 个主键，如果其他列或列的组合也 具有唯一的特性，而且希望在输入 数据时SQL Server能帮助加以检 查，就可以利用唯一约束定义列或,三、惟一约束,列组合。如驾驶员表j

23、sy中除有驾 照号、姓名、籍贯和出生年月等等 字段外，还有身份证号和人身保险 号等字段，其中驾照号为主键，但 身份证号和保险号也都具有唯一 性，可以定义成唯一约束，以确保 输入数据的正确性。,一旦在列或列的组合上定义了 唯一约束，那么数据必须符合唯一 约束的要求，不能为NULL 或重复 值，数据输入或修改时SQL Server系统将执行唯一性检查。 （一）命令方式定义唯一约束 用UNIQUE关键字定义唯一约束,同样有两种方法，方法1是在创建 表时定义唯一约束，例如： CREATE TABLE jsy_temp3 ( 驾照号char(8) NOT NULL PRIMARY KEY CLUSTER

24、ED, 姓名varchar(8) NOT NULL, 身份证号 char(18) NOT,NULL UNIQUE NONCLUSTERED） 方法2是在创建表之后用ALTER TABLE语句设定。 （二）界面方式定义唯一约束 在企业管理器中为列定义唯一 约束的步骤为：,第1步 在企业管理器窗口展开数据 库层次结构，选择指定的表，打开 表设计器，选中需要作为唯一约束 的列，单击鼠标右键，在弹出的快 捷菜单中选择“索引/键”。 第2步在打开的属性对话框中单 击“索引/键”标签，单击“新建”按 钮，如图7-8。,第3步在打开的新建窗口，设置 唯一约束的名称及索引类别参数， 单击“确定”按钮，如图7-

25、9。 也可以在属性窗口工具栏中单击 “管理约束”图标，然后在打开 的属性对话框中单击“CHECK约 束”标签，打开“CHECK约束”对话 框进行设置。,四、外 键 约 束,外键约束是用于在两个表之间 实现一对一或一对多的关系。如 jsy表与xc表之间的关系就是一对 多的关系，jsy表中的一行与xc表 中的一行或多行有对应关系，jsy 表为父表，xc表为子表。定义外键,约束后，SQL Server自动执行一 对多关系的某些规则，如不能在xc 表中插入jsy表中不存在的驾驶员 记录 ，而应该首先在jsy表中输入 驾驶员记录，然后在xc表中再输入 该驾照号。同理，必须先删除了xc 表中某个驾驶员的所

26、有记录后，才,能删除jsy表中该驾驶员的记录。 一个表中可以有一个或多个外 键，也可以没有外键，但当表中的 数据与其他表中数据有对应关系 时，应该定义外键来指明该对应关 系。外键必须指向父表的主键或具 有唯一约束的键即候选键，这样,SQL Server才能确切地知道父表 中的哪一行正在被子表的外键引 用。 （一）命令方式定义外键约束 用REFERENCES关键字定义 外键约束。有两种方法，方法1是 在创建表时定义外键约束，例如：,CREATE TABLE xc_temp ( 驾照号char(6) NOT NULL REFERENCES jsy (驾照号) ON UPDATE CASCADE O

27、N DELETE CASCADE, 车牌号 char(8) NOT NULL REFERENCES cl(车牌号),ON UPDATE CASCADE, 调度号 char(4) NULL, 行程 smallint NULL, CONSTRAINT pkey_xc_temp PRIMARY KEY (驾照号, 车牌号) ) 例中激活了层叠更新和层叠删,除（层叠更新和层叠删除参见第8 章）。如果改变主键的值或删除主 键的值，那么与其相关联的外键将 自动层叠式地改变，这样能够保持 主键值和外键值的同步调整。如无 此关键字，则不激活层叠改变。 方法是在创建表之后用ALTER,TABLE语句设定。 （二

28、）界面方式定义外键约束 在企业管理器中定义列外键约 束的步骤为： 第1步 在企业管理器窗口展开数据 库层次结构，选择指定的表，打开 表设计器，选中需要作为外键的,列，单击鼠标右键，在弹出的快捷 菜单中选择“属性”。 第2步在打开的对话框中单击“关 系”标签卡，单击“新建”按钮，如 图7-10。,第3步在打开的新建窗口，设置外 键约束的名称及父表主键参数，单 击“关闭”按钮即完成定义设置，如 图7-11。 也可以在属性窗口工具栏中单 击“管理约束”图标，打开 “CHECK约束”对话框进行设置。,五、缺 省 约 束,缺省值是指当对列数据进行 INSERT操作时系统自动提供的 值，缺省约束是加在缺省

29、值上的规 则。缺省约束与前面所介绍的“缺 省定义”一样执行相同的功能，但 “缺省定义”是独立的数据库对象,二者是不相关的，而且缺省约束 仅用于INSERT语句，不适用于 UPDATE。由于“缺省定义”仍然建 立约束，有时也把“缺省定义”当作 “缺省约束”对待。 缺省值可以是常量或系统函 数。SQL 对缺省约束不能像缺省,对象那样建立和绑定，并且定义在 某个列的缺省不能应用于其他列。 如果需要一个表的多个列或多 个表的多个列设置相同的缺省，只 能逐一地进行定义，缺省值实际变 成了列属性。不过可以通过代码复 制，为每一列提供相同的缺省。,（一）命令方式定义缺省约束 用DEFAULT关键字定义缺省

30、约束。同样有两种方式建立缺省约 束，方法1是在创建表时定义缺省 约束，例如：CREATE TABLE jsy_temp4 (驾照号intIDENTITY(1,1),NOT NULL PRIMARY KEY CLUSTERED, 姓名 varchar(8) NOT NULL, 是否见习 varchar(20)DEFAULT(是), 身份证号 char(18) NOT NULL,UNIQUE NONCLUSTERED) 方法2是在创建表之后用ALTER TABLE语句设定。例如： CREATE TABLE jsy_temp4 (驾照号intIDENTITY(1,1) NOT NULL , 姓名 v

31、archar(8)NOT NULL,是否见习char(2) , 身份证号 char(18)NOT NULL ) GO ALTER TABLE jsy_temp4 ADD CONSTRAINT def_ jsy_temp DEFAULT,GO （二）界面方式定义缺省约束 在企业管理器中设置列缺省约 束步骤为： 第1步 在企业管理器窗口展开数据 库层次结构，择指定的表，打开表 设计器。,第2步选中需要设置缺省的列， 在窗口下面部分缺省栏中进行设 置，此时缺省值成为了列的属性， 如图7-12。 在此不能对缺省约束命名。如 果已经建立了缺省对象，那么缺省 栏将出现下拉列表，可以选择某一 缺省对象进行绑

32、定。,六、CHECK 约 束,CHECK约束与规则的作用相 同，可以让系统对列的输入值进行 正确性检查，用户在插入或修改数 据时列值必须符合CHECK约束条 件，否则数据便无法加入到数据表 中。CHECK约束可以是一个数据,列表，也可以是一个数据范围。在 某一列上可以加入多个CHECK约 束，如对电话号码列可以同时检查 位数和区域码是否正确。 （一）命令方式定义CHECK约束 用CHECK关键字定义CHECK 约束。同样有两种方式建立,CHECK约束，方法1是在创建表时 定义CHECK约束，例如： CREATE TABLE jsy_temp5 ( 驾照号intIDENTITY(1,1) NOT

33、 NULL PRIMARY KEY CLUSTERED,姓名 varchar(8) NOT NULL, 是否见习char(1) DEFAULT(是), 身份证号 char(18) NOT NULL UNIQUE NONCLUSTERED, 电话 char(8) CHECK(LEN(电 话)=8 AND电话LIKE 72_-%) ),方法2是在创建表之后用ALTER TABLE语句设定，例如： CREATE TABLE jsy_temp5 ( 驾照号intIDENTITY(1,1) NOT NULL , 姓名 varchar(8)NOT NULL, 是否见习char(1) ,电话char(8)

34、GO ALTER TABLE jsy_temp5 ADD CONSTRAINT ck_ jsy_temp5 CHECK(LEN(电话)=8 AND 电话LIKE 72_-%) GO,（二）界面方式定义CHECK约束 第1步 在企业管理器窗口展开数据 库层次结构，选择指定的表，打开 表设计器 ，选中需要设置CHECK 约束的列，单击鼠标右键，在弹出 的快捷菜单中选择“CHECK约 束”，如图7-13。,第2步在打开的“CHECK约束” 对话框，单击“新建”按钮，在新建 约束对话框中分别输入约束名和约 束表达式，如图7-14。 第3步 单击“关闭”按钮即可。 也可以在属性窗口工具栏中单 击“管理约

35、束”图标，然后在打,开的属性对话框中单击“CHECK约 束”标签卡，打开“CHECK约束”对 话框进行设置。,七、启用与禁止约束,如果在现有的表数据基础上追 加各种约束，那么需要考虑约束是 否对表中已有数据起作用，分下列 几种情况： （1）当建立主键约束或唯一约束 时，SQL Server会在后台自动建,立惟一索引，并对表中的数据进行 检查，以确保数据符合约束要求， 这种自查过程是无法关闭。 （2）当创建检查约束和外键约束 时，在缺省状态下，系统也会自动 对数据进行检查，与主键约束和惟 一约束不同的是，它可以取消。当,确认表中的数据已经符合约束条件 时，就不必再浪费时间重复检查。 如果已确认表中的数据不符合 约束条件，又不想马上清理当前的 数据，也不必对数据进行检查，此 时可以使用WITH NOCHECK选项