 |
级别: 初级
Jason Zhang (jasonz@ca.ibm.com), 软件开发人员, IBM
2004 年 9 月 01 日
如何将代理键（surrogate key）用作 DB2 UDB 中的主键？代理键是生成惟一序列号的一种有效方法。从本文中可以了解三种实现：传统方法，使用键管理器，以及使用新的 DB2 UDB 特性。
简介
使用代理键解决方案是为了发现一种生成惟一序列号的有效方法。本文描述了三种实现：
- 使用传统方法。
- 使用键管理器。
- 使用 DB2 UDB 特性。
代理键也叫 内键（internal key）。当创建一个表时，可以添加一个额外的列作为代理键。这个列应该是 NOT NULL，并且没有商业意义。可以将该代理列指定为主键列。例如可以有一个数字代理列。代理键的值从某一个数字开始，例如 "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;
}
}
|
参考资料
|