深入討論SQL Server 表的主鍵問題
導(dǎo)讀：關(guān)于數(shù)據(jù)庫(kù)的邏輯設(shè)計(jì)，是一個(gè)很廣泛的問題。本文主要針對(duì)開發(fā)應(yīng)用中遇到在MS SQL Server上進(jìn)行表設(shè)計(jì)時(shí)，對(duì)表的主鍵設(shè)計(jì)應(yīng)注意的問題以及相應(yīng)的解決辦法。
主鍵設(shè)計(jì)現(xiàn)狀和問題
關(guān)于數(shù)據(jù)庫(kù)表的主鍵設(shè)計(jì)，一般而言，是根據(jù)業(yè)務(wù)需求情況，以業(yè)務(wù)邏輯為基礎(chǔ)，形成主鍵。
比如，銷售時(shí)要記錄銷售情況，一般需要兩個(gè)表，一個(gè)是銷售單的概要描述，記錄諸如銷售單號(hào)、總金額一類的情況，另外一個(gè)表記錄每種商品的數(shù)量和金額。對(duì)于***個(gè)表(主表)，通常我們以單據(jù)號(hào)為主鍵;對(duì)于商品銷售的明細(xì)表(從表)，我們就需要將主表的單據(jù)號(hào)也放入到商品的明細(xì)表中，使其關(guān)聯(lián)起來形成主從關(guān)系。同時(shí)該單據(jù)號(hào)與商品的編碼一起，形成明細(xì)表的聯(lián)合主鍵。這只是一般情況，我們稍微將這個(gè)問題延伸一下：假如在明細(xì)中，我們每種商品又可能以不同的價(jià)格方式銷售。有部分按折扣價(jià)格銷售，有部分按正常價(jià)格銷售。要記錄這些情況，那么我們就需要第三個(gè)表。而這第三個(gè)表的主鍵就需要***個(gè)表的單據(jù)號(hào)以及第二個(gè)表的商品號(hào)再加上自身需要的信息一起構(gòu)成聯(lián)合主鍵;又或者其他情況，在***個(gè)主表中，本身就是以聯(lián)合方式構(gòu)成聯(lián)合主鍵，那么也需要在從表中將主表的多個(gè)字段添加進(jìn)來聯(lián)合在一起形成自己的主鍵。
數(shù)據(jù)冗余存儲(chǔ)：隨著這種主從關(guān)系的延伸，數(shù)據(jù)庫(kù)中需要重復(fù)存儲(chǔ)的數(shù)據(jù)將變得越來越龐大。或者當(dāng)主表本身就是聯(lián)合主鍵時(shí)，就必須在從表中將所有的字段重新存儲(chǔ)一次。
SQL復(fù)雜度增加：當(dāng)存在多個(gè)字段的聯(lián)合主鍵時(shí)，我們需要將主表的多個(gè)字段與子表的多個(gè)字段關(guān)聯(lián)以獲取滿足某些條件的所有詳細(xì)情況記錄。
程序復(fù)雜度增加：可能需要傳遞多個(gè)參數(shù)。
效率降低：數(shù)據(jù)庫(kù)系統(tǒng)需要判斷更多的條件，SQL語句長(zhǎng)度增加。同時(shí)，聯(lián)合主鍵自動(dòng)生成聯(lián)合索引
WEB分頁(yè)困難：由于是聯(lián)合主鍵方式(對(duì)于多數(shù)的子表)，那么在WEB頁(yè)面上要進(jìn)行分頁(yè)處理時(shí)，在自關(guān)聯(lián)時(shí)，難于處理。
解決方案
從上面，我們已經(jīng)看到現(xiàn)有結(jié)構(gòu)存在著相當(dāng)多的弊端，主要是導(dǎo)致程序復(fù)雜、效率降低并且不利于分頁(yè)。
為解決上述問題，本文提出：當(dāng)應(yīng)用系統(tǒng)后臺(tái)數(shù)據(jù)庫(kù)表間存在主從關(guān)系時(shí)，數(shù)據(jù)庫(kù)表額外增加一非業(yè)務(wù)字段作為主鍵，該字段為數(shù)值型;或者當(dāng)該表需要在應(yīng)用中進(jìn)行分頁(yè)查詢時(shí)，也應(yīng)考慮如此設(shè)計(jì)。一般地，我們也可以幾乎為任何表增加一個(gè)與業(yè)務(wù)邏輯無關(guān)的字段作為該表的主鍵字段。
由于該字段要作為表的主鍵，那么其首要條件是要保證在該表中要具有唯一性。同時(shí)，結(jié)合SQL Server數(shù)據(jù)庫(kù)自身的特性，可以為其建立一個(gè)自增列：
以下為引用的內(nèi)容：
create TABLE T_PK_DEMO
(
U_ID BIGINT NOT NULL IDENTITY(1,1),
--唯一標(biāo)識(shí)記錄的ID
COL_OTHER VARchar(20) NOT NULL ,
--其他列
CONSTRAINT PK_T_PK_DEMO PRIMARY KEY NONCLUSTERED
(U_ID)--定義為主鍵
)
#p#
但是，SQL Server中的自增列卻存在一個(gè)比較尷尬的事實(shí)，那就是該字段一旦定義和使用，用戶無法直接干預(yù)該字段的值，完全由數(shù)據(jù)庫(kù)系統(tǒng)自身控制：
完全數(shù)據(jù)庫(kù)系統(tǒng)控制，用戶無法修改值
在數(shù)據(jù)庫(kù)的發(fā)布和訂閱時(shí)，使用自增列會(huì)比較麻煩
恢復(fù)部分?jǐn)?shù)據(jù)時(shí)，使用自增列會(huì)比較麻煩
該列的值必須在插入數(shù)據(jù)后才能獲取
鑒于此，建議不以自增列的方式來定義，而是參考Oracle數(shù)據(jù)庫(kù)系統(tǒng)中序列，在SQL Server系統(tǒng)中實(shí)現(xiàn)類似Oracle數(shù)據(jù)庫(kù)系統(tǒng)序列功能。這個(gè)具體在下面的小節(jié)中介紹。我們只需要按照普通字段的定義方式修改表定義為：
以下為引用的內(nèi)容：
create TABLE T_PK_DEMO
(
U_ID BIGINT NOT NULL ,--唯一標(biāo)識(shí)記錄的ID
COL_OTHER VARchar(20) NOT NULL ,--其他列
CONSTRAINT PK_T_PK_DEMO PRIMARY KEY NONCLUSTERED (U_ID)--定義為主鍵
)
參照Oracle序列的功能，我們需要在SQL Server數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)新表，以管理序列值：
以下為引用的內(nèi)容：
create TABLE T_DB_SEQ
(
SEQ_NAMEVARchar(50) NOT NULL ,--序列名稱
SEQ_OWNER VARchar(50) NOT NULL DEFAULT ’DBO’,
--序列所有者(SYSTEM_USER)
SEQ_CURRENT BIGINT NOT NULL DEFAULT 0,--序列當(dāng)前值
SEQ_MIN BIGINT NOT NULL DEFAULT 0,--序列最小值
SEQ_MAX BIGINT NOT NULL DEFAULT 0,--序列最小值
SEQ_MAX BIGINT NOT NULL DEFAULT 0,--序列***值
SEQ_STEPINT NOT NULL DEFAULT 1,--序列增長(zhǎng)步長(zhǎng)
IF_CYCLEINT NOT NULL DEFAULT 0,--是否循環(huán)(0,不循環(huán);1,循環(huán))
CONSTRAINT T_DB_SEQ PRIMARY KEY CLUSTERED
(SEQ_NAME,SEQ_OWNER)--主鍵
)
應(yīng)用系統(tǒng)為需要?jiǎng)?chuàng)建自增列的表創(chuàng)建一個(gè)序列名稱，在表“T_DB_SEQ”中反映為數(shù)據(jù)庫(kù)中的一行。
***，需要為需要建立序列的表創(chuàng)建一個(gè)序列。采用方法：F_create_SEQ(序列名)。該函數(shù)傳入序列的名稱，在表“T_DB_SEQ”插入一行。序列的所有者，采用系統(tǒng)變量SYSTEM_USER。
第二，獲取下一個(gè)值。采用方法：F_GET_NEXT_SEQ_VAL(序列名)。該函數(shù)根據(jù)序列名獲取該序列的下一個(gè)值，根據(jù)當(dāng)前值與增長(zhǎng)步長(zhǎng)得到。同時(shí)，該函數(shù)保證在同時(shí)獲取同一個(gè)序列時(shí)，應(yīng)保證并發(fā)一致性。
第三、將返回值返回到應(yīng)用使用。
此外，為保證應(yīng)用的完整性，可能還需要提供一些方法的重載方法，同時(shí)提供一些其他方法：
獲取序列當(dāng)前值：F_GET_SEQ_CUR_VAL(序列名)
設(shè)置序列值：F_SET_SEQ_VAL(序列名)
刪除序列：F_DEL_SEQ(序列名)
判斷序列是否存在：F_SEQ_exists(序列名)
在主從關(guān)系的表設(shè)計(jì)中，子表也使用序列字段作為唯一主鍵，將父表的序列字段作為外鍵關(guān)聯(lián)：
#p#
以下為引用的內(nèi)容：
create TABLE T_PK_DEMO_C
(
U_ID BIGINT NOT NULL ,--唯一標(biāo)識(shí)記錄的ID
COL_OTHER VARchar(20) NOT NULL ,--其他列
P_ID INT NOT NULL ,--父表ID
CONSTRAINT PK_T_PK_DEMO_C PRIMARY KEY
NONCLUSTERED (U_ID)--定義為主鍵
CONSTRAINT FK_T_PK_DEMO_C FOREIGN KEY (P_ID)
REFERENCES T_PK_DEMO(U_ID) ON delete CASCADE,
)
使用序列的問題及解決辦法
由于系統(tǒng)使用一個(gè)額外增加一個(gè)字段作為主鍵，因此沒有為業(yè)務(wù)邏輯建立主鍵約束。比如在企業(yè)用戶信息表中，要求企業(yè)中用戶登錄名必須唯一。一般在創(chuàng)建表時(shí)，以登錄名作為主鍵，這個(gè)時(shí)候在數(shù)據(jù)庫(kù)層自然的創(chuàng)建另一個(gè)主鍵唯一性約束。而現(xiàn)在沒有使用登錄名作為主鍵，那么就沒有這個(gè)約束。解決辦法：
一是在數(shù)據(jù)庫(kù)層解決。可以為該表創(chuàng)建一個(gè)唯一(UNIQUE)約束或者唯一索引。如：
alter TABLE T_PK_DEMO ADD CONSTRAINT C_T_PK_DEMO UNIQUE NONCLUSTERED(COL_OTHER)-唯一約束
create UNIQUE INDEX IX_T_PK_DEMO ON T_PK_DEMO(COL_OTHER) – 唯一索引
二是在應(yīng)用端解決。也就是在應(yīng)用中判斷該列是否有重復(fù)值，然后根據(jù)判斷結(jié)果來保證唯一性。
我們注意到，在之前的例子中，主鍵采用了NONCLUSTERED(非聚蔟)的索引方式。關(guān)于如何設(shè)計(jì)索引，不是本文的重點(diǎn)，在這里僅提供一個(gè)建立索引時(shí)采用聚蔟方式還是非聚蔟方式的一個(gè)一般原則：
作為非業(yè)務(wù)字段的主鍵列，是一個(gè)沒有重復(fù)值的、基本不進(jìn)行更新操作的列。并且，在SQL Server數(shù)據(jù)庫(kù)中，聚蔟索引在一個(gè)表中只能有一個(gè)。因此，聚蔟索引非常重要，需要留給更重要的字段來使用。因此，對(duì)照上表和根據(jù)聚蔟索引的重要程度，在此處采用非聚蔟方式創(chuàng)建其索引。
具體應(yīng)用
采用這種主鍵設(shè)計(jì)方式，有諸多好處，這已經(jīng)在前文說明。現(xiàn)在就以一個(gè)具體的應(yīng)用來說明如何使用這個(gè)主鍵。
當(dāng)前的應(yīng)用系統(tǒng)基本上都已經(jīng)采用B/S方式，盡管現(xiàn)在的網(wǎng)絡(luò)速度已經(jīng)有大幅度的提高，但是由于在WEB應(yīng)用上用戶數(shù)量眾多、同時(shí)基本上所有的運(yùn)算都集中在WEB應(yīng)用服務(wù)器上，所以在WEB設(shè)計(jì)上更要考慮到性能的優(yōu)化，以減少網(wǎng)絡(luò)流量和對(duì)服務(wù)器的壓力。最常見的一個(gè)應(yīng)用就是列表方式展現(xiàn)時(shí)的分頁(yè)方式。一般的，在數(shù)據(jù)量小的情況下，一般不會(huì)怎么注意這個(gè)問題，通常采用將數(shù)據(jù)完全取出，然后在WEB服務(wù)器上進(jìn)行分頁(yè)。但是，當(dāng)數(shù)據(jù)量龐大時(shí)，這種方式就會(huì)導(dǎo)致速度降低，甚至根本不可用。所以，一般采用存儲(chǔ)過程，在數(shù)據(jù)庫(kù)端進(jìn)行分頁(yè)。
上文中就SQL Server表的主鍵問題給出了詳細(xì)的解析，希望大家都能夠從中有所收獲。
【編輯推薦】
