2017年計算機等考三級網(wǎng)絡技術輔導：SQLServer對表的主鍵設計問題及解決辦法

關于數(shù)據(jù)庫的邏輯設計，是一個很廣泛的問題。本文主要針對開發(fā)應用中遇到在MS SQL Server上進行表設計時，對表的主鍵設計應注意的問題以及相應的解決辦法。
    

主鍵設計現(xiàn)狀和問題
    

關于數(shù)據(jù)庫表的主鍵設計，一般而言，是根據(jù)業(yè)務需求情況，以業(yè)務邏輯為基礎，形成主鍵。
    

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

數(shù)據(jù)冗余存儲：隨著這種主從關系的延伸，數(shù)據(jù)庫中需要重復存儲的數(shù)據(jù)將變得越來越龐大。或者當主表本身就是聯(lián)合主鍵時，就必須在從表中將所有的字段重新存儲一次。
    

SQL復雜度增加：當存在多個字段的聯(lián)合主鍵時，我們需要將主表的多個字段與子表的多個字段關聯(lián)以獲取滿足某些條件的所有詳細情況記錄。
    

程序復雜度增加：可能需要傳遞多個參數(shù)。
    

效率降低：數(shù)據(jù)庫系統(tǒng)需要判斷更多的條件，SQL語句長度增加。同時，聯(lián)合主鍵自動生成聯(lián)合索引
    

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

解決方案
    

從上面，我們已經(jīng)看到現(xiàn)有結構存在著相當多的弊端，主要是導致程序復雜、效率降低并且不利于分頁。
    

為解決上述問題，本文提出：當應用系統(tǒng)后臺數(shù)據(jù)庫表間存在主從關系時，數(shù)據(jù)庫表額外增加一非業(yè)務字段作為主鍵，該字段為數(shù)值型;或者當該表需要在應用中進行分頁查詢時，也應考慮如此設計。一般地，我們也可以幾乎為任何表增加一個與業(yè)務邏輯無關的字段作為該表的主鍵字段。
    

由于該字段要作為表的主鍵，那么其首要條件是要保證在該表中要具有性。同時，結合SQL Server數(shù)據(jù)庫自身的特性，可以為其建立一個自增列：
    

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中的自增列卻存在一個比較尷尬的事實，那就是該字段一旦定義和使用，用戶無法直接干預該字段的值，完全由數(shù)據(jù)庫系統(tǒng)自身控制：
    

完全數(shù)據(jù)庫系統(tǒng)控制，用戶無法修改值
    

在數(shù)據(jù)庫的發(fā)布和訂閱時，使用自增列會比較麻煩
    

恢復部分數(shù)據(jù)時，使用自增列會比較麻煩
    

該列的值必須在插入數(shù)據(jù)后才能獲取
    

鑒于此，建議不以自增列的方式來定義，而是參考Oracle數(shù)據(jù)庫系統(tǒng)中序列，在SQL Server系統(tǒng)中實現(xiàn)類似Oracle數(shù)據(jù)庫系統(tǒng)序列功能。這個具體在下面的小節(jié)中介紹。我們只需要按照普通字段的定義方式修改表定義為：
    

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數(shù)據(jù)庫中創(chuàng)建一個新表，以管理序列值：
    

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_MAX BIGINT　NOT NULL DEFAULT 0,--序列值 
SEQ_STEPINT NOT NULL DEFAULT 1,--序列增長步長 
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)--主鍵 
)

應用系統(tǒng)為需要創(chuàng)建自增列的表創(chuàng)建一個序列名稱，在表“T_DB_SEQ”中反映為數(shù)據(jù)庫中的一行。