深入討論SQL Server 表的主鍵問題

　關於資料庫的邏輯設計，是一個很廣泛的問題。本文主要針對開發應用中遇到在MS SQL Server上進行表設計時，對錶的主鍵設計應注意的問題以及相應的解決辦法。

　　主鍵設計現狀和問題

　　關於資料庫表的主鍵設計，一般而言，是根據業務需求情況，以商務邏輯為基礎，形成主鍵。

　　比如，銷售時要記錄銷售情況，一般需要兩個表，一個是銷售單的概要描述，記錄諸如銷售單號、總金額一類的情況，另外一個表記錄每種商品的數量和金額。對於第一個表(主表)，通常我們以單據號為主鍵;對於商品銷售的明細表(從表)，我們就需要將主表的單據號也放入到商品的明細表中，使其關聯起來形成主從關係。同時該單據號與商品的編碼一起，形成明細表的聯合主鍵。這隻是一般情況，我們稍微將這個問題延伸一下：假如在明細中，我們每種商品又可能以不同的價格方式銷售。有部分按折扣價格銷售，有部分按正常價格銷售。要記錄這些情況，那麼我們就需要第三個表。而這第三個表的主鍵就需要第一個表的單據號以及第二個表的商品號再加上自身需要的資訊一起構成聯合主鍵;又或者其他情況，在第一個主表中，本身就是以聯合方式構成聯合主鍵，那麼也需要在從表中將主表的多個欄位添加進來聯合在一起形成自己的主鍵。

　　資料冗餘儲存：隨著這種主從關係的延伸，資料庫中需要重複儲存的資料將變得越來越龐大。或者當主表本身就是聯合主鍵時，就必須在從表中將所有的欄位重新儲存一次。

　　SQL複雜度增加：當存在多個欄位的聯合主鍵時，我們需要將主表的多個欄位與子表的多個欄位關聯以擷取滿足某些條件的所有詳細情況記錄。

　　程式複雜度增加：可能需要傳遞多個參數。

　　效率降低：資料庫系統需要判斷更多的條件，SQL語句長度增加。同時，聯合主鍵自動產生聯合索引

　　WEB分頁困難：由於是聯合主鍵方式(對於多數的子表)，那麼在WEB頁面上要進行分頁處理時，在自關聯時，難於處理。

　　解決方案

　　從上面，我們已經看到現有結構存在著相當多的弊端，主要是導致程式複雜、效率降低並且不利於分頁。

　　為解決上述問題，本文提出：當應用系統後台資料庫表間存在主從關係時，資料庫表額外增加一非業務欄位作為主鍵，該欄位為數值型;或者當該表需要在應用中進行分頁查詢時，也應考慮如此設計。一般地，我們也可以幾乎為任何錶增加一個與商務邏輯無關的欄位作為該表的主鍵欄位。

　　由於該欄位要作為表的主鍵，那麼其首要條件是要保證在該表中要具有唯一性。同時，結合SQL Server資料庫自身的特性，可以為其建立一個自增列：

　　以下為引用的內容：

create TABLE T_PK_DEMO 　　( 　　U_ID　 BIGINT　NOT NULL IDENTITY(1,1), 　　–唯一標識記錄的ID 　　COL_OTHER　VARchar(20) NOT NULL　, 　　–其他列 　　CONSTRAINT PK_T_PK_DEMO PRIMARY KEY　NONCLUSTERED 　　(U_ID)–定義為主鍵 　　)

但是，SQL Server中的自增列卻存在一個比較尷尬的事實，那就是該欄位一旦定義和使用，使用者無法直接幹預該欄位的值，完全由資料庫系統自身控制：

　　完全資料庫系統控制，使用者無法修改值

　　在資料庫的發布和訂閱時，使用自增列會比較麻煩

　　恢複部分資料時，使用自增列會比較麻煩

　　該列的值必須在插入資料後才能擷取

　　鑒於此，建議不以自增列的方式來定義，而是參考Oracle資料庫系統中序列，在SQL Server系統中實作類別似Oracle資料庫系統序列功能。這個具體在下面的小節中介紹。我們只需要按照普通欄位的定義方式修改表定義為：

　　以下為引用的內容：

create TABLE T_PK_DEMO 　　( 　　U_ID　 BIGINT NOT NULL ,–唯一標識記錄的ID 　　COL_OTHER　VARchar(20) NOT NULL ,–其他列 　　CONSTRAINT PK_T_PK_DEMO PRIMARY KEY　NONCLUSTERED (U_ID)–定義為主鍵 　　)

　　參照Oracle序列的功能，我們需要在SQL Server資料庫中建立一個新表，以管理序列值：

　　以下為引用的內容：

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,–序列當前值 　　SEQ_MIN BIGINT　NOT NULL DEFAULT 0,–序列最小值 　　SEQ_MAX BIGINT　NOT NULL DEFAULT 0,–序列最小值 　　SEQ_STEPINT NOT NULL DEFAULT 1,–序列增長步長 　　IF_CYCLEINT NOT NULL DEFAULT 0,–是否迴圈(0,不迴圈;1,迴圈) 　　CONSTRAINT T_DB_SEQ PRIMARY KEY　CLUSTERED 　　(SEQ_NAME,SEQ_OWNER)–主鍵 　　)

　　應用系統為需要建立自增列的表建立一個序列名稱，在表“T_DB_SEQ”中反映為資料庫中的一行。

　第一，需要為需要建立序列的表建立一個序列。採用方法：F_create_SEQ(序列名)。該函數傳入序列的名稱，在表“T_DB_SEQ”插入一行。序列的所有者，採用系統變數SYSTEM_USER。

　　第二，擷取下一個值。採用方法：F_GET_NEXT_SEQ_VAL(序列名)。該函數根據序列名擷取該序列的下一個值，根據當前值與增長步長得到。同時，該函數保證在同時擷取同一個序列時，應保證並發一致性。

　　第三、將傳回值返回到應用使用。

　　此外，為保證應用的完整性，可能還需要提供一些方法的重載方法，同時提供一些其他方法：

　　擷取序列當前值：F_GET_SEQ_CUR_VAL(序列名)

　　設定序列值：F_SET_SEQ_VAL(序列名)

　　刪除序列：F_DEL_SEQ(序列名)

　　判斷序列是否存在：F_SEQ_exists(序列名)

　　在主從關係的表設計中，子表也使用序欄欄位作為唯一主鍵，將父表的序欄欄位作為外部索引鍵關聯：

　　以下為引用的內容：

create TABLE T_PK_DEMO_C 　　( 　　U_ID　 BIGINT NOT NULL ,–唯一標識記錄的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, 　　)

　　使用序列的問題及解決辦法

　　由於系統使用一個額外增加一個欄位作為主鍵，因此沒有為商務邏輯建立主鍵約束。比如在企業使用者資訊表中，要求企業中使用者登入名稱必須唯一。一般在建立表時，以登入名稱作為主鍵，這個時候在資料庫層自然的建立另一個主鍵唯一性限制式。而現在沒有使用登入名稱作為主鍵，那麼就沒有這個約束。解決辦法：

　　一是在資料庫層解決。可以為該表建立一個唯一(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) – 唯一索引

　　二是在應用端解決。也就是在應用中判斷該列是否有重複值，然後根據判斷結果來保證唯一性。

　　我們注意到，在之前的例子中，主鍵採用了NONCLUSTERED(非聚蔟)的索引方式。關於如何設計索引，不是本文的重點，在這裡僅提供一個建立索引時採用聚蔟方式還是非聚蔟方式的一個一般原則：

　　作為非業務欄位的主鍵列，是一個沒有重複值的、基本不進行更新操作的列。並且，在SQL Server資料庫中，聚蔟索引在一個表中只能有一個。因此，聚蔟索引非常重要，需要留給更重要的欄位來使用。因此，對照上表和根據聚蔟索引的重要程度，在此處採用非聚蔟方式建立其索引。

　　具體應用

　　採用這種主鍵設計方式，有諸多好處，這已經在前文說明。現在就以一個具體的應用來說明如何使用這個主鍵。

　　當前的應用系統基本上都已經採用B/S方式，儘管現在的網路速度已經有大幅度的提高，但是由於在WEB應用上使用者數量眾多、同時基本上所有的運算都集中在WEB應用伺服器上，所以在WEB設計上更要考慮到效能的最佳化，以減少網路流量和對伺服器的壓力。最常見的一個應用就是列表方式展現時的分頁方式。一般的，在資料量小的情況下，一般不會怎麼注意這個問題，通常採用將資料完全取出，然後在WEB伺服器上進行分頁。但是，當資料量龐大時，這種方式就會導致速度降低，甚至根本不可用。所以，一般採用預存程序，在資料庫端進行分頁。

轉自：http://111206wr.blog.chinabyte.com/2010/06/09/107/