 |
級別: 初級
Jason Zhang (jasonz@ca.ibm.com), 軟件開發人員, IBM
2004 年 9 月 01 日
如何將代理鍵（surrogate key）用作 DB2 UDB 中的主鍵？代理鍵是生成惟一序列號的一種有效方法。從本文中可以了解三種實現：傳統方法，使用鍵管理器，以及使用新的 DB2 UDB 特性。
簡介
使用代理鍵解決方案是為了發現一種生成惟一序列號的有效方法。本文描述了三種實現：
- 使用傳統方法。
- 使用鍵管理器。
- 使用 DB2 UDB 特性。
代理鍵也叫 內鍵（internal key）。當創建一個表時，可以添加一個額外的列作為代理鍵。這個列應該是 NOT NULL，并且沒有商業意義?？梢詫⒃摯砹兄付橹麈I列。例如可以有一個數字代理列。代理鍵的值從某一個數字開始，例如 "1"，以這個數字作為該列在表中第一行的值，之后的每一行中該列的值都按 1 遞增。
例如，如果我們有表 EMPLOYEE：
CREATE TABLE EMPLOYEE ( FIRSTNAME CHAR(64),
LASTNAME CHAR(64),
SALARY DECIMAL(10, 2))
|
那么可以添加一個代理鍵列 SERIALNUMBER，并將其指定為主鍵列。這樣，這個表的定義就變為：
CREATE TABLE EMPLOYEE ( SERIALNUMBER BIGINT NOT NULL,
FIRSTNAME CHAR(64),
LASTNAME CHAR(64),
SALARY DECIMAL(10, 2),
PRIMARY KEY (SERIALNUMBER))
|
那么，怎樣將惟一的值賦給每一行的 SERIALNUMBER 列呢？首先需要為代理鍵生成惟一的值。下面我將討論三種可行的解決方案。
使用傳統方法
解決方案的思想
傳統方法是使用簡單的 SQL 或觸發器生成惟一的值。
示例
以表 EMPLOYEE 為例。您可以在 INSERT 語句中實現代理鍵生成函數：
INSERT INTO EMPLOYEE (SERIALNUMBER, FIRSTNAME, LASTNAME,
SALARY) VALUES ((SELECT MAX(SERIALNUMBER) FROM EMPLOYEE)+1,
‘John’, ‘Smith’, 999.99)
|
SQL 語句 " (SELECT MAX(SERIALNUMBER) FROM EMPLOYEE)+1 " 將找出最大的 SERIALNUMBER 并將其加 1，這樣新行就有一個惟一的 SERIALNUMBER。
這樣做存在的一個問題是，當將第一行插入表中時，可能會得到如下錯誤：
SQL0407N Assignment of a NULL value to a NOT NULL.
SQLSTATE=23502.
|
得到上述錯誤的原因是，當表為空時，" SELECT MAX(SERIALNUMBER) FROM EMPLOYEE " 的返回為 NULL。因此，我們必須使用 COALESCE() 來處理這個問題：
INSERT INTO EMPLOYEE (SERIALNUMBER, FIRSTNAME, LASTNAME,
SALARY) VALUES (COALESCE((SELECT MAX(SERIALNUMBER)
FROM EMPLOYEE), 0)+1, ‘John’, ‘Smith’, 999.99)
|
另一種傳統方法是使用觸發器來生成代理鍵：
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
|
優點及問題
傳統方法易于理解，而且容易在所有系統上實現。但是，這種實現實際上會導致事務處理系統中出現并發問題。因為該實現只允許一次執行一條 INSERT 操作。
因此，在獲得最大的 SERIALNUMBER 之前，" SELECT MAX(SERIALNUMBER) FROM EMPLOYEE " 必須等待其他所有事務完成對表 EMPLOYEE 的 INSERT 或 UPDATE 操作。例如，如果有兩個事務正在對 EMPLOYEE 表進行 INSERT 操作，那么其中有一個事務會被另一個事務阻塞。顯然，這種“逐次插入”的解決方案不適合多用戶的事務處理系統。
使用鍵管理器
解決方案的思想
很多大型的應用程序使用鍵管理器方法維護所有表的代理鍵。鍵管理器可以是一個助手類。每當需要向表插入一個行時，便可以調用鍵管理器生成新的鍵值，然后將獲得的鍵值插入新行。
示例
首先，需要創建表 KEYS 來記錄每個表的當前代理鍵值。鍵管理器類將使用該表生成新鍵值。
CREATE TABLE KEYS ( TABLENAME CHAR(256),
COLNUMNAME CHAR(256),
SURROGATEKEYVALUE BIGINT,
INCREMENT BIGINT,
PRIMARY KEY(TABLENAME, COLNUMNAME));
|
第二，將新表（例如表 EMPLOYEE）注冊到表 KEYS 中。
INSERT INTO KEYS (TABLENAME, COLUMNNAME, SURROGATEKEYVALUE,
INCREMENT) VALUES (‘EMPLOYEE’, ‘SERIALNUMBER’, 0, 1);
|
第三，編寫 KeyManger 類來維護每個已注冊表的代理鍵。KeyManager 將提供兩個方法：
/**
*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);
|
要查看更詳細的 KeyManger 的代碼，請參考附錄。
第四，調用 KeyManger 來獲得主鍵值：
…
KeyManager km = KeyManager.singleton();
Long surrogateKey = km.getSurrogateKey("EMPLOYEE", "SERIALNUMBER");
…
|
優點和問題
顯然，鍵管理器是模塊化設計的一個很好的例子。鍵管理器封裝了代理鍵生成函數。這種實現也易于定制。您可以在 KEYS 表中為 SURROGATEKEYVALUE 或 INCREMENT 指定不同的值，以得到不同的代理鍵。而且，這種實現可以在大多數數據庫系統上實施。但是，為了進行維護，需要一個單獨的表和編寫代碼。所以，這種方法更適合于大型的跨數據庫系統。
使用 DB2 UDB 特性
DB2 UDB 提供了三種方法來生成惟一值。您可以使用這些方法來實現代理鍵。
- DB2 UDB Version 6.1 中的 GENERATE_UNIQUE() SQL 函數。
- DB2 UDB Version 7.2 中 CREATE TABLE 語句的 IDENTITY 選項。
- DB2 UDB Version 7.2 中的 SEQUENCE 對象。
GENERATE_UNIQUE()
解決方案的思想
GENERATE_UNIQUE() 最初是在 DB2 UDB Version 6.1 中提供的一個 SQL 函數。該函數返回當前系統時間戳。我們可以使用該函數為代理鍵列生成惟一值。
示例
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 語句插入一行：
INSERT INTO EMPLOYEE (SERIALNUMBER, FIRSTNAME, LASTNAME,
SALARY) VALUES(GENERATE_UNIQUE(), ‘John’, ‘Smith’, 999.99)
|
優點和問題
這里需要清楚兩件事情。
首先，當多個事務在同一時刻插入行時，GENERATE_UNIQUE() 可能會返回相同的時間戳。在這種情況下，GENERATE_UNIQUE() 不能為每個事務生成一個惟一的返回值，因而這種方法不適合有大量事務的系統。
第二，一旦系統時鐘需要向后調整，那么 GENERATE_UNIQUE() 將可能返回重復的值。
由于上述限制，我決不會在生產系統中使用 GENERATE_UNIQUE()。但是，當您需要在有限的時間內完成一個原型時，這也許是一種選擇。
CREATE TABLE 語句中的 IDENTITY 選項
解決方案的思想
IDENTITY 是 DB2 UDB Version 7.1 和后期版本提供的 CREATE TABLE 語句中的一個選項。在創建表時，可以將某個列指定為 IDENTITY 列。對于每條 INSERT 語句，DB2 將負責為其中的這一列生成一個惟一的值。
示例
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))
|
然后可以用下面的語句插入一行：
INSERT INTO EMPLOYEE (FIRSTNAME, LASTNAME, SALARY) VALUES
( ‘John’, ‘Smith’, 999.99)
|
INSERT 語句不需要指定 SERIALNUMBER 列的值。DB2 UDB 將根據列的定義自動生成惟一值，即 "GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)"。
優點和問題
IDENTITY 函數在大多數情況下是代理鍵函數的一個好的解決方案。DB2 import 和 export 實用程序也支持 IDENTITY 選項。
然而，在某種情況下，這種解決方案不大方便。在運行 INSERT 語句之后，應用程序將永遠都不知道放入了主鍵列中的是什么值。如果應用程序必須繼續向子表插入一個行，那么它就不得不對父表運行一條 SELECT 語句，以得到主鍵值。不過，如果這一點對于您的系統不成問題的話，那么使用 IDENTITY 選項是一個好主意。
SEQUENCE 對象
解決方案的思想
SEQUENCE 對象是在 DB2 UDB Version 7.2 中引入的一個特性。用戶可以在數據庫中創建一個 SEQUENCE 對象，就像創建表對象或視圖對象一樣，然后從 SEQUENCE 中請求值。DB2 保證用戶每次可以得到一個惟一的序列值。
示例
您可以在數據庫中創建一個 SEQUENCE 對象：
CREATE SEQUENCE EMPSERIAL
AS BIGINT
START WITH 1
INCREMENT BY 1
|
如果有一個如下所示的 EMPLOYEE 表：
CREATE TABLE EMPLOYEE ( SERIALNUMBER BIGINT NOT NULL,
FIRSTNAME CHAR(64),
LASTNAME CHAR(64),
SALARY DECIMAL(10, 2),
PRIMARY KEY (SERIALNUMBER))
|
那么可以用下面的語句插入一個行：
INSERT INTO EMPLOYEE ( SERIALNUMBER, FIRSTNAME, LASTNAME,
SALARY) VALUES(NEXTVAL FOR EMPSERIAL, 'John', 'Smith', 99.99)
|
在這里使用 " NEXTVAL FOR EMPSERIAL " 從 SEQUENCE 中獲得惟一值。
您可以使用 " PREVVAL FOR EMPSERIAL " 獲得當前連接會話中最近生成的序列值。應用程序就可以知道放入主鍵列中的是什么值，從而繼續向子表插入一個行。這里，“在當前連接會話中”這一點很重要，這意味著 "PREVVAL" 將只返回在相同連接會話中生成的值。
例如，考慮這樣的情況：有兩個應用程序連接到數據庫，并按照如下順序運行下面的 SQL 語句。
（假設 SEQUENCE " EMPSERIAL " 的當前值是 3）。
應用程序 1：
INSERT INTO EMPLOYEE ( SERIALNUMBER, FIRSTNAME, LASTNAME,
SALARY) VALUES(NEXTVAL FOR EMPSERIAL, 'Martin', 'Wong', 1000.00)
|
從 EMPSERIAL 生成的 " NEXTVAL " 是 4。
應用程序 2：
INSERT INTO EMPLOYEE ( SERIALNUMBER, FIRSTNAME, LASTNAME,
SALARY) VALUES(NEXTVAL FOR EMPSERIAL, 'Patrick', 'Chan', 99.99)
|
從 EMPSERIAL 生成的 " NEXTVAL " 是 5。
應用程序 1：
SELECT PREVVAL FOR EMPSERIAL FROM EMPLOYEE
|
" PREVVAL " 將返回 4，而不是 5。
而且， PREVVAL 和 NEXTVAL 的值不會受事務回滾的影響。
例如，假設 SEQUENCE " EMPSERIAL " 的當前值是 30。某個應用程序開始了一個事務：
INSERT INTO EMPLOYEE ( SERIALNUMBER, FIRSTNAME, LASTNAME,
SALARY) VALUES(NEXTVAL FOR EMPSERIAL, 'William', 'Chen', 99.99)
|
執行 ROLLBACK 操作。
然后，如果運行：
SELECT PREVVAL FOR EMPSERIAL FROM EMPLOYEE
|
則 " PREVVAL " 將返回 31，而不是 30。
優點和問題
SEQUENCE 是最近 DB2 UDB 為生成惟一值而實現的函數。它還有一個緩存函數，用于提高性能（要了解詳細信息，請參閱 IBM DB2 UDB SQL Reference）。該函數比 IDENTITY 函數更靈活，因為它是數據庫中的一個獨立對象。必要時候，可以通過運行 ALTER SEQUENCE 語句更改其設置。
如果系統只在 DB2 UDB 上運行，那么 SEQUENCE 也許是最好的解決方案，因為它易于使用，而且不像鍵管理器那樣需要額外的代碼，并且可以隨需求的變化很輕易對其進行更改。
結束語
本文描述了實現作為主鍵的代理鍵的三種方法。文中主要討論了如何為代理鍵生成惟一的序列值。
傳統方法適合于簡單的、單用戶（非并發）系統。對于實現對于大型系統和跨平臺系統，鍵管理器是一個好選擇。但是，如果項目只在 DB2 UDB 上運行的話，可以考慮 DB2 UDB 提供的特性。IDENTITY 和 SEQUENCE 函數提供了一種容易的、靈活的解決方案。
在創建 IDENTITY 列和 SEQUENCE 對象時，可以使用很多選擇。請參閱 IBM DB2 UDB Administration Guide和 IBM DB2 UDB SQL Reference，以獲得完整的細節。
附錄
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;
}
}
|
參考資料
|