約束被 DB2 Universal Database(TM)(DB2 UDB)用來(lái)對(duì)數(shù)據(jù)實(shí)施業(yè)務(wù)規(guī)則。本文描述了下列類(lèi)型的約束：
非空(NOT NULL)、惟一、主鍵、外鍵、表檢查。此外，還有另一種名為信息約束(informational constraint)的約束。與上面所列的這五種約束類(lèi)型不同的是，信息約束不是由數(shù)據(jù)庫(kù)管理器實(shí)施的，但是 SQL 編譯器可用它來(lái)提高查詢(xún)性能。在這篇文章中，我將只關(guān)注上面所列的這幾類(lèi)約束，而不會(huì)討論信息約束。
您可以在創(chuàng)建一個(gè)新表時(shí)定義一個(gè)或多個(gè) DB2 UDB 約束，也可以稍后通過(guò)更改表來(lái)定義它們。CREATE TABLE 語(yǔ)句是十分復(fù)雜的;所以盡管實(shí)際上其選項(xiàng)中只有一小部分是用于定義約束的，但是當(dāng)在語(yǔ)法圖(圖1和 圖2)中進(jìn)行查看時(shí)，那些選項(xiàng)本身看上去就相當(dāng)復(fù)雜。通過(guò) DB2 Control Center 可使約束管理更簡(jiǎn)單、方便。
圖 1. CREATE TABLE 語(yǔ)句的部分語(yǔ)法，顯示了用于定義約束的子句
約束定義與它們所應(yīng)用的數(shù)據(jù)庫(kù)相關(guān)聯(lián)，并存儲(chǔ)在數(shù)據(jù)庫(kù)目錄中(表1)。您可以查詢(xún)數(shù)據(jù)庫(kù)目錄來(lái)檢索并查看該信息。您可以從命令行直接進(jìn)行(請(qǐng)記住要首先建立數(shù)據(jù)庫(kù)連接)，同樣，您會(huì)發(fā)現(xiàn)通過(guò) Control Center 來(lái)訪問(wèn)這些信息會(huì)更方便。
可將所創(chuàng)建的約束像對(duì)待其他數(shù)據(jù)庫(kù)對(duì)象一樣進(jìn)行處理。它們具有名稱(chēng)和關(guān)聯(lián)模式(creator ID)，并且在有些情況下還能被撤銷(xiāo)(刪除)。
圖 2. CREATE TABLE 語(yǔ)句的部分語(yǔ)法，顯示了用于定義約束的子句(續(xù))
表1. 數(shù)據(jù)庫(kù)目錄中的約束信息。要運(yùn)行成功，對(duì)目錄的查詢(xún)需要建立數(shù)據(jù)庫(kù)連接。 目錄視圖 視圖列 描述 查詢(xún)實(shí)例
SYSCAT.CHECKS為每個(gè)表檢查約束包含一行記錄db2 select constname, tabname, text from syscat.checks
SYSCAT.COLCHECKS為表檢查約束所引用的每一列包含一行記錄db2 select constname, tabname, colname, usage from syscat.colchecks
SYSCAT.COLUMNSNULLS指明一列是可為空(Y)還是不可為空(N)db2 select tabname, colname, nulls from syscat.columns where tabschema = ''MELNYK'' and nulls = ''N''
SYSCAT.CONSTDEP為某些其他對(duì)象上的約束的每個(gè)依賴(lài)性包含一行記錄db2 select constname, tabname, btype, bname from syscat.constdep
SYSCAT.INDEXES為每個(gè)索引包含一行記錄db2 select tabname, uniquerule, made_unique, system_required from syscat.indexes where tabschema = ''MELNYK''
SYSCAT.KEYCOLUSE為惟一、主鍵或外鍵約束定義的鍵中所包含的每個(gè)列包含一行記錄db2 select constname, tabname, colname, colseq from syscat.keycoluse
SYSCAT.REFERENCES為每個(gè)參照約束包含一行記錄db2 select constname, tabname, refkeyname, reftabname, colcount, deleterule, updaterule from syscat.references
SYSCAT.TABCONST為每個(gè)惟一(U)、主鍵(P)、外鍵(F)或表檢查(K)約束包含一行記錄db2 select constname, tabname, type from syscat.tabconst
SYSCAT.TABLESPARENTS該表的父表數(shù)目(該表在其中充當(dāng)子表的參照約束數(shù)目)db2 "select tabname, parents from syscat.tables where parents > 0"
SYSCAT.TABLESCHILDREN該表的子表數(shù)目(該表在其中充當(dāng)父表的參照約束數(shù)目)db2 "select tabname, children from syscat.tables where children > 0"
SYSCAT.TABLESSELFREFS該表的自引用參照約束數(shù)目(該表在其中既充當(dāng)父表又充當(dāng)子表的參照約束數(shù)目)db2 "select tabname, selfrefs from syscat.tables where selfrefs > 0"
SYSCAT.TABLESKEYUNIQUE在該表上所定義的惟一約束(除了主鍵)的數(shù)目db2 "select tabname, keyunique from syscat.tables where keyunique > 0"
SYSCAT.TABLESCHECKCOUNT在該表上所定義的檢查約束的數(shù)目db2 "select tabname, checkcount from syscat.tables where checkcount > 0"
“不能為空值!” - 非空約束
非空約束(NOT NULL constraint)防止向一列添加空值。這就確保了該列在表中每一行都存在一個(gè)有意義的值。例如，SAMPLE 數(shù)據(jù)庫(kù)中 EMPLOYEE 表的定義包括 LASTNAME VARCHAR(15) NOT NULL ，這就確保每行都將包含一個(gè)雇員的姓。
要判斷一列是否可為空，您可以查閱該表的數(shù)據(jù)定義語(yǔ)言(DDL)(可通過(guò)調(diào)用 db2look 工具來(lái)生成);您也可以使用 DB2 Control Center(圖3和圖4);或者您還可以查詢(xún)數(shù)據(jù)庫(kù)目錄(清單1)。
圖 3. DB2 Control Center 的內(nèi)容窗格中顯示了在其對(duì)象樹(shù)中選中的關(guān)聯(lián)了特定數(shù)據(jù)庫(kù)的表。該列表是在 melnyk 模式上篩選的。
DB2 Control Center 讓您方便地訪問(wèn)諸如表這樣的數(shù)據(jù)庫(kù)對(duì)象。圖 3 顯示了 SAMPLE 數(shù)據(jù)庫(kù)中的用戶(hù)表。當(dāng)在對(duì)象樹(shù)中選中 Tables 時(shí)，它們就會(huì)出現(xiàn)在其內(nèi)容窗格中。如果選擇 EMPLOYEE 表，我們可以打開(kāi) Alter Table 窗口來(lái)查看表定義，包括列屬性(圖 4)。
圖 4. Alter Table 窗口提供了一個(gè)方便方式來(lái)查看表屬性。
清單 1. 查詢(xún)數(shù)據(jù)庫(kù)目錄以判斷哪些數(shù)據(jù)庫(kù)列可為空
db2 select tabname, colname, nulls
from syscat.columns
where tabschema = ''MELNYK'' and nulls = ''N''
“僅單獨(dú)存在” - 惟一約束
惟一約束(unique constraint)防止一個(gè)值在表中的特定列里出現(xiàn)不止一次。它還防止一組值在特定的一組列里出現(xiàn)不止一次。必須將惟一約束中所引用的列定義為非空(NOT NULL)。可在 CREATE TABLE 語(yǔ)句中使用 UNIQUE 子句(圖1和圖2)或者在如下的 altER TABLE 語(yǔ)句中定義惟一約束。
清單 2. 創(chuàng)建惟一約束。除了 ORG_TEMP 中的 LOCATION 列不能為空且在其上定義了惟一約束之外，ORG_TEMP 表與 SAMPLE 數(shù)據(jù)庫(kù)中的 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
惟一約束通過(guò)防止無(wú)意的復(fù)制有助于確保數(shù)據(jù)的完整性。本例中，它防止插入第二條指定 New York 為該組織某部門(mén)位置的記錄。惟一約束是通過(guò)惟一索引來(lái)實(shí)施的。
“頭號(hào)人物!” - 主鍵約束
主鍵約束(primary key constraint)確保了表中構(gòu)成主鍵的一列或一組列的所有值是惟一的。主鍵用于識(shí)別表中的特定行。每個(gè)表只能有一個(gè)主鍵，但可以有幾個(gè)惟一鍵。主鍵約束是惟一約束的特例，它是通過(guò)主索引來(lái)實(shí)施的。
必須將主鍵約束中所引用的列定義為非空(NOT NULL)。可在 CREATE TABLE 語(yǔ)句中使用 PRIMARY KEY 子句(圖1和圖2)或者在如下的 altER TABLE 語(yǔ)句中定義主鍵約束。
清單 3. 創(chuàng)建主鍵約束。EMPLOYEE 表中的 EMPNO 列不能為空，并可在其上定義主鍵約束。
db2 alter table employee
add primary key (empno)
您也可以使用 DB2 Control Center 來(lái)定義表上的主鍵約束(圖5)。
圖 5. Alter Table 窗口提供了一個(gè)方便方式來(lái)定義表上的主鍵約束。從 available columns 的列表中選擇一個(gè)或多個(gè)列并單擊按鈕以將選中的列名移至 primary key columns 列表中。選中的列必須不可為空。
“都是相關(guān)的!” - 外鍵約束
外鍵約束(foreign key constraint)有時(shí)候稱(chēng)作參照約束。 參照完整性(referential integrity)被定義為“數(shù)據(jù)庫(kù)的所有外鍵值都是有效的狀態(tài)”。那外鍵又是什么呢? 外鍵(foreign key)是指表中的一列或一組列，其值必須至少匹配其父表中一行的一個(gè)主鍵或惟一鍵值。這真正意味著什么呢?它實(shí)際上并非如聽(tīng)起來(lái)那般復(fù)雜。簡(jiǎn)單來(lái)說(shuō)，它意味著如果表(T2)中的一列(C2)存在值匹配另一個(gè)表(T1)中的一列(C1)的值， 并且 C1 是 T1 的主鍵，那么 C2 就是 T2 中的外鍵列。將包含了父鍵(主鍵或惟一鍵)的表稱(chēng)為 父表(parent table)，而將包含了外鍵的表稱(chēng)為 子表(dependent table)。讓我們來(lái)考慮一個(gè)實(shí)例。
SAMPLE 數(shù)據(jù)庫(kù)中的 PROJECT 表有一個(gè)稱(chēng)為 RESPEMP 的列。該列中的值表示負(fù)責(zé)該表中所列的每個(gè)項(xiàng)目的雇員編號(hào)。RESPEMP 是不能為空值的。因?yàn)樵摿袑?duì)應(yīng)了 EMPLOYEE 表中的 EMPNO 列，并且我們知道 EMPNO 是 EMPLOYEE 表的主鍵，RESPEMP 就可以定義為 PROJECT 表中的外鍵(清單 4)。這將確保今后對(duì) EMPLOYEE 表進(jìn)行的刪除不會(huì)讓 PROJECT 表包含“不存在的”項(xiàng)目負(fù)責(zé)雇員。
可在 CREATE TABLE 語(yǔ)句中使用 FOREIGN KEY 子句(圖 1 和圖 2)或者在如下的 altER TABLE 語(yǔ)句中定義外鍵約束。
清單 4. 創(chuàng)建外鍵約束。
db2 alter table project
add foreign key (respemp)
references employee on delete cascade
REFERENCES 子句指向此參照約束的父表。定義外鍵約束的語(yǔ)法包括 規(guī)則從句(rule-clause)，在其中您可以從參照完整性角度告訴 DB2 如何處理 update 或 delete 操作(圖1)。
將以標(biāo)準(zhǔn)方式處理 Insert 操作，您不能對(duì)其進(jìn)行控制。參照約束的 插入規(guī)則(insert rule) 是指外鍵的插入值必須匹配其父表中的某個(gè)父鍵值。這是有道理的，并且與上述內(nèi)容一致。如果向 PROJECT 表插入一條新記錄，那么該記錄必須包含對(duì) EMPLOYEE 表中一個(gè)現(xiàn)有記錄的引用(通過(guò)父-外鍵關(guān)系)。
參照約束的 更新規(guī)則(update rule) 是指 外鍵(foreign key)的更新值必須匹配其父表中的某個(gè)父鍵值，并且當(dāng)完成 父鍵(parent key)上的 update 操作時(shí)，所有的外鍵值必須有匹配的父鍵值。總的來(lái)說(shuō)，這意味著不能存在任何“孤兒”;每個(gè)子表必須有一個(gè)父表。
參照約束的 刪除規(guī)則(delete rule) 是當(dāng)從父表中刪除一行時(shí)應(yīng)用的，并且依賴(lài)于在定義參照約束時(shí)所指定的選項(xiàng)。如果指定了 RESTRICT 或 NO ACTION 子句，就不能刪除任何一行。如果指定了 SET NULL 子句，則會(huì)將每個(gè)可為空的外鍵列設(shè)置為 null。然而，如果在創(chuàng)建參照約束時(shí)指定了 CASCADE 選項(xiàng)，那么 delete 操作將會(huì)被傳播到父表的各子表上。因?yàn)橐阎付ㄟ@些子表與父表是 刪除關(guān)聯(lián)的(delete-connected。
下列實(shí)例說(shuō)明了這些觀點(diǎn)。
清單 5. 演示了外鍵約束中的更新規(guī)則和刪除規(guī)則。
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 值 可以被更改，因?yàn)樽颖?PROJECT)中不存在為“000190”的 RESPEMP 值。然而，對(duì)于為“000150”的 EMPNO 值就不是這樣的了，它在 PROJECT 表中有匹配的外鍵值，因而不能被更新。指定了 CASCADE 選項(xiàng)的刪除規(guī)則確保了當(dāng)從 EMPLOYEE 表中刪除主鍵值時(shí)，刪除關(guān)聯(lián)的 PROJECT 表將丟失包含相匹配的外鍵值的所有記錄行。
#p#“檢查和再次檢查” - 表檢查約束
表檢查約束(table check constraint)對(duì)將要添加到表中的數(shù)據(jù)實(shí)施已定義的限制。例如，一個(gè)表檢查約束可確保每當(dāng)在 EMPLOYEE 表中添加或更新電話(huà)分機(jī)時(shí)，雇員的電話(huà)分機(jī)號(hào)碼都正好為四位數(shù)字。可在 CREATE TABLE 語(yǔ)句中使用 CHECK 子句(圖1和圖2)或者在如下的 altER TABLE 語(yǔ)句中定義表檢查約束。
清單 6. 創(chuàng)建表檢查約束。PHONENO_LENGTH 約束確保向 EMPLOYEE 表添加的電話(huà)分機(jī)正好為四位數(shù)字。
db2 alter table employee
add constraint phoneno_length check (length(rtrim(phoneno)) = 4)
您也可以使用 DB2 Control Center 來(lái)定義表檢查約束(圖 6)。
圖 6. Alter Table 窗口提供了一個(gè)方便方式來(lái)定義一列上的表檢查約束。
單擊 Add 按鈕以定義新約束(將打開(kāi) Add Check Constraint 窗口)，或者單擊 Change 按鈕以修改在列表中選中的現(xiàn)有的約束(圖7)。
圖 7. Change Check Constraint 窗口讓您修改現(xiàn)有的檢查條件。
如果表中的現(xiàn)有行包含違反新約束的值，您就不能創(chuàng)建此表檢查約束(圖 8)。在適當(dāng)更新了那些不兼容的值之后，您就可以成功添加或修改此約束了。
圖 8. 如果新的表檢查約束與表中現(xiàn)有的值不兼容，則會(huì)返回一條錯(cuò)誤。
使用 SET INTEGRITY 語(yǔ)句可以打開(kāi)或者關(guān)閉表檢查約束。這將非常有用，例如，當(dāng)在給表加載大型數(shù)據(jù)的期間優(yōu)化性能時(shí)。清單 7 呈現(xiàn)了一個(gè)簡(jiǎn)單場(chǎng)景，展示了使用 SET INTEGRITY 語(yǔ)句的一種可能方式。本例中，將雇員“000100”的電話(huà)分機(jī)更新為 123，然后關(guān)閉 EMPLOYEE 表的完整性檢查。在 EMPLOYEE 表上定義要求電話(huà)分機(jī)值為 4 位數(shù)字的檢查約束。創(chuàng)建名為 EMPL_EXCEPT 的異常表;這個(gè)新表的定義是 EMPLOYEE 表的鏡像。然后打開(kāi)完整性檢查，而違反檢查約束的行將被寫(xiě)入異常表中。對(duì)這些表的查詢(xún)將證實(shí)有問(wèn)題的行現(xiàn)在僅存在于異常表中。
清單 7. 使用 SET INTEGRITY 語(yǔ)句來(lái)延遲約束的檢查。
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.
結(jié)束語(yǔ)
我們已經(jīng)探索了 DB2 Universal Database 所支持的不同類(lèi)型的約束：非空(NOT NULL)約束、惟一約束、主鍵約束、外鍵(參照)約束以及表檢查約束。我們展示了 DB2 UDB 是如何使用約束來(lái)對(duì)數(shù)據(jù)實(shí)施業(yè)務(wù)規(guī)則以及幫助維護(hù)數(shù)據(jù)庫(kù)完整性的。我們還講解了如何使用命令行和 DB2 Control Center(和如何查詢(xún)數(shù)據(jù)庫(kù)目錄)來(lái)有效地管理約束。