 |
級(jí)別: 初級(jí)
Jason Zhang (jasonz@ca.ibm.com), 軟件開(kāi)發(fā)人員, IBM
2004 年 9 月 01 日
如何將代理鍵（surrogate key）用作 DB2 UDB 中的主鍵？代理鍵是生成惟一序列號(hào)的一種有效方法。從本文中可以了解三種實(shí)現(xiàn)：傳統(tǒng)方法，使用鍵管理器，以及使用新的 DB2 UDB 特性。
簡(jiǎn)介
使用代理鍵解決方案是為了發(fā)現(xiàn)一種生成惟一序列號(hào)的有效方法。本文描述了三種實(shí)現(xiàn)：
- 使用傳統(tǒng)方法。
- 使用鍵管理器。
- 使用 DB2 UDB 特性。
代理鍵也叫 內(nèi)鍵（internal key）。當(dāng)創(chuàng)建一個(gè)表時(shí)，可以添加一個(gè)額外的列作為代理鍵。這個(gè)列應(yīng)該是 NOT NULL，并且沒(méi)有商業(yè)意義。可以將該代理列指定為主鍵列。例如可以有一個(gè)數(shù)字代理列。代理鍵的值從某一個(gè)數(shù)字開(kāi)始，例如 "1"，以這個(gè)數(shù)字作為該列在表中第一行的值，之后的每一行中該列的值都按 1 遞增。
例如，如果我們有表 EMPLOYEE：
CREATE TABLE EMPLOYEE ( FIRSTNAME CHAR(64),
LASTNAME CHAR(64),
SALARY DECIMAL(10, 2))
|
那么可以添加一個(gè)代理鍵列 SERIALNUMBER，并將其指定為主鍵列。這樣，這個(gè)表的定義就變?yōu)椋?/p>
CREATE TABLE EMPLOYEE ( SERIALNUMBER BIGINT NOT NULL,
FIRSTNAME CHAR(64),
LASTNAME CHAR(64),
SALARY DECIMAL(10, 2),
PRIMARY KEY (SERIALNUMBER))
|
那么，怎樣將惟一的值賦給每一行的 SERIALNUMBER 列呢？首先需要為代理鍵生成惟一的值。下面我將討論三種可行的解決方案。
使用傳統(tǒng)方法
解決方案的思想
傳統(tǒng)方法是使用簡(jiǎn)單的 SQL 或觸發(fā)器生成惟一的值。
示例
以表 EMPLOYEE 為例。您可以在 INSERT 語(yǔ)句中實(shí)現(xiàn)代理鍵生成函數(shù)：
INSERT INTO EMPLOYEE (SERIALNUMBER, FIRSTNAME, LASTNAME,
SALARY) VALUES ((SELECT MAX(SERIALNUMBER) FROM EMPLOYEE)+1,
‘John’, ‘Smith’, 999.99)
|
SQL 語(yǔ)句 " (SELECT MAX(SERIALNUMBER) FROM EMPLOYEE)+1 " 將找出最大的 SERIALNUMBER 并將其加 1，這樣新行就有一個(gè)惟一的 SERIALNUMBER。
這樣做存在的一個(gè)問(wèn)題是，當(dāng)將第一行插入表中時(shí)，可能會(huì)得到如下錯(cuò)誤：
SQL0407N Assignment of a NULL value to a NOT NULL.
SQLSTATE=23502.
|
得到上述錯(cuò)誤的原因是，當(dāng)表為空時(shí)，" SELECT MAX(SERIALNUMBER) FROM EMPLOYEE " 的返回為 NULL。因此，我們必須使用 COALESCE() 來(lái)處理這個(gè)問(wèn)題：
INSERT INTO EMPLOYEE (SERIALNUMBER, FIRSTNAME, LASTNAME,
SALARY) VALUES (COALESCE((SELECT MAX(SERIALNUMBER)
FROM EMPLOYEE), 0)+1, ‘John’, ‘Smith’, 999.99)
|
另一種傳統(tǒng)方法是使用觸發(fā)器來(lái)生成代理鍵：
CREATE TRIGGER AUTOSURROGATEKEY NO CASCADE
BEFORE INSERT ON EMPLOYEE
REFERENCING NEW AS N
FOR EACH ROW
MODE DB2SQL BEGIN ATOMIC
SET N.SERIALNUMBER = COALESCE((SELECT MAX(SERIALNUMBER)
FROM EMPLOYEE), 0)+1;
END
|
優(yōu)點(diǎn)及問(wèn)題
傳統(tǒng)方法易于理解，而且容易在所有系統(tǒng)上實(shí)現(xiàn)。但是，這種實(shí)現(xiàn)實(shí)際上會(huì)導(dǎo)致事務(wù)處理系統(tǒng)中出現(xiàn)并發(fā)問(wèn)題。因?yàn)樵搶?shí)現(xiàn)只允許一次執(zhí)行一條 INSERT 操作。
因此，在獲得最大的 SERIALNUMBER 之前，" SELECT MAX(SERIALNUMBER) FROM EMPLOYEE " 必須等待其他所有事務(wù)完成對(duì)表 EMPLOYEE 的 INSERT 或 UPDATE 操作。例如，如果有兩個(gè)事務(wù)正在對(duì) EMPLOYEE 表進(jìn)行 INSERT 操作，那么其中有一個(gè)事務(wù)會(huì)被另一個(gè)事務(wù)阻塞。顯然，這種“逐次插入”的解決方案不適合多用戶(hù)的事務(wù)處理系統(tǒng)。
使用鍵管理器
解決方案的思想
很多大型的應(yīng)用程序使用鍵管理器方法維護(hù)所有表的代理鍵。鍵管理器可以是一個(gè)助手類(lèi)。每當(dāng)需要向表插入一個(gè)行時(shí)，便可以調(diào)用鍵管理器生成新的鍵值，然后將獲得的鍵值插入新行。
示例
首先，需要?jiǎng)?chuàng)建表 KEYS 來(lái)記錄每個(gè)表的當(dāng)前代理鍵值。鍵管理器類(lèi)將使用該表生成新鍵值。
CREATE TABLE KEYS ( TABLENAME CHAR(256),
COLNUMNAME CHAR(256),
SURROGATEKEYVALUE BIGINT,
INCREMENT BIGINT,
PRIMARY KEY(TABLENAME, COLNUMNAME));
|
第二，將新表（例如表 EMPLOYEE）注冊(cè)到表 KEYS 中。
INSERT INTO KEYS (TABLENAME, COLUMNNAME, SURROGATEKEYVALUE,
INCREMENT) VALUES (‘EMPLOYEE’, ‘SERIALNUMBER’, 0, 1);
|
第三，編寫(xiě) KeyManger 類(lèi)來(lái)維護(hù)每個(gè)已注冊(cè)表的代理鍵。KeyManager 將提供兩個(gè)方法：
/**
*Intialize the KeyManger
*/
KeyManager.singleton();
/**
*Return the unique surrogate key value according to the input table
*name and column name.
*/
KeyManager. GetSurrogateKey(String tableName, String columnName);
|
要查看更詳細(xì)的 KeyManger 的代碼，請(qǐng)參考附錄。
第四，調(diào)用 KeyManger 來(lái)獲得主鍵值：
…
KeyManager km = KeyManager.singleton();
Long surrogateKey = km.getSurrogateKey("EMPLOYEE", "SERIALNUMBER");
…
|
優(yōu)點(diǎn)和問(wèn)題
顯然，鍵管理器是模塊化設(shè)計(jì)的一個(gè)很好的例子。鍵管理器封裝了代理鍵生成函數(shù)。這種實(shí)現(xiàn)也易于定制。您可以在 KEYS 表中為 SURROGATEKEYVALUE 或 INCREMENT 指定不同的值，以得到不同的代理鍵。而且，這種實(shí)現(xiàn)可以在大多數(shù)數(shù)據(jù)庫(kù)系統(tǒng)上實(shí)施。但是，為了進(jìn)行維護(hù)，需要一個(gè)單獨(dú)的表和編寫(xiě)代碼。所以，這種方法更適合于大型的跨數(shù)據(jù)庫(kù)系統(tǒng)。
使用 DB2 UDB 特性
DB2 UDB 提供了三種方法來(lái)生成惟一值。您可以使用這些方法來(lái)實(shí)現(xiàn)代理鍵。
- DB2 UDB Version 6.1 中的 GENERATE_UNIQUE() SQL 函數(shù)。
- DB2 UDB Version 7.2 中 CREATE TABLE 語(yǔ)句的 IDENTITY 選項(xiàng)。
- DB2 UDB Version 7.2 中的 SEQUENCE 對(duì)象。
GENERATE_UNIQUE()
解決方案的思想
GENERATE_UNIQUE() 最初是在 DB2 UDB Version 6.1 中提供的一個(gè) SQL 函數(shù)。該函數(shù)返回當(dāng)前系統(tǒng)時(shí)間戳。我們可以使用該函數(shù)為代理鍵列生成惟一值。
示例
CREATE TABLE EMPLOYEE ( SERIALNUMBER CHAR(13) FOR BIT
DATA NOT NULL,
FIRSTNAME CHAR(64),
LASTNAME CHAR(64),
SALARY DECIMAL(10, 2),
PRIMARY KEY (SERIALNUMBER))
|
然后可以用下面的 SQL 語(yǔ)句插入一行：
INSERT INTO EMPLOYEE (SERIALNUMBER, FIRSTNAME, LASTNAME,
SALARY) VALUES(GENERATE_UNIQUE(), ‘John’, ‘Smith’, 999.99)
|
優(yōu)點(diǎn)和問(wèn)題
這里需要清楚兩件事情。
首先，當(dāng)多個(gè)事務(wù)在同一時(shí)刻插入行時(shí)，GENERATE_UNIQUE() 可能會(huì)返回相同的時(shí)間戳。在這種情況下，GENERATE_UNIQUE() 不能為每個(gè)事務(wù)生成一個(gè)惟一的返回值，因而這種方法不適合有大量事務(wù)的系統(tǒng)。
第二，一旦系統(tǒng)時(shí)鐘需要向后調(diào)整，那么 GENERATE_UNIQUE() 將可能返回重復(fù)的值。
由于上述限制，我決不會(huì)在生產(chǎn)系統(tǒng)中使用 GENERATE_UNIQUE()。但是，當(dāng)您需要在有限的時(shí)間內(nèi)完成一個(gè)原型時(shí)，這也許是一種選擇。
CREATE TABLE 語(yǔ)句中的 IDENTITY 選項(xiàng)
解決方案的思想
IDENTITY 是 DB2 UDB Version 7.1 和后期版本提供的 CREATE TABLE 語(yǔ)句中的一個(gè)選項(xiàng)。在創(chuàng)建表時(shí)，可以將某個(gè)列指定為 IDENTITY 列。對(duì)于每條 INSERT 語(yǔ)句，DB2 將負(fù)責(zé)為其中的這一列生成一個(gè)惟一的值。
示例
CREATE TABLE EMPLOYEE ( SERIALNUMBER BIGINT NOT NULL
GENERATED ALWAYS AS IDENTITY
(START WITH 1, INCREMENT BY 1),
FIRSTNAME CHAR(64),
LASTNAME CHAR(64),
SALARY DECIMAL(10, 2),
PRIMARY KEY (SERIALNUMBER))
|
然后可以用下面的語(yǔ)句插入一行：
INSERT INTO EMPLOYEE (FIRSTNAME, LASTNAME, SALARY) VALUES
( ‘John’, ‘Smith’, 999.99)
|
INSERT 語(yǔ)句不需要指定 SERIALNUMBER 列的值。DB2 UDB 將根據(jù)列的定義自動(dòng)生成惟一值，即 "GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)"。
優(yōu)點(diǎn)和問(wèn)題
IDENTITY 函數(shù)在大多數(shù)情況下是代理鍵函數(shù)的一個(gè)好的解決方案。DB2 import 和 export 實(shí)用程序也支持 IDENTITY 選項(xiàng)。
然而，在某種情況下，這種解決方案不大方便。在運(yùn)行 INSERT 語(yǔ)句之后，應(yīng)用程序?qū)⒂肋h(yuǎn)都不知道放入了主鍵列中的是什么值。如果應(yīng)用程序必須繼續(xù)向子表插入一個(gè)行，那么它就不得不對(duì)父表運(yùn)行一條 SELECT 語(yǔ)句，以得到主鍵值。不過(guò)，如果這一點(diǎn)對(duì)于您的系統(tǒng)不成問(wèn)題的話，那么使用 IDENTITY 選項(xiàng)是一個(gè)好主意。
SEQUENCE 對(duì)象
解決方案的思想
SEQUENCE 對(duì)象是在 DB2 UDB Version 7.2 中引入的一個(gè)特性。用戶(hù)可以在數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè) SEQUENCE 對(duì)象，就像創(chuàng)建表對(duì)象或視圖對(duì)象一樣，然后從 SEQUENCE 中請(qǐng)求值。DB2 保證用戶(hù)每次可以得到一個(gè)惟一的序列值。
示例
您可以在數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè) SEQUENCE 對(duì)象：
CREATE SEQUENCE EMPSERIAL
AS BIGINT
START WITH 1
INCREMENT BY 1
|
如果有一個(gè)如下所示的 EMPLOYEE 表：
CREATE TABLE EMPLOYEE ( SERIALNUMBER BIGINT NOT NULL,
FIRSTNAME CHAR(64),
LASTNAME CHAR(64),
SALARY DECIMAL(10, 2),
PRIMARY KEY (SERIALNUMBER))
|
那么可以用下面的語(yǔ)句插入一個(gè)行：
INSERT INTO EMPLOYEE ( SERIALNUMBER, FIRSTNAME, LASTNAME,
SALARY) VALUES(NEXTVAL FOR EMPSERIAL, 'John', 'Smith', 99.99)
|
在這里使用 " NEXTVAL FOR EMPSERIAL " 從 SEQUENCE 中獲得惟一值。
您可以使用 " PREVVAL FOR EMPSERIAL " 獲得當(dāng)前連接會(huì)話中最近生成的序列值。應(yīng)用程序就可以知道放入主鍵列中的是什么值，從而繼續(xù)向子表插入一個(gè)行。這里，“在當(dāng)前連接會(huì)話中”這一點(diǎn)很重要，這意味著 "PREVVAL" 將只返回在相同連接會(huì)話中生成的值。
例如，考慮這樣的情況：有兩個(gè)應(yīng)用程序連接到數(shù)據(jù)庫(kù)，并按照如下順序運(yùn)行下面的 SQL 語(yǔ)句。
（假設(shè) SEQUENCE " EMPSERIAL " 的當(dāng)前值是 3）。
應(yīng)用程序 1：
INSERT INTO EMPLOYEE ( SERIALNUMBER, FIRSTNAME, LASTNAME,
SALARY) VALUES(NEXTVAL FOR EMPSERIAL, 'Martin', 'Wong', 1000.00)
|
從 EMPSERIAL 生成的 " NEXTVAL " 是 4。
應(yīng)用程序 2：
INSERT INTO EMPLOYEE ( SERIALNUMBER, FIRSTNAME, LASTNAME,
SALARY) VALUES(NEXTVAL FOR EMPSERIAL, 'Patrick', 'Chan', 99.99)
|
從 EMPSERIAL 生成的 " NEXTVAL " 是 5。
應(yīng)用程序 1：
SELECT PREVVAL FOR EMPSERIAL FROM EMPLOYEE
|
" PREVVAL " 將返回 4，而不是 5。
而且， PREVVAL 和 NEXTVAL 的值不會(huì)受事務(wù)回滾的影響。
例如，假設(shè) SEQUENCE " EMPSERIAL " 的當(dāng)前值是 30。某個(gè)應(yīng)用程序開(kāi)始了一個(gè)事務(wù)：
INSERT INTO EMPLOYEE ( SERIALNUMBER, FIRSTNAME, LASTNAME,
SALARY) VALUES(NEXTVAL FOR EMPSERIAL, 'William', 'Chen', 99.99)
|
執(zhí)行 ROLLBACK 操作。
然后，如果運(yùn)行：
SELECT PREVVAL FOR EMPSERIAL FROM EMPLOYEE
|
則 " PREVVAL " 將返回 31，而不是 30。
優(yōu)點(diǎn)和問(wèn)題
SEQUENCE 是最近 DB2 UDB 為生成惟一值而實(shí)現(xiàn)的函數(shù)。它還有一個(gè)緩存函數(shù)，用于提高性能（要了解詳細(xì)信息，請(qǐng)參閱 IBM DB2 UDB SQL Reference）。該函數(shù)比 IDENTITY 函數(shù)更靈活，因?yàn)樗菙?shù)據(jù)庫(kù)中的一個(gè)獨(dú)立對(duì)象。必要時(shí)候，可以通過(guò)運(yùn)行 ALTER SEQUENCE 語(yǔ)句更改其設(shè)置。
如果系統(tǒng)只在 DB2 UDB 上運(yùn)行，那么 SEQUENCE 也許是最好的解決方案，因?yàn)樗子谑褂茫也幌矜I管理器那樣需要額外的代碼，并且可以隨需求的變化很輕易對(duì)其進(jìn)行更改。
結(jié)束語(yǔ)
本文描述了實(shí)現(xiàn)作為主鍵的代理鍵的三種方法。文中主要討論了如何為代理鍵生成惟一的序列值。
傳統(tǒng)方法適合于簡(jiǎn)單的、單用戶(hù)（非并發(fā)）系統(tǒng)。對(duì)于實(shí)現(xiàn)對(duì)于大型系統(tǒng)和跨平臺(tái)系統(tǒng)，鍵管理器是一個(gè)好選擇。但是，如果項(xiàng)目只在 DB2 UDB 上運(yùn)行的話，可以考慮 DB2 UDB 提供的特性。IDENTITY 和 SEQUENCE 函數(shù)提供了一種容易的、靈活的解決方案。
在創(chuàng)建 IDENTITY 列和 SEQUENCE 對(duì)象時(shí)，可以使用很多選擇。請(qǐng)參閱 IBM DB2 UDB Administration Guide和 IBM DB2 UDB SQL Reference，以獲得完整的細(xì)節(jié)。
附錄
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class KeyManager {
private static KeyManager singleton;
private PreparedStatement getKeyStmt = null;
private PreparedStatement updateKeyStmt = null;
static final String db2Driver = "COM.ibm.db2.jdbc.app.DB2Driver";
static final String db2UrlPfx = "jdbc:db2:";
public KeyManager Singleton() throws ClassNotFoundException,
SQLException{
if (singleton == null) {
if (singleton == null)
singleton = new KeyManager();
}
return singleton;
}
private KeyManager() throws ClassNotFoundException, SQLException{
Class.forName(db2Driver);
Connection connection = DriverManager.getConnection(db2UrlPfx+ "dbName",
"userName", "password");
getKeyStmt = connection.prepareStatement("SELECT SURROGATEKEYVALUE
FROM KEYS WHERE TABLENAME = ? AND COLUMNNAME = ?");
updateKeyStmt = connection.prepareStatement("UPDATE KEYS SET
SURROGATEKEYVALUE = SURROGATEKEYVALUE + INCREMENT WHERE
TABLENAME = ? AND COLUMNNAME = ?");
}
public Long getSurrogateKey(String tableName, String columnName)
throws SQLException{
Long keyValue = null;
getKeyStmt.setString(1, tableName);
getKeyStmt.setString(2, columnName);
updateKeyStmt.setString(1, tableName);
updateKeyStmt.setString(2, columnName);
updateKeyStmt.execute();
ResultSet rs = getKeyStmt.executeQuery();
if (rs.next() == true) {
keyValue = new Long(rs.getLong(1));
}
return keyValue;
}
}
|
參考資料
|