How to choose the right primary key

之前, 我并不知道Oracle是否能创建Compound primary key, 因为我选择Primary Key的原则是尽量用sequence, 或者其他一些选择性很强的字段. 所以我没有在意其他情况.

我在review script时候, 看到下面这个表, 它的结构和key是这样创建的.

create table RMR_USBLTY_VALUES(

SRVY_ID VARCHAR2(5) not null,

SRVY_PHASE_NMBR VARCHAR2(2) not null,

OPERAND_NMBR NUMBER(2) not null,

SMPLU_ID NUMBER(5) not null,

ARBSU_ID VARCHAR2(6) not null,

CNTY_TYPE_CODE VARCHAR2(5) not null,

CMPD_DEMO_ID VARCHAR2(5) not null,

TOTAL_SMPL_CNT NUMBER(6)

);

-- Create/Recreate primary, unique and foreign key constraints

alter table RMR_USBLTY_VALUES

add constraint RMR_USBLTY_VALUES_PK primary key (SRVY_ID, SRVY_PHASE_NMBR, OPERAND_NMBR, ARBSU_ID, CMPD_DEMO_ID, SMPLU_ID, CNTY_TYPE_CODE)

粗体字field都被作为复合主键了, 第一眼, 我就认为这不是一个好的设计, 我猜想, 原设计者认为只有将几个粗体字field组合在一起, 才能在该表中唯一区分每个记录, 所以将这些字段组合起来作为复合主键, 这是十足的为了有个主键而建主键的, 是完全错误的. 理由是: 每次更新与索引相关的字段, oracle将不得不重新计算索引. 上例中, 主键中有这么多字段, 更新字段的几率是比较大的, 也就是说, 随便更新其中一个字段, 就要计算一次索引.

那么什么样的字段可以作为主键呢 ?

先看看, 做主键的条件是什么? (article: Candidate keys: Choosing the right primary key)

1. The candidate key must be unique within its domain (the entity it represents, and beyond, if you also intend to access external entities).

2. The candidate key can not hold NULL values (NULL is not zero. Zero is a number. NULL is 'nonexistent value').

3. The candidate key can never change. It must hold the same value for a given occurrence of an entity for the lifetime of that entity.

知道了作为主键应该具备哪些条件之后, 那么到底哪些字段适合作为主键呢? (article: Database Design Guidelines and Rules of Thumb )

通常我们可选择的主键有: You have three choices for defining the primary key for most tables: a 'natural' key, a system generated or 'internal' key, and (for smaller lookup tables) a mnemonic code.

最适合的应该是 a system generated field

Rule of thumb: for any table over 100 records, use a system-generated number as the primary key.