SQL4.docx

SQL4.docx

《SQL4.docx》由会员分享，可在线阅读，更多相关《SQL4.docx（73页珍藏版）》请在冰豆网上搜索。

SQL4

下面语句给出在CORPDATAH.DEPARTMENT表中的所有列的名字：

SELECT*

FROMCORPDATA.SYSCOLUMNS

WHERETBNAME='DEPARTMENT'

前面的例子的结果是对表中每列有一行信息，其中一些信息是不可见的，因为该信息的宽度比显示屏还要宽。

关于每列的更多信息，定义如下选择语句：

SELECTNAME,TBNAME,COLTYPE,LENGTH,DEFAULT

FROMCORPDATA.SYSCOLUMNS

WHERETBNAME='DEPARTMENT'

对于每一列，除了列名字以外，选择语句给出下列信息：

包含列的表的名字

列的数据类型

列的长度

是否允许缺省值

结果如下所示：

NAME

TBNAME

COLTYPE

LENGTH

DEFAULT

DEPTNO

DEPARTMENT

CHAR

3

N

DEPTNAME

DEPARTMENT

VARCHAR

29

N

MGRNO

DEPARTMENT

CHAR

6

Y

ADMRDEPT

DEPARTMENT

CHAR

3

N

第六章数据完整性

数据的完整性是集合中表之间数据的值要保持一致，这是商业活动中的一个重要原则。

例如，银行在表A中有一个客户清单，在表B中有一个客户的存款清单，那么一个客户要先申请加入表A中，该客户的存款才允许加入到表B中。

这章介绍系统自动保证这几种关系的不同方法。

引用完整性、检查约束和触发器是保证数据完整性的有效方法。

另外，在CREATEVIEW中的WITHCHECKOPTION子句限制通过视图插入和更新数据。

关于数据完整性的其它内容，请看AS/400DB2数据库编程。

6.1AS/400DB2检查约束

一个检查约束限制在一列或一组列中允许值的规则。

SQL支持在CREATETABLE和ALTERTABLE语句中的检查约束。

这些命令的详细说明，请看AS/400DB2SQL参考一书。

考虑下面的语句：

CREATETABLET1（COL1INT,COL2INTCHECK（COL2>0）,COL3INT）

这个语句生成有三个列的表，第二列有一个约束条件，它限制该列中的值要为正整数。

对这个表，如果用下面的语句：

INSERTINTOT1VALUES（-1,-1,-1）

将要失败，因为插入到第二列中的值没有满足约束条件，-1并不大于零。

若用下面的语句：

INSERTINTOT1VALUES（1,1,1）

将是成功的。

该行被插入后，下面的语句就是错误的：

ALTERTABLET1ADDCONSTRAINTC1CHECK（COL1=1ANDCOL1

这个修改表的语句试图加入一个约束，用来限制第一列为“1”这个值，让第二列的值大于1，这个约束条件不被接受，因为已经存在的数据并不满足约束条件的第二部分，（第二列中的“1”并不小于第一列的值“1”。

）

6.2AS/400DB2引用完整性

在原文附录A的AS/400DB2样本表中：

CORPDATA.EMPLOYEE是雇员的主列表。

CORPDATA.DEPARTMENT所有有效的部门号的主列表。

CORPDATA.EMP_ACT是执行项目的活动主列表。

其它的表引用这些表中相同的描述。

当一个表包含了主表中的数据，那么数据应确实出现在主列表中，否则引用无效。

包含主列表的表是父表，引用它的表是从属表，这一系列表中一个表引用另一个表的条件是有效的，称作引用完整性。

对引用完整性的讨论要求了解以下术语：

唯一键字是表中唯一标识一行的一列或一组列，尽管一个表可能有几个唯一键字，

但是在一个表中不可能有两行有相同的唯一键值。

主键字是不允许为空的唯一键字，一个表中只能有一个主键字。

父键字既可是唯一键字也可是主键字，它们是在引用约束中被引用的。

外来键字是一列或几列的值必须与它们的父键字一致的列。

如果用来建立外来键

字的列值为空，那么这个规则不适用。

一个父表是一个包含父键字的表。

一个从属表是一个包含外来键字的表。

一个子表是一个从属表或是一个从属表的子表。

引用完整性的实施防止了违反规则，即每一个非空的外来键字的状态必须有相匹配的父键字。

SQL支持在CREATETABLE和ALTERTABLE语句引用完整性的概念。

这些命令的详细说明，请看AS/400DB2SQL参考一书。

6.2.1生成有引用约束的表

在定义引用约束时，要规定：

一个主键或唯一键字

一个外来键字

删除和修改关系规则。

可选的，可以为约束条件指定一个名字，如果没规定名字，那么就自动生成一个。

删除和修改规则规定了当删除和修改父行时，要考虑子行的动作。

例如，在样本雇员表中给出的每一个部门号必须出现在从属表中的规则就是一个引用约束。

这个约束保证了每个雇员属于一个部门。

下面的SQL语言用这些约束关系定义创建了CORPDATA.DEPARTMENT和CORPDATA.EMPLOYEE表。

CREATETABLECORPDATA.DEPARTMENT

（DEPTNOCHAR（3）NOTNULLPRIMARYKEY,

DEPTNAMEVARCHAR（29）NOTNULL,

MGRNOCHAR（6）,

ADMRDEPTCHAR（3）NOTNULL

CONSTRAINTREPORTS_TO_EXISTS

REFERENCESCORPDATA.DEPARTMENT（DEPTNO）

ONDELETECASCADE）

CREATETABLECORPDATA.EMPLOYEE

（EMPNOCHAR（6）NOTNULLPRIMARYKEY,

FIRSTNAMEVARCHAR（12）NOTNULL,

MIDINITCHAR

（1）NOTNULL,

LASTNAMEVARCHAR（15）NOTNULL,

WORKDEPTCHAR（3）CONSTRAINTWORKDEPT_EXISTS

REFERENCESCORPDATA.DEPARTMENT（DEPTNO）

ONDELETESETNULLONUPDATERESTRICT,

PHONENOCHAR（4）,

HIREDATEDATE,

JOBCHAR（8）,

EDLEVELSMALLINTNOTNULL,

SEXCHAR

（1）,

BIRTHDATEDATE,

SALARYDECIMAL（9,2）,

BONUSDECIMAL（9,2）,

COMMDECIMAL（9,2）,

CONSTRAINTUNIQUE_LNAME_IN_DEPTUNIQUE（WORKDEPT,LASTNAME））

在这种情况下，DEPARTMENT表有唯一的部门号列作为一个主键字，是两个约束关系中的父表：

REPORTS_TO_EXISTS是一个自引用约束，DEPARTMENT表是同一关系的父表和从属表。

每一个ADMRDEPT非空值必须与DEPTNO的值相匹配。

一个部门必须报告出在数据库中存在的部门。

DELETECASCADE规则表明，如果有DEPTNO值n的行被删除，那么表中ADMRDEPT值为n的各行也应被删除。

WORKDEPT－EXISTS建立了EMPLOYEE表做为一个从属表。

WORKDEPT做为一个外来键字，这样，WORKDEPT的每一个值必须和DEPTNO的值匹配，DELETESETNULL的规则是指：

如果从DEPARTMENT中的DEPTNO的值为n的一行被删除了，那么在EMPLOYEE中WORKDEPT的值为n的行应置空。

UPDATERESTRICT规则是指：

如果在EMPLOYEE中的WORKDEPT的值和当前的DEPTNO值相匹配，那么不能更新DEPARTMENT中的DEPTNO的值。

在EMPLOYEE表中的UNIQUE_LNAME_IN_DEPT约束导致一个部门中的名字要唯一，当这个约束是不可能的时，它解释一个由几列组成的约束是怎样在表级上被定义的。

一旦定义了引用约束，系统就开始在通过SQL或其它接口（包括CL命令、实用程序和HLL语句）完成的每个插入、删除和修改操作中实施这个约束。

6.2.2取消引用约束

ALTERTABLE语句能用来每次从表中增加或去掉一个约束条件。

如果一个被去掉的约束条件是某些引用约束中的父键字，那么也取消父文件和从属文件之间的约束。

DROPTABLE和DROPCOLLECTION语句也取消表或集合的约束。

6.2.2.1取消约束的例子

下面的例子是在表DEPARTMENT中取消DEPTNO列的主键字，在表DEPARTMENT和EMPLOYEE上定义的REPORTS_TO_EXISTS和WORKDEPT_EXISTS约束也将取消，因为取消的主键字是这些约束关系中的父键字。

ALTERTABLECORPDATA.EMPLOYEEDROPPRIMARYKEY

能用名字取消一个约束，下面是例子：

ALTERTABLECORPDATA.DEPARTMENT

DROPCONSTRAINTUNIQUE_LNAME_IN_DEPT

6.2.3往有引用约束的表中插入数据

在往有引用约束的表中插入数据时，有一些重要的事情要记住，如果对有父键字的父表插入数据时，SQL不允许：

父键字有重复值。

如果父键字是主键字，主键字的任意一列是空值。

如果往有外来键字的从属表中插入数据：

插入到外键字列的非空值必须与父表中的相应父键字的某些值相等。

如果外来键字的列有空值，全部的外来键字就是空值。

如果所有外来键字都是空值，则插入成功（这里无唯一索引错误）。

6.2.3.1用约束插入数据的例子

修改样板应用项目表（PROJECT）定义两个外来键字：

一个外来键字是引用部门表中的部门号（DEPTNO）

一个外来键字是引用雇员表中的雇员号（RESPEMP）

ALTERTABLECORPDATA.PROJECTADDCONSTRAINTRESP_DEPT_EXISTS

FOREIGNKEY（DEPTNO）

REFERENCESCORPDATA.DEPARTMENT

ONDELETERESTRICT

ALTERTABLECORPDATA.PROJECTADDCONSTRAINTRESP_EMP_EXISTS

FOREIGNKEY（RESPEMP）

REFERENCESCORPDATA.EMPLOYEE

ONDELETERESTRICT

注意到父表列不在REFERENCES子句中规定，只要引用表有一个主键字或者有一个合适的唯一键字可以用来做父键字，那么就不要求规定这些列。

每一个插入到PROJECT表的行必须有一个DEPTNO的值和在部门表中的DEPTNO的某些值相等。

（空值是不允许的，因为DEPTNO在项目表中被定义为NOTNULL）。

行必须有一个RESPEMP的值或是和雇员表中的EMPNO的值相等或者为空。

下面的INSERT语句不能成功执行，因为没有与DEPARTMENT表中DEPTNO值（‘A01’）相匹配的值。

INSERTINTOCORPDATA.PROJECT（PROJNO,PROJNAME,DEPTNO,RESPEMP）

VALUES（'AD3120','BENEFITSADMIN','A01','000010'）

同样，下面的插入语句也不成功，因为在表EMPLOYEE中没有‘000011’的EMPNO值。

INSERTINTOCORPDATA.PROJECT（PROJNO,PROJNAME,DEPTNO,RESPEMP）

VALUES（'AD3130','BILLING','D21','000011'）

下面的插入语名能成功的完成，因为在表DEPARTMENT中有一个匹配的DEPTNO值‘E01’，在表EMPLOYEE中有一个匹配的EMPNO值‘000010’。

INSERTINTOCORPDATA.PROJECT（PROJNO,PROJNAME,DEPTNO,RESPEMP）

VALUES（'AD3120','BENEFITSADMIN','E01','000010'）

6.2.4更新有引用约束的表

如果更新一个父表，不能修改从属表行存在的主键字。

修改键字将违背从属表的引用约束且使一些行无父表行，此外，不能使主键字的任何一部分为空值。

6.2.4.1更新规则

在父表中执行UPDATE时，对从属表采取的动作，依赖于对引用限制规定的更新规则。

如果没有规定更新规则，则使用UPDATENOACTION规则。

UPDATENOACTION：

如果没有其它的行依整于父表中的行，那么可以更新它。

如果从属行中存在依赖关系，UPDATE失败。

对从属行的检查是在语句的末尾执行的。

UPDATERESTRICT：

它指出，如果没有其它的行依整于父表中的行，那么可以更新它。

如果从属行中存在依赖关系，UPDATE失败，对从属行的检查是立即执行的。

在了解了触发器与引用约束的相互影响时，很容易看到RESTRICT与NOACTION之间的细微不同。

触发器可以定义在操作前或操作后触发（此时是UPDATE语句）。

一个前触发器是在执行UPDATE前触发，因此是在检查约束之前。

后触发器是在执行UPDATE之后触发，RESTRICT约束规则之后（在此检查立即执行），但在NOACTION约束规则之前（检查是在语句的末尾执行），触发器和其规则按下列顺序发生：

1.一个前触发器是在UPDATE之前和RESTRICT或NOACTION约束规则之前触发。

2.一个后触发器将在RESTRICT约束规则之后，在NOACTION规则之前触发。

如果正修改一个从属表，一些修改的非空外来键值，必须和从属表的主键字相匹配。

例如，在雇员者表中的部门号依赖于部门表中的部门号，你可以不给雇员分配部门（空值），但不能给它分配一个不存在的部门。

如果对一个表的更新违反了引用约束条件从而导致UPDATE失败，那么在更新操作之中的所有改变都被还原。

详细内容请看19.2.2和19.2.3。

6.2.4.2UPDATE规则的例子

例如，如果一个部门对那些在项目表用部门行描述的项目仍然有责任的话，就不能更新部门表中的部门号。

下面的更新是失败的，因为项目表中有依赖于值是‘D01’的DEPARTMENT.DEPTNO的行（WHERE语句指定的行），如果允许这个UPDATE的话，那么就打破了在项目表和部门表之间的引用约束。

UPDATECORPDATA.DEPARTMENT

SETDEPTNO='D99'

WHEREDEPTNAME='DEVELOPMENTCENTER'

下面的语句也是失败的，因为它违反了存在于部门表的主键字DEPTNO和项目表的外来键字DEPTNO之间的引用约束。

UPDATECORPDATA.PROJECT

SETDEPTNO='D00'

WHEREDEPTNO='D01';

语句试图把所有部门号为‘D01’的改为‘D00’，因为‘D00’不是部门表中主键字DEPTNO的值，故语句失败。

6.2.5从有引用约束的表中删除数据

如果一个表有主键字但没有从属表，那么删除操作就象没有引用约束一样去做，同样，如果一个表只有外来键字没有主键字也是如此。

如果一个表有一个主键字和从属表，那么DELETE就根据定义的删除规则进行删除或修改行。

所有影响关系的删除规则必须满足删除操作能成功进行的目的。

如果违反引用约束，删除失败。

当在父表中执行DELETE时，在从属表中采取的动作依赖于约束定义的删除规则。

如果没有定义删除规则，使用DELETENOACTION。

DELETENOACTION：

如果无其它行从属于父表中的行，就可以删除父表中的行。

如果关系中存在从属性，那么删除失败。

从属行的检查是在语句的末尾执行的。

DELETERESTRICT：

如果无其它行从属于父表中的行，那么就可删除父表中的行。

如果关系中存在从属行，那么删除失败。

从属行的检查立即执行。

例如，如果一个部门对那些在项目表中的用部门描述的项目仍然有责任的话，就不能从部门表中删除。

DELETECASCADE：

删除父表中第一个被指出的行，然后，再删除从属行。

例如，能从部门表中的删除部门行，同时也删除了：

―向它报告的所有部门的行

―向这些部门报告的所有部门等

DELETESETNULL：

在每一个从属行中可为空白外来键字的列被置成缺省值。

就是说，如果这列是被删除行引用的外来键字的一部分，那么该列就置为缺省值。

仅仅影响立即派生的从属性。

DELETESETDEFAULT：

指出在每一个从属行中的外来键字的每一列被置为它的缺省值。

这就意味着如果这列是被删除行引用的外来键字的一部分，那么该列置为它的缺省值。

仅仅影响立即派生的从属性。

例如，如果一个雇员管理一些部门，可以从雇员表中删除该雇员。

在这种情况下，向管理者报告的每个雇员的MGRNO的值在部门表中被设置为空白。

在生成表时如果定义了缺省值，那么就使用该值。

这是由于在部门表中定义了REPORTS_TO_EXISTS约束。

如果一个派生表有RESTRICT或NOACTINON，且找到删除规则，不能删除的派生行，那么整个DELETE失败。

当在程序中执行这个语句时，删除的行数送往SQLCA的SQLERDD（3）。

这个数只指出在DELETE语句中规定的表中删除的行数，不包括根据CASCADE规则删除的行数。

在SQLCASQLERRD（5）中包含那些所有表中引用约束影响的行数。

在了解了触发器与引用约束的相互影响时，很容易看到RESTRICT与NOACTION之间的细微不同。

触发器可在操作前或操作后触发（此时是DELETE）。

一个前触发器是在执行DELETE前触发，因此是在检查约束之前。

后触发器是在执行DELETE之后触发，在RESTRICT约束规则之后（在此检查立即执行），但在NOACTION约束规则之前（检查是在语句的末尾执行），触发器和其规则按下列顺序发生：

1.一个前触发器是在DELETE之前和RESTRICT或NOACTION约束规则之前触发。

2.一个后触发器将在RESTRICT约束规则之后在NOACTION规则之前触发。

6.2.5.1DELETECASCADE的例子

从部门表中删除一个部门，它设定分配给此部门的雇员的WORKDEPT（在雇员表中）为空。

考虑下面的删除语句：

DELETEFROMCORPDATA.DEPARTMENT

WHEREDEPTNO='E11'

本书原文附录A中的AS/400DB2样板表给出表和数据。

此时，雇员表中的值为‘E11’的WORKDEPT的值置为缺省值。

在下面的样板数据中？

表示空值。

结果如下面所示：

表6－1部门表删除语句完成后表的内容

DEPTNO

DEPTNAME

MGRNO

ADMRDEPT

A00

SPIFFYCOMPUTERSERVICEDIV.

000010

A00

B01

PLANNING

000020

A00

C01

INFORMATIONCENTER

000030

A00

D01

DEVELOPMENTCENTER

A00

D11

MANUFACTURINGSYSTEMS

000060

D01

D21

ADMINISTRATIONSYSTEMS

000070

D01

E01

SUPPORTSERVICES

000050

A00

E21

SOFTWARESUPPORT

000100

E01

注意在部门表中没有级联删除，因为没有部门和E11部门相关。

下面是在删除语句完成前后雇员表受影响的部分。

表6－2雇员表的一部分，删除前的部分内容

EMPNO

FIRSTMNE

MI

LASTNAME

WORKDEPT

PHONENO

HIREDATE

000230

JAMES

J

JEFFERSON

D21

2094

1966-11-21

000240

SALVATORE

M

MARINO

D21

3780

1979-12-05

000250

DANIEL

S

SMITH

D21

0961

1960-10-30

000260

SYBIL

P

JOHNSON

D21

8953

1975-09-11

000270

MARIA

L

PEREZ

D21

9001

1980-09-30

000280

ETHEL

R

SCHNEIDER

E11

0997

1967-03-24

000290

JOHN

R

PARKER

E11

4502

1980-05-30

000300

PHILIP

X

SMITH

E11

2095

1972-06-19

000310

MAUDE

F

SETRIGHT

E11

3332

1964-09-12

000320

RAMLAL

V

MEHTA

E21

9990

1965-07-07

000330

WING

LEE

E21

2103

1976-02-23

000340

JASON

R

GOUNOT

E21

5696

1947-05-05

表6－3雇员表的一部分，删除后的部分内容

EMPNO

FIRSTMNE

MI

LASTNAME

WORKDEPT

PHONENO

HIREDATE

000230

JAMES

J

JEFFERSON

D21

2094

1966-11-21

000240

SALVATORE

M

MARINO

D21

3780

1979-12-05

000250

DANIEL

S

SMITH

D21

0961

1960-10-30

000260

SYBIL

P

JOHNSON

D21

8953

1975-09-11

000270

MARIA

L

PEREZ

D21

9001

1980-09-30

000280

ETHEL

R

SCHNEIDER

0997

1967-03-24

000290

JOHN

R

PARKER

4502

1980-05-30

000300

PHILIP

X

SMITH

2095

1972-06-19

000310

MAUDE

F

SETRIGHT

3332

1964-09-12

000320

RAMLAL

V

MEHTA

E21

9990

1965-07-07

000330

WING

LEE

E21

2103

1976-02-23

000340

JASON

R

GOUNOT

E21

5696

1947-05-05