DB2 基础: 约束
2010-07-20 00:00:00 来源:WEB开发网 闂傚倸鍊搁崐鎼佸磹閹间礁纾归柟闂寸绾惧綊鏌熼梻瀵割槮缁炬儳缍婇弻鐔兼⒒鐎靛壊妲紒鐐劤缂嶅﹪寮婚悢鍏尖拻閻庨潧澹婂Σ顔剧磼閻愵剙鍔ょ紓宥咃躬瀵鎮㈤崗灏栨嫽闁诲酣娼ф竟濠偽i鍓х<闁绘劦鍓欓崝銈囩磽瀹ュ拑韬€殿喖顭烽幃銏ゅ礂鐏忔牗瀚介梺璇查叄濞佳勭珶婵犲伣锝夘敊閸撗咃紲闂佺粯鍔﹂崜娆撳礉閵堝洨纾界€广儱鎷戦煬顒傗偓娈垮枛椤兘骞冮姀銈呯閻忓繑鐗楃€氫粙姊虹拠鏌ュ弰婵炰匠鍕彾濠电姴浼i敐澶樻晩闁告挆鍜冪床闂備浇顕栭崹搴ㄥ礃閿濆棗鐦遍梻鍌欒兌椤㈠﹤鈻嶉弴銏犵闁搞儺鍓欓悘鎶芥煛閸愩劎澧曠紒鈧崘鈹夸簻闊洤娴烽ˇ锕€霉濠婂牏鐣洪柡灞诲妼閳规垿宕卞▎蹇撴瘓缂傚倷闄嶉崝搴e垝椤栫偛桅闁告洦鍨扮粻鎶芥倵閿濆簼绨藉ù鐘荤畺濮婃椽妫冨☉娆愭倷闁诲孩鐭崡鎶芥偘椤曗偓瀹曞爼顢楁径瀣珫婵犳鍣徊鍓р偓绗涘洤绠查柛銉墮閽冪喖鏌i弬鎸庢喐闁荤喎缍婇弻娑⑩€﹂幋婵囩亪濡炪値鍓欓悧鍡涒€旈崘顔嘉ч幖绮光偓鑼嚬缂傚倷绶¢崰妤呭箰閹间焦鍋╅柣鎴f绾偓闂佺粯鍔曠粔闈浳涢崘顔兼槬闁逞屽墯閵囧嫰骞掗幋婵愪紑閻庤鎸风粈渚€鍩為幋锔藉亹闁圭粯甯╂导鈧紓浣瑰劤瑜扮偟鍒掑▎鎾宠摕婵炴垶鐭▽顏堟煙鐟欏嫬濮囨い銉︾箞濮婃椽鏌呴悙鑼跺濠⒀傚嵆閺岀喖鎼归锝呯3闂佹寧绻勯崑娑㈠煘閹寸姭鍋撻敐搴樺亾椤撴稒娅婇柡灞界У濞碱亪骞忕仦钘夊腐闂備焦鐪归崐鏇㈠箠閹邦喗顫曢柟鎯х摠婵挳鏌涢幘鏉戠祷闁告挸宕—鍐Χ閸℃浠搁梺鑽ゅ暱閺呮盯鎮鹃悜钘壩ㄧ憸澶愬磻閹剧粯鏅查幖绮瑰墲閻忓秹姊虹紒妯诲鞍婵炲弶锕㈡俊鐢稿礋椤栨氨鐤€闂傚倸鐗婄粙鎰姳閼测晝纾藉ù锝堟閻撴劖鎱ㄥΟ绋垮婵″弶鍔欓獮妯兼嫚閼碱剦妲伴梻浣稿暱閹碱偊宕愭繝姣稿洭寮舵惔鎾存杸濡炪倖姊婚妴瀣啅閵夛负浜滄い鎾跺仜濡插鏌i敐鍥у幋妤犵偞甯¢獮瀣籍閳ь剟鎮楁繝姘拺閻熸瑥瀚崕妤呮煕濡 鍋撻悢鎻掑緧婵犵數濮烽弫鍛婃叏閻戣棄鏋侀柛娑橈攻閸欏繑銇勯幘鍗炵仼缁炬儳顭烽弻鐔煎礈瑜忕敮娑㈡煃闁垮鐏﹂柕鍥у楠炴帡宕卞鎯ь棜缂傚倸鍊风粈渚€藝闁秴鏋佸┑鐘虫皑瀹撲線鏌涢埄鍐姇闁稿﹦鍏橀弻娑樷攽閸℃浼€濡炪倖姊归崝鏇㈠煘閹达附鍊婚柛銉㈡櫇鏍¢梻浣告啞閹稿鎮烽敂鐣屸攳濠电姴娲﹂崵鍐煃閸濆嫬鏆熼柨娑欑矒濮婇缚銇愰幒鎴滃枈闂佸憡鐟ユ鎼佸煝閹炬枼鍫柛顐ゅ枔閸樻悂鏌h箛鏇炰户缁绢厼鐖煎畷鎴﹀箻鐠囪尙鐤€婵炶揪绲介幉锟犲磹椤栫偞鈷戠痪顓炴噹娴滃綊鎮跺☉鏍у姦闁糕斁鍋撳銈嗗笒閸燁偊鎯冨ú顏呯厸濞达絽婀辨晶顏堟煃鐟欏嫬鐏撮柟顔界懇瀵爼骞嬮悩杈敇闂傚倷绀佸﹢杈ㄧ仚闂佺濮ょ划搴ㄥ礆閹烘绫嶉柛顐ゅ枎娴犺櫣绱撴担鍓插創妞ゆ洘濞婇弫鍐磼濞戞艾骞堥梻浣告惈濞层垽宕濆畝鍕€堕柣妯肩帛閻撴洟鏌熼懜顒€濡煎ù婊勫劤閳规垿鏁嶉崟顐℃澀闂佺ǹ锕ラ悧鐘茬暦濠靛鏅濋柍褜鍓熼垾锕傚锤濡も偓閻掑灚銇勯幒宥堝厡缂佺姴澧介埀顒€鍘滈崑鎾斥攽閻樿京绐旈柛瀣殔閳规垿顢欑涵鐑界反濠电偛鎷戠徊鍨i幇鏉跨闁瑰啿纾崰鎾诲箯閻樼粯鍤戦柤绋跨仛濮f劙姊婚崒姘偓鐑芥嚄閼哥數浠氭繝鐢靛仜椤曨參宕楀Ο渚殨妞ゆ劑鍊栫€氭氨鈧懓澹婇崰鏍р枔閵婏妇绡€闁汇垽娼ф牎缂佺偓婢樼粔鐟邦嚕閺屻儱绠甸柟鐑樼箘閸炵敻鏌i悩鐑橆仩閻忓繈鍔岄蹇涘Ψ瑜夐崑鎾舵喆閸曨剙纰嶅┑鈽嗗亝缁诲倿锝炶箛娑欐優闁革富鍘鹃敍婊冣攽閳藉棗鐏犻柟纰卞亰閿濈偛顓奸崶鈺冿紳婵炶揪缍侀ˉ鎾诲礉瀹ュ鐓欑紒瀣仢閺嗛亶鏌i敐鍥у幋妤犵偛顑夐弫鍐焵椤掑倻涓嶅┑鐘崇閸嬶綁鏌涢妷鎴濆暟妤犲洭鎮楃憴鍕碍缂佸鎸抽垾鏃堝礃椤斿槈褔鏌涢埄鍏狀亪妫勫鍥╃=濞达絽澹婇崕鎰版煕閵娿儱顣崇紒顔碱儏椤撳吋寰勭€n亖鍋撻柨瀣ㄤ簻闁瑰搫绉堕ˇ锔锯偓娈垮枛閻忔繈鍩為幋锕€鐓¢柛鈩冾殘娴狀垶姊洪崨濠庣劶闁告洦鍙庡ú鍛婁繆閵堝繒鍒伴柛鐕佸灦瀹曟劙宕归锝呭伎濠碘槅鍨抽崢褎绂嶆ィ鍐╁€垫慨妯煎亾鐎氾拷

约束被 DB2 Universal Database(TM)(DB2 UDB)用来对数据实施业务规则。本文描述了下列类型的约束:
非空(NOT NULL)
惟一
主键
外键
表检查
此外,还有另一种名为 信息约束(informational constraint)的约束。与上面所列的这五种约束类型不同的是,信息约束不是由数据库管理器实施的,但是 SQL 编译器可用它来提高查询性能。在这篇文章中,我将只关注上面所列的这几类约束,而不会讨论信息约束。
您可以在创建一个新表时定义一个或多个 DB2 UDB 约束,也可以稍后通过更改表来定义它们。CREATE TABLE 语句是十分复杂的;所以尽管实际上其选项中只有一小部分是用于定义约束的,但是当在语法图( 图 1和 图 2)中进行查看时,那些选项本身看上去就相当复杂。通过 DB2 Control Center 可使约束管理更简单、方便。
图 1. CREATE TABLE 语句的部分语法,显示了用于定义约束的子句
查看原图(大图)
约束定义与它们所应用的数据库相关联,并存储在数据库目录中( 表 1)。您可以查询数据库目录来检索并查看该信息。您可以从命令行直接进行(请记住要首先建立数据库连接),同样,您会发现通过 Control Center 来访问这些信息会更方便。
可将所创建的约束像对待其他数据库对象一样进行处理。它们具有名称和关联模式(creator ID),并且在有些情况下还能被撤销(删除)。
图 2. CREATE TABLE 语句的部分语法,显示了用于定义约束的子句(续)
查看原图(大图)
表 1. 数据库目录中的约束信息。要运行成功,对目录的查询需要建立数据库连接。
目录视图 | 视图列 | 描述 | 查询实例 |
SYSCAT.CHECKS | 为每个表检查约束包含一行记录 | db2 select constname, tabname, text from syscat.checks | |
SYSCAT.COLCHECKS | 为表检查约束所引用的每一列包含一行记录 | db2 select constname, tabname, colname, usage from syscat.colchecks | |
SYSCAT.COLUMNS | NULLS | 指明一列是可为空(Y)还是不可为空(N) | db2 select tabname, colname, nulls from syscat.columns where tabschema = 'MELNYK' and nulls = 'N' |
SYSCAT.CONSTDEP | 为某些其他对象上的约束的每个依赖性包含一行记录 | db2 select constname, tabname, btype, bname from syscat.constdep | |
SYSCAT.INDEXES | 为每个索引包含一行记录 | db2 select tabname, uniquerule, made_unique, system_required from syscat.indexes where tabschema = 'MELNYK' | |
SYSCAT.KEYCOLUSE | 为惟一、主键或外键约束定义的键中所包含的每个列包含一行记录 | db2 select constname, tabname, colname, colseq from syscat.keycoluse | |
SYSCAT.REFERENCES | 为每个参照约束包含一行记录 | db2 select constname, tabname, refkeyname, reftabname, colcount, deleterule, updaterule from syscat.references | |
SYSCAT.TABCONST | 为每个惟一(U)、主键(P)、外键(F)或表检查(K)约束包含一行记录 | db2 select constname, tabname, type from syscat.tabconst | |
SYSCAT.TABLES | PARENTS | 该表的父表数目(该表在其中充当子表的参照约束数目) | db2 "select tabname, parents from syscat.tables where parents > 0" |
SYSCAT.TABLES | CHILDREN | 该表的子表数目(该表在其中充当父表的参照约束数目) | db2 "select tabname, children from syscat.tables where children > 0" |
SYSCAT.TABLES | SELFREFS | 该表的自引用参照约束数目(该表在其中既充当父表又充当子表的参照约束数目) | db2 "select tabname, selfrefs from syscat.tables where selfrefs > 0" |
SYSCAT.TABLES | KEYUNIQUE | 在该表上所定义的惟一约束(除了主键)的数目 | db2 "select tabname, keyunique from syscat.tables where keyunique > 0" |
SYSCAT.TABLES | CHECKCOUNT | 在该表上所定义的检查约束的数目 | db2 "select tabname, checkcount from syscat.tables where checkcount > 0" |
“不能为空值!” - 非空约束
非空约束(NOT NULL constraint)防止向一列添加空值。这就确保了该列在表中每一行都存在一个有意义的值。例如,SAMPLE 数据库中 EMPLOYEE 表的定义包括 LASTNAME VARCHAR(15) NOT NULL ,这就确保每行都将包含一个雇员的姓。
要判断一列是否可为空,您可以查阅该表的数据定义语言(DDL)(可通过调用 db2look 工具来生成);您也可以使用 DB2 Control Center( 图 3和 图 4);或者您还可以查询数据库目录( 清单 1)。
图 3. DB2 Control Center 的内容窗格中显示了在其对象树中选中的关联了特定数据库的表。该列表是在 melnyk 模式上筛选的。
查看原图(大图)
DB2 Control Center 让您方便地访问诸如表这样的数据库对象。图 3 显示了 SAMPLE 数据库中的用户表。当在对象树中选中 Tables 时,它们就会出现在其内容窗格中。如果选择 EMPLOYEE 表,我们可以打开 Alter Table 窗口来查看表定义,包括列属性(图 4)。
图 4. Alter Table 窗口提供了一个方便方式来查看表属性。
查看原图(大图)
清单 1. 查询数据库目录以判断哪些数据库列可为空
db2 select tabname, colname, nulls
from syscat.columns
where tabschema = 'MELNYK' and nulls = 'N'
“仅单独存在” - 惟一约束
惟一约束(unique constraint)防止一个值在表中的特定列里出现不止一次。它还防止一组值在特定的一组列里出现不止一次。必须将惟一约束中所引用的列定义为非空(NOT NULL)。可在 CREATE TABLE 语句中使用 UNIQUE 子句(图 1 和 图 2)或者在如下的 altER TABLE 语句中定义惟一约束。
清单 2. 创建惟一约束。除了 ORG_TEMP 中的 LOCATION 列不能为空且在其上定义了惟一约束之外,ORG_TEMP 表与 SAMPLE 数据库中的 ORG 表是相同的。
db2 create table org_temp (
deptnumb smallint not null,
deptname varchar(14),
manager smallint,
division varchar(10),
location varchar(13)
not null)
db2 alter table org_temp
add unique (location)
db2 insert into org_temp
values (10, 'Head Office', 160, 'Corporate', '
New York')
DB20000I The SQL command completed successfully.
db2 insert into org_temp
values (15, 'New England', 50, 'Eastern', '
New York')
SQL0803N One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains
table "MELNYK.ORG_TEMP" from having duplicate rows for those columns.
SQLSTATE=23505
惟一约束通过防止无意的复制有助于确保数据的完整性。本例中,它防止插入第二条指定 New York 为该组织某部门位置的记录。惟一约束是通过惟一索引来实施的。
![]() |
|
“头号人物!” - 主键约束
主键约束(primary key constraint)确保了表中构成主键的一列或一组列的所有值是惟一的。主键用于识别表中的特定行。每个表只能有一个主键,但可以有几个惟一键。主键约束是惟一约束的特例,它是通过主索引来实施的。
必须将主键约束中所引用的列定义为非空(NOT NULL)。可在 CREATE TABLE 语句中使用 PRIMARY KEY 子句( 图 1和 图 2)或者在如下的 altER TABLE 语句中定义主键约束。
清单 3. 创建主键约束。EMPLOYEE 表中的 EMPNO 列不能为空,并可在其上定义主键约束。
db2 alter table employee
add primary key (empno)
您也可以使用 DB2 Control Center 来定义表上的主键约束( 图 5)。
图 5. Alter Table 窗口提供了一个方便方式来定义表上的主键约束。从 available columns 的列表中选择一个或多个列并单击按钮以将选中的列名移至 primary key columns 列表中。选中的列必须不可为空。
查看原图(大图)
“都是相关的!” - 外键约束
外键约束(foreign key constraint)有时候称作参照约束。 参照完整性(referential integrity)被定义为“数据库的所有外键值都是有效的状态”。那外键又是什么呢? 外键(foreign key)是指表中的一列或一组列,其值必须至少匹配其父表中一行的一个主键或惟一键值。这真正意味着什么呢?它实际上并非如听起来那般复杂。简单来说,它意味着如果表(T2)中的一列(C2)存在值匹配另一个表(T1)中的一列(C1)的值, 并且 C1 是 T1 的主键,那么 C2 就是 T2 中的外键列。将包含了父键(主键或惟一键)的表称为 父表(parent table),而将包含了外键的表称为 子表(dependent table)。让我们来考虑一个实例。
SAMPLE 数据库中的 PROJECT 表有一个称为 RESPEMP 的列。该列中的值表示负责该表中所列的每个项目的雇员编号。RESPEMP 是不能为空值的。因为该列对应了 EMPLOYEE 表中的 EMPNO 列,并且我们知道 EMPNO 是 EMPLOYEE 表的主键,RESPEMP 就可以定义为 PROJECT 表中的外键(清单 4)。这将确保今后对 EMPLOYEE 表进行的删除不会让 PROJECT 表包含“不存在的”项目负责雇员。
可在 CREATE TABLE 语句中使用 FOREIGN KEY 子句(图 1 和图 2)或者在如下的 altER TABLE 语句中定义外键约束。
清单 4. 创建外键约束。
db2 alter table project
add foreign key (respemp)
references employee on delete cascade
REFERENCES 子句指向此参照约束的父表。定义外键约束的语法包括 规则从句(rule-clause),在其中您可以从参照完整性角度告诉 DB2 如何处理 update 或 delete 操作( 图 1)。
将以标准方式处理 Insert 操作,您不能对其进行控制。参照约束的 插入规则(insert rule) 是指外键的插入值必须匹配其父表中的某个父键值。这是有道理的,并且与上述内容一致。如果向 PROJECT 表插入一条新记录,那么该记录必须包含对 EMPLOYEE 表中一个现有记录的引用(通过父-外键关系)。
参照约束的 更新规则(update rule) 是指 外键(foreign key)的更新值必须匹配其父表中的某个父键值,并且当完成 父键(parent key)上的 update 操作时,所有的外键值必须有匹配的父键值。总的来说,这意味着不能存在任何“孤儿”;每个子表必须有一个父表。
参照约束的 删除规则(delete rule) 是当从父表中删除一行时应用的,并且依赖于在定义参照约束时所指定的选项。如果指定了 RESTRICT 或 NO ACTION 子句,就不能删除任何一行。如果指定了 SET NULL 子句,则会将每个可为空的外键列设置为 null。然而,如果在创建参照约束时指定了 CASCADE 选项,那么 delete 操作将会被传播到父表的各子表上。因为已指定这些子表与父表是 删除关联的(delete-connected)。
下列实例说明了这些观点。
清单 5. 演示了外键约束中的更新规则和删除规则。
db2 update employee set empno = '350' where empno = '000190'
DB20000I The SQL command completed successfully.
db2 update employee set empno = '360' where empno = '000150'
SQL0531N The parent key in a parent row of relationship
"MELNYK.PROJECT.SQL040103212526610" cannot be updated. SQLSTATE=23504
db2 "select respemp from project where respemp < '000050' order by respemp"
RESPEMP
-------
000010
000010
000020
000030
000030
db2 delete from employee where empno = '000010'
DB20000I The SQL command completed successfully.
db2 "select respemp from project where respemp < '000050' order by respemp"
RESPEMP
-------
000020
000030
000030
父表(EMPLOYEE)中为“000190”的 EMPNO 值 可以被更改,因为子表(PROJECT)中不存在为“000190”的 RESPEMP 值。然而,对于为“000150”的 EMPNO 值就不是这样的了,它在 PROJECT 表中有匹配的外键值,因而不能被更新。指定了 CASCADE 选项的删除规则确保了当从 EMPLOYEE 表中删除主键值时,删除关联的 PROJECT 表将丢失包含相匹配的外键值的所有记录行。
“检查和再次检查” - 表检查约束
表检查约束(table check constraint)对将要添加到表中的数据实施已定义的限制。例如,一个表检查约束可确保每当在 EMPLOYEE 表中添加或更新电话分机时,雇员的电话分机号码都正好为四位数字。可在 CREATE TABLE 语句中使用 CHECK 子句( 图 1和 图 2)或者在如下的 altER TABLE 语句中定义表检查约束。
清单 6. 创建表检查约束。PHONENO_LENGTH 约束确保向 EMPLOYEE 表添加的电话分机正好为四位数字。
db2 alter table employee
add constraint phoneno_length check (length(rtrim(phoneno)) = 4)
您也可以使用 DB2 Control Center 来定义表检查约束(图 6)。
图 6. Alter Table 窗口提供了一个方便方式来定义一列上的表检查约束。
查看原图(大图)
单击 Add 按钮以定义新约束(将打开 Add Check Constraint 窗口),或者单击 Change 按钮以修改在列表中选中的现有的约束( 图 7)。
图 7. Change Check Constraint 窗口让您修改现有的检查条件。
如果表中的现有行包含违反新约束的值,您就不能创建此表检查约束(图 8)。在适当更新了那些不兼容的值之后,您就可以成功添加或修改此约束了。
图 8. 如果新的表检查约束与表中现有的值不兼容,则会返回一条错误。
![]() |
|
使用 SET INTEGRITY 语句可以打开或者关闭表检查约束。这将非常有用,例如,当在给表加载大型数据的期间优化性能时。清单 7 呈现了一个简单场景,展示了使用 SET INTEGRITY 语句的一种可能方式。本例中,将雇员“000100”的电话分机更新为 123,然后关闭 EMPLOYEE 表的完整性检查。在 EMPLOYEE 表上定义要求电话分机值为 4 位数字的检查约束。创建名为 EMPL_EXCEPT 的异常表;这个新表的定义是 EMPLOYEE 表的镜像。然后打开完整性检查,而违反检查约束的行将被写入异常表中。对这些表的查询将证实有问题的行现在仅存在于异常表中。
清单 7. 使用 SET INTEGRITY 语句来延迟约束的检查。
db2 update employee set phoneno = '123' where empno = '000100'
db2 set integrity for employee off
db2 alter table employee add constraint phoneno_length check (length(rtrim(phoneno)) = 4)
db2 create table empl_except like employee
db2 set integrity for employee immediate checked for exception in employee use empl_except
SQL3602W Check data processing found constraint violations and moved them to
exception tables. SQLSTATE=01603
db2 select empno, lastname, workdept, phoneno from empl_except
EMPNO LASTNAME WORKDEPT PHONENO
------ --------------- -------- -------
000100 SPENSER E21 123
1 record(s) selected.
结束语
我们已经探索了 DB2 Universal Database 所支持的不同类型的约束:非空(NOT NULL)约束、惟一约束、主键约束、外键(参照)约束以及表检查约束。我们展示了 DB2 UDB 是如何使用约束来对数据实施业务规则以及帮助维护数据库完整性的。我们还讲解了如何使用命令行和 DB2 Control Center(和如何查询数据库目录)来有效地管理约束。
- ››db2 对float类型取char后显示科学计数法
- ››DB2中出现SQL1032N错误现象时的解决办法
- ››DB2 锁升级示例
- ››db2诊断系列之---定位锁等待问题
- ››db2 命令选项解释
- ››DB2 最佳实践: 使用 DB2 pureXML 管理 XML 数据的...
- ››DB2 9.5 SQL Procedure Developer 认证考试 735 准...
- ››DB2 9.5 SQL Procedure Developer 认证考试 735 准...
- ››DB2 9.5 SQL Procedure Developer 认证考试 735 准...
- ››DB2 基础: 表空间和缓冲池
- ››DB2 XML 编程,第 1 部分: 理解 XML 数据模型
- ››DB2 pureScale 实战
更多精彩
赞助商链接