數(shù)據(jù)庫主鍵和外鍵設(shè)計的原則

1、數(shù)據(jù)庫主鍵和外鍵設(shè)計的原則(2010-10-26 10:12:10主鍵和外鍵是把多個表組織為一個有效的關(guān)系數(shù)據(jù)庫的粘合劑。主鍵和外鍵的設(shè)計對物理數(shù)據(jù)庫的性能和可用性都有著決定性的影響。必須將數(shù)據(jù)庫模式從理論上的邏輯設(shè)計轉(zhuǎn)換為實際的物理設(shè)計。而主鍵和外鍵的結(jié)構(gòu)是這個設(shè)計過程的癥結(jié)所在。一旦將所設(shè)計的數(shù)據(jù)庫用于了生產(chǎn)環(huán)境,就很難對這些鍵進(jìn)行修改,所以在開發(fā)階段就設(shè)計好主鍵和外鍵就是非常必要和值得的。主鍵:關(guān)系數(shù)據(jù)庫依賴于主鍵-它是數(shù)據(jù)庫物理模式的基石。主鍵在物理層面上只有兩個用途:1. 惟一地標(biāo)識一行。2. 作為一個可以被外鍵有效引用的對象?；谝陨线@兩個用途,下面給出了我在設(shè)計物理層面的主鍵時所

2、遵循的一些原則:1. 主鍵應(yīng)當(dāng)是對用戶沒有意義的。如果用戶看到了一個表示多對多關(guān)系的連接表中的數(shù)據(jù),并抱怨它沒有什么用處,那就證明它的主鍵設(shè)計地很好。2. 主鍵應(yīng)該是單列的,以便提高連接和篩選操作的效率。注:使用復(fù)合鍵的人通常有兩個理由為自己開脫,而這兩個理由都是錯誤的。其一是主鍵應(yīng)當(dāng)具有實際意義,然而,讓主鍵具有意義只不過是給人為地破壞數(shù)據(jù)庫提供了方便。其二是利用這種方法可以在描述多對多關(guān)系的連接表中使用兩個外部鍵來作為主鍵,我也反對這種做法,理由是:復(fù)合主鍵常常導(dǎo)致不良的外鍵,即當(dāng)連接表成為另一個從表的主表,而依據(jù)上面的第二種方法成為這個表主鍵的一部分,然,這個表又有可能再成為其它從表的主

3、表,其主鍵又有可能成了其它從表主鍵的一部分,如此傳遞下去,越靠后的從表,其主鍵將會包含越多的列了。3. 永遠(yuǎn)也不要更新主鍵。實際上,因為主鍵除了惟一地標(biāo)識一行之外,再沒有其他的用途了,所以也就沒有理由去對它更新。如果主鍵需要更新,則說明主鍵應(yīng)對用戶無意義的原則被違反了。注:這項原則對于那些經(jīng)常需要在數(shù)據(jù)轉(zhuǎn)換或多數(shù)據(jù)庫合并時進(jìn)行數(shù)據(jù)整理的數(shù)據(jù)并不適用。4. 主鍵不應(yīng)包含動態(tài)變化的數(shù)據(jù),如時間戳、創(chuàng)建時間列、修改時間列等。5. 主鍵應(yīng)當(dāng)有計算機自動生成。如果由人來對主鍵的創(chuàng)建進(jìn)行干預(yù),就會使它帶有除了惟一標(biāo)識一行以外的意義。一旦越過這個界限,就可能產(chǎn)生認(rèn)為修改主鍵的動機,這樣,這種系統(tǒng)用來鏈接記錄

4、行、管理記錄行的關(guān)鍵手段就會落入不了解數(shù)據(jù)庫設(shè)計的人的手中。在我們的數(shù)據(jù)庫設(shè)計中,不可逃避的就是數(shù)據(jù)庫表的主鍵,可能有很多朋友沒有深入思考過,主鍵的設(shè)計對整個數(shù)據(jù)庫的設(shè)計影響很大,因此我們不得不要重視起來。主鍵的必要性:有些朋友可能不提倡數(shù)據(jù)庫表必須要主鍵,但在我的思考中,覺得每個表都應(yīng)該具有主鍵,不管是單主鍵還是雙主鍵,主鍵的存在就代表著表結(jié)構(gòu)的完整性,表的記錄必須得有唯一區(qū)分的字段,主鍵主要是用于其他表的外鍵關(guān)聯(lián),本記錄的修改與刪除,當(dāng)我們沒有主鍵時,這些操作會變的非常麻煩。主鍵的無意義性:我強調(diào)主鍵不應(yīng)該具有實際的意義,這可能對于一些朋友來說不太認(rèn)同,比如訂單表吧,會有“訂單編號”字段,

5、而這個字段呢在業(yè)務(wù)實際中本身就是應(yīng)該具有唯一性,具有唯一標(biāo)識記錄的功能,但我是不推薦采用訂單編號字段作為主鍵的,因為具有實際意義的字段,具有“意義更改”的可能性,比如訂單編號在剛開始的時候我們一切順利,后來客戶說“訂單可以作廢,并重新生成訂單,而且訂單號要保持原訂單號一致”,這樣原來的主鍵就面臨危險了。因此,具有唯一性的實際字段也代表可以作為主鍵。因此,我推薦是新設(shè)一個字段專門用為主鍵,此主鍵本身在業(yè)務(wù)邏輯上不體現(xiàn),不具有實際意義。而這種主鍵在一定程序增加了復(fù)雜度,所以要視實際系統(tǒng)的規(guī)模大小而定,對于小項目,以后擴展不會很大的話,也查允許用實際唯一的字段作主鍵的。主鍵的選擇我們現(xiàn)在在思考一下,

6、應(yīng)該采用什么來作表的主鍵比較合理,申明一下,主鍵的設(shè)計沒有一個定論,各人有各人的方法,哪怕同一個,在不同的項目中,也會采用不同的主鍵設(shè)計原則。第一:編號作主鍵此方法就是采用實際業(yè)務(wù)中的唯一字段的“編號”作為主鍵設(shè)計,這在小型的項目中是推薦這樣做的,因為這可以使項目比較簡單化,但在使用中卻可能帶來一些麻煩,比如要進(jìn)行“編號修改”時,可能要涉及到很多相關(guān)聯(lián)的其他表,就象黎叔說的“后果很嚴(yán)重”;還有就是上面提到的“業(yè)務(wù)要求允許編號重復(fù)時”,我們再那么先知,都無法知道業(yè)務(wù)將會修改成什么?第二:自動編號主鍵這種方法也是很多朋友在使用的,就是新建一個id字段,自動增長,非常方便也滿足主鍵的原則,優(yōu)點是:數(shù)

7、據(jù)庫自動編號,速度快,而且是增量增長,聚集型主鍵按順序存放,對于檢索非常有利;數(shù)字型的,占用空間小,易排序,在程序中傳遞也方便;如果通過非系統(tǒng)增加記錄(比如手動錄入,或是用其他工具直接在表里插入新記錄,或老系統(tǒng)數(shù)據(jù)導(dǎo)入時,非常方便,不用擔(dān)心主鍵重復(fù)問題。缺點:其實缺點也就是來自其優(yōu)點,就是因為自動增長,在手動要插入指定id的記錄時會顯得麻煩,尤其是當(dāng)系統(tǒng)與其他系統(tǒng)集成時,需要數(shù)據(jù)導(dǎo)入時,很難保證原系統(tǒng)的id 不發(fā)生主鍵沖突(前提是老系統(tǒng)也是數(shù)字型的;如果其他系統(tǒng)主鍵不是數(shù)字型那就麻煩更大了,會導(dǎo)致修改主鍵數(shù)據(jù)類型了,這也會導(dǎo)致其他相關(guān)表的修改,后果同樣很嚴(yán)重;就算其他系統(tǒng)也是數(shù)字型的,在導(dǎo)入時

8、,為了區(qū)分新老數(shù)據(jù),可能想在老數(shù)據(jù)主鍵前統(tǒng)一加一個“o”(old來表示這是老數(shù)據(jù),那么自動增長的數(shù)字型又面臨一個挑戰(zhàn)。第三:max加一由于自動編號存在那些問題,所以有些朋友就采用自己生成,同樣是數(shù)字型的,只是把自動增長去掉了,采用在insert時,讀取max值后加一,這種方法可以避免自動編號的問題,但也存在一個效率問題,如果記錄非常大的話,那么max(也會影響效率的;更嚴(yán)重的是并發(fā)性問題,如果同時有兩人讀到相同的max后,加一后插入的id值會重復(fù),這已經(jīng)是有經(jīng)驗教訓(xùn)的了。第四:自制加一考慮max加一的效率后,有人采用自制加一,也就是建一個特別的表,字段為:表名,當(dāng)前序列值。這樣在往表中插入值時

9、,先從此表中找到相應(yīng)表的最大值后加一,進(jìn)行插入,有人可能發(fā)現(xiàn),也可能會存在并發(fā)處理,這個并發(fā)處理,我們可以采用lock線程的方式來避免,在生成此值的時,先lock,取到值以后,再unlock出來,這樣不會有兩人同時生成了。這比max加一的速度要快多了。但同樣存在一個問題:在與其他系統(tǒng)集成時,脫離了系統(tǒng)中的生成方法后,很麻煩保證自制表中的最大值與導(dǎo)入后的保持一致,而且數(shù)字型都存在上面講到的“o”老數(shù)據(jù)的導(dǎo)入問題。因此在“自制加一”中可以把主鍵設(shè)為字符型的。字符型的自制加一我倒是蠻推薦的,應(yīng)該字符型主鍵可以應(yīng)付很多我們意想不到的情況。第五:guid主鍵同 identity 列相比,uniqueidentifier 列可以通過 newid( 函數(shù)提前得知新增加的行 id,為應(yīng)用程序的后續(xù)處理提供了很大方便。便于數(shù)據(jù)庫移植,其它數(shù)據(jù)庫中并不一定具有 identity 列,而 guid 列可以作為字符型列轉(zhuǎn)換到其它數(shù)據(jù)庫中,同時將應(yīng)用程序中產(chǎn)生的 guid 值存入數(shù)據(jù)庫,它不會對原有數(shù)據(jù)帶來影響。便于數(shù)據(jù)庫初始化,如果應(yīng)用程序要加載一些初始數(shù)據(jù), identity 列的處理方式就比較麻煩,而 uniqueidentifier 列則無需任何處理,直接用 t-sql 加載即可。便于對某些對象或常量進(jìn)行永久標(biāo)識,如類的 classid,對象的實例標(biāo)識,uddi 中的聯(lián)