約束被 DB2 Universal Database(TM)(DB2 UDB)用來對數(shù)據(jù)實施業(yè)務規(guī)則。本文描述了下列類型的約束：非空(NOT NULL)、惟一、主鍵、外鍵、表檢查。此外，還有另一種名為信息約束(informational constraint)的約束。與上面所列的這五種約束類型不同的是，信息約束不是由數(shù)據(jù)庫管理器實施的，但是 SQL 編譯器可用它來提高查詢性能。在這篇文章中，我將只關(guān)注上面所列的這幾類約束，而不會討論信息約束。

　　您可以在創(chuàng)建一個新表時定義一個或多個 DB2 UDB 約束，也可以稍后通過更改表來定義它們。CREATE TABLE 語句是十分復雜的;所以盡管實際上其選項中只有一小部分是用于定義約束的，但是當在語法圖(圖1和 圖2)中進行查看時，那些選項本身看上去就相當復雜。通過 DB2 Control Center 可使約束管理更簡單、方便。

　　圖 1. CREATE TABLE 語句的部分語法，顯示了用于定義約束的子句

 

　　約束定義與它們所應用的數(shù)據(jù)庫相關(guān)聯(lián)，并存儲在數(shù)據(jù)庫目錄中(表1)。您可以查詢數(shù)據(jù)庫目錄來檢索并查看該信息。您可以從命令行直接進行(請記住要首先建立數(shù)據(jù)庫連接)，同樣，您會發(fā)現(xiàn)通過 Control Center 來訪問這些信息會更方便。

　　可將所創(chuàng)建的約束像對待其他數(shù)據(jù)庫對象一樣進行處理。它們具有名稱和關(guān)聯(lián)模式(creator ID)，并且在有些情況下還能被撤銷(刪除)。

　　圖 2. CREATE TABLE 語句的部分語法，顯示了用于定義約束的子句(續(xù))

 

　　表1. 數(shù)據(jù)庫目錄中的約束信息。要運行成功，對目錄的查詢需要建立數(shù)據(jù)庫連接。 目錄視圖 視圖列 描述 查詢實例

　　SYSCAT.CHECKS為每個表檢查約束包含一行記錄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為某些其他對象上的約束的每個依賴性包含一行記錄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.TABLESPARENTS該表的父表數(shù)目(該表在其中充當子表的參照約束數(shù)目)db2 "select tabname, parents from syscat.tables where parents > 0"

　　SYSCAT.TABLESCHILDREN該表的子表數(shù)目(該表在其中充當父表的參照約束數(shù)目)db2 "select tabname, children from syscat.tables where children > 0"

　　SYSCAT.TABLESSELFREFS該表的自引用參照約束數(shù)目(該表在其中既充當父表又充當子表的參照約束數(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)防止向一列添加空值。這就確保了該列在表中每一行都存在一個有意義的值。例如，SAMPLE 數(shù)據(jù)庫中 EMPLOYEE 表的定義包括 LASTNAME VARCHAR(15) NOT NULL ，這就確保每行都將包含一個雇員的姓。

　　要判斷一列是否可為空，您可以查閱該表的數(shù)據(jù)定義語言(DDL)(可通過調(diào)用 db2look 工具來生成);您也可以使用 DB2 Control Center(圖3和圖4);或者您還可以查詢數(shù)據(jù)庫目錄(清單1)。

　　圖 3. DB2 Control Center 的內(nèi)容窗格中顯示了在其對象樹中選中的關(guān)聯(lián)了特定數(shù)據(jù)庫的表。該列表是在 melnyk 模式上篩選的。

 

　　DB2 Control Center 讓您方便地訪問諸如表這樣的數(shù)據(jù)庫對象。圖 3 顯示了 SAMPLE 數(shù)據(jù)庫中的用戶表。當在對象樹中選中 Tables 時，它們就會出現(xiàn)在其內(nèi)容窗格中。如果選擇 EMPLOYEE 表，我們可以打開 Alter Table 窗口來查看表定義，包括列屬性(圖 4)。

　　圖 4. Alter Table 窗口提供了一個方便方式來查看表屬性。

 

　　清單 1. 查詢數(shù)據(jù)庫目錄以判斷哪些數(shù)據(jù)庫列可為空

　　db2 select tabname, colname, nulls

　　from syscat.columns

　　where tabschema = ''MELNYK'' and nulls = ''N''

　　“僅單獨存在” - 惟一約束

　　惟一約束(unique constraint)防止一個值在表中的特定列里出現(xiàn)不止一次。它還防止一組值在特定的一組列里出現(xiàn)不止一次。必須將惟一約束中所引用的列定義為非空(NOT NULL)。可在 CREATE TABLE 語句中使用 UNIQUE 子句(圖1和圖2)或者在如下的 altER TABLE 語句中定義惟一約束。

　　清單 2. 創(chuàng)建惟一約束。除了 ORG_TEMP 中的 LOCATION 列不能為空且在其上定義了惟一約束之外，ORG_TEMP 表與 SAMPLE 數(shù)據(jù)庫中的 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

　　惟一約束通過防止無意的復制有助于確保數(shù)據(jù)的完整性。本例中，它防止插入第二條指定 New York 為該組織某部門位置的記錄。惟一約束是通過惟一索引來實施的。

　　“頭號人物!” - 主鍵約束

　　主鍵約束(primary key constraint)確保了表中構(gòu)成主鍵的一列或一組列的所有值是惟一的。主鍵用于識別表中的特定行。每個表只能有一個主鍵，但可以有幾個惟一鍵。主鍵約束是惟一約束的特例，它是通過主索引來實施的。

　　必須將主鍵約束中所引用的列定義為非空(NOT NULL)。可在 CREATE TABLE 語句中使用 PRIMARY KEY 子句(圖1和圖2)或者在如下的 altER TABLE 語句中定義主鍵約束。

　　清單 3. 創(chuàng)建主鍵約束。EMPLOYEE 表中的 EMPNO 列不能為空，并可在其上定義主鍵約束。

　　db2 alter table employee

　　add primary key (empno)

　　您也可以使用 DB2 Control Center 來定義表上的主鍵約束(圖5)。

　　圖 5. Alter Table 窗口提供了一個方便方式來定義表上的主鍵約束。從 available columns 的列表中選擇一個或多個列并單擊按鈕以將選中的列名移至 primary key columns 列表中。選中的列必須不可為空。

 

　　“都是相關(guān)的!” - 外鍵約束

　　外鍵約束(foreign key constraint)有時候稱作參照約束。 參照完整性(referential integrity)被定義為“數(shù)據(jù)庫的所有外鍵值都是有效的狀態(tài)”。那外鍵又是什么呢? 外鍵(foreign key)是指表中的一列或一組列，其值必須至少匹配其父表中一行的一個主鍵或惟一鍵值。這真正意味著什么呢?它實際上并非如聽起來那般復雜。簡單來說，它意味著如果表(T2)中的一列(C2)存在值匹配另一個表(T1)中的一列(C1)的值， 并且 C1 是 T1 的主鍵，那么 C2 就是 T2 中的外鍵列。將包含了父鍵(主鍵或惟一鍵)的表稱為 父表(parent table)，而將包含了外鍵的表稱為 子表(dependent table)。讓我們來考慮一個實例。

　　SAMPLE 數(shù)據(jù)庫中的 PROJECT 表有一個稱為 RESPEMP 的列。該列中的值表示負責該表中所列的每個項目的雇員編號。RESPEMP 是不能為空值的。因為該列對應了 EMPLOYEE 表中的 EMPNO 列，并且我們知道 EMPNO 是 EMPLOYEE 表的主鍵，RESPEMP 就可以定義為 PROJECT 表中的外鍵(清單 4)。這將確保今后對 EMPLOYEE 表進行的刪除不會讓 PROJECT 表包含“不存在的”項目負責雇員。

　　可在 CREATE TABLE 語句中使用 FOREIGN KEY 子句(圖 1 和圖 2)或者在如下的 altER TABLE 語句中定義外鍵約束。

　　清單 4. 創(chuàng)建外鍵約束。

　　db2 alter table project

　　add foreign key (respemp)

　　references employee on delete cascade

　　REFERENCES 子句指向此參照約束的父表。定義外鍵約束的語法包括 規(guī)則從句(rule-clause)，在其中您可以從參照完整性角度告訴 DB2 如何處理 update 或 delete 操作(圖1)。

　　將以標準方式處理 Insert 操作，您不能對其進行控制。參照約束的 插入規(guī)則(insert rule) 是指外鍵的插入值必須匹配其父表中的某個父鍵值。這是有道理的，并且與上述內(nèi)容一致。如果向 PROJECT 表插入一條新記錄，那么該記錄必須包含對 EMPLOYEE 表中一個現(xiàn)有記錄的引用(通過父-外鍵關(guān)系)。

　　參照約束的 更新規(guī)則(update rule) 是指 外鍵(foreign key)的更新值必須匹配其父表中的某個父鍵值，并且當完成 父鍵(parent key)上的 update 操作時，所有的外鍵值必須有匹配的父鍵值。總的來說，這意味著不能存在任何“孤兒”;每個子表必須有一個父表。

　　參照約束的 刪除規(guī)則(delete rule) 是當從父表中刪除一行時應用的，并且依賴于在定義參照約束時所指定的選項。如果指定了 RESTRICT 或 NO ACTION 子句，就不能刪除任何一行。如果指定了 SET NULL 子句，則會將每個可為空的外鍵列設置為 null。然而，如果在創(chuàng)建參照約束時指定了 CASCADE 選項，那么 delete 操作將會被傳播到父表的各子表上。因為已指定這些子表與父表是 刪除關(guān)聯(lián)的(delete-connected。

　　下列實例說明了這些觀點。

　　清單 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 值 可以被更改，因為子表(PROJECT)中不存在為“000190”的 RESPEMP 值。然而，對于為“000150”的 EMPNO 值就不是這樣的了，它在 PROJECT 表中有匹配的外鍵值，因而不能被更新。指定了 CASCADE 選項的刪除規(guī)則確保了當從 EMPLOYEE 表中刪除主鍵值時，刪除關(guān)聯(lián)的 PROJECT 表將丟失包含相匹配的外鍵值的所有記錄行。

　　#p#“檢查和再次檢查” - 表檢查約束

　　表檢查約束(table check constraint)對將要添加到表中的數(shù)據(jù)實施已定義的限制。例如，一個表檢查約束可確保每當在 EMPLOYEE 表中添加或更新電話分機時，雇員的電話分機號碼都正好為四位數(shù)字。可在 CREATE TABLE 語句中使用 CHECK 子句(圖1和圖2)或者在如下的 altER TABLE 語句中定義表檢查約束。

　　清單 6. 創(chuàng)建表檢查約束。PHONENO_LENGTH 約束確保向 EMPLOYEE 表添加的電話分機正好為四位數(shù)字。

　　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 按鈕以修改在列表中選中的現(xiàn)有的約束(圖7)。

　　圖 7. Change Check Constraint 窗口讓您修改現(xiàn)有的檢查條件。

 

　　如果表中的現(xiàn)有行包含違反新約束的值，您就不能創(chuàng)建此表檢查約束(圖 8)。在適當更新了那些不兼容的值之后，您就可以成功添加或修改此約束了。

　　圖 8. 如果新的表檢查約束與表中現(xiàn)有的值不兼容，則會返回一條錯誤。

 

　　使用 SET INTEGRITY 語句可以打開或者關(guān)閉表檢查約束。這將非常有用，例如，當在給表加載大型數(shù)據(jù)的期間優(yōu)化性能時。清單 7 呈現(xiàn)了一個簡單場景，展示了使用 SET INTEGRITY 語句的一種可能方式。本例中，將雇員“000100”的電話分機更新為 123，然后關(guān)閉 EMPLOYEE 表的完整性檢查。在 EMPLOYEE 表上定義要求電話分機值為 4 位數(shù)字的檢查約束。創(chuàng)建名為 EMPL_EXCEPT 的異常表;這個新表的定義是 EMPLOYEE 表的鏡像。然后打開完整性檢查，而違反檢查約束的行將被寫入異常表中。對這些表的查詢將證實有問題的行現(xiàn)在僅存在于異常表中。

　　清單 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.

　　結(jié)束語

　　我們已經(jīng)探索了 DB2 Universal Database 所支持的不同類型的約束：非空(NOT NULL)約束、惟一約束、主鍵約束、外鍵(參照)約束以及表檢查約束。我們展示了 DB2 UDB 是如何使用約束來對數(shù)據(jù)實施業(yè)務規(guī)則以及幫助維護數(shù)據(jù)庫完整性的。我們還講解了如何使用命令行和 DB2 Control Center(和如何查詢數(shù)據(jù)庫目錄)來有效地管理約束。