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

數(shù)據(jù)庫(kù)主鍵和外鍵設(shè)計(jì)的原則(2010-10-26 10:12:10) 主鍵和外鍵是把多個(gè)表組織為一個(gè)有效的關(guān)系數(shù)據(jù)庫(kù)的粘合劑。主鍵和外鍵的設(shè)計(jì)對(duì)物理數(shù)據(jù)庫(kù)的性能和可用性都有著決定性的影響。必須將數(shù)據(jù)庫(kù)模式從理論上的邏輯設(shè)計(jì)轉(zhuǎn)換為實(shí)際的物理設(shè)計(jì)。而主鍵和外鍵的結(jié)構(gòu)是這個(gè)設(shè)計(jì)過(guò)程的癥結(jié)所在。一旦將所設(shè)計(jì)的數(shù)據(jù)庫(kù)用于了生產(chǎn)環(huán)境，就很難對(duì)這些鍵進(jìn)行修改，所以在開發(fā)階段就設(shè)計(jì)好主鍵和外鍵就是非常必要和值得的。主鍵： 關(guān)系數(shù)據(jù)庫(kù)依賴于主鍵-它是數(shù)據(jù)庫(kù)物理模式的基石。主鍵在物理層面上只有兩個(gè)用途：1. 惟一地標(biāo)識(shí)一行。2. 作為一個(gè)可以被外鍵有效引用的對(duì)象。 基于以上這兩個(gè)用途，下面給出了我在設(shè)計(jì)物理層面的主鍵時(shí)所遵循的一些原則： 1. 主鍵應(yīng)當(dāng)是對(duì)用戶沒(méi)有意義的。如果用戶看到了一個(gè)表示多對(duì)多關(guān)系的連接表中的數(shù)據(jù)，并抱怨它沒(méi)有什么用處，那就證明它的主鍵設(shè)計(jì)地很好。 2. 主鍵應(yīng)該是單列的，以便提高連接和篩選操作的效率。 注：使用復(fù)合鍵的人通常有兩個(gè)理由為自己開脫，而這兩個(gè)理由都是錯(cuò)誤的。其一是主鍵應(yīng)當(dāng)具有實(shí)際意義，然而，讓主鍵具有意義只不過(guò)是給人為地破壞數(shù)據(jù)庫(kù)提供了方便。其二是利用這種方法可以在描述多對(duì)多關(guān)系的連接表中使用兩個(gè)外部鍵來(lái)作為主鍵，我也反對(duì)這種做法，理由是：復(fù)合主鍵常常導(dǎo)致不良的外鍵，即當(dāng)連接表成為另一個(gè)從表的主表，而依據(jù)上面的第二種方法成為這個(gè)表主鍵的一部分，然，這個(gè)表又有可能再成為其它從表的主表，其主鍵又有可能成了其它從表主鍵的一部分，如此傳遞下去，越靠后的從表，其主鍵將會(huì)包含越多的列了。 3. 永遠(yuǎn)也不要更新主鍵。實(shí)際上，因?yàn)橹麈I除了惟一地標(biāo)識(shí)一行之外，再?zèng)]有其他的用途了，所以也就沒(méi)有理由去對(duì)它更新。如果主鍵需要更新，則說(shuō)明主鍵應(yīng)對(duì)用戶無(wú)意義的原則被違反了。注：這項(xiàng)原則對(duì)于那些經(jīng)常需要在數(shù)據(jù)轉(zhuǎn)換或多數(shù)據(jù)庫(kù)合并時(shí)進(jìn)行數(shù)據(jù)整理的數(shù)據(jù)并不適用。 4. 主鍵不應(yīng)包含動(dòng)態(tài)變化的數(shù)據(jù)，如時(shí)間戳、創(chuàng)建時(shí)間列、修改時(shí)間列等。 5. 主鍵應(yīng)當(dāng)有計(jì)算機(jī)自動(dòng)生成。如果由人來(lái)對(duì)主鍵的創(chuàng)建進(jìn)行干預(yù)，就會(huì)使它帶有除了惟一標(biāo)識(shí)一行以外的意義。一旦越過(guò)這個(gè)界限，就可能產(chǎn)生認(rèn)為修改主鍵的動(dòng)機(jī)，這樣，這種系統(tǒng)用來(lái)鏈接記錄行、管理記錄行的關(guān)鍵手段就會(huì)落入不了解數(shù)據(jù)庫(kù)設(shè)計(jì)的人的手中。在我們的數(shù)據(jù)庫(kù)設(shè)計(jì)中，不可逃避的就是數(shù)據(jù)庫(kù)表的主鍵，可能有很多朋友沒(méi)有深入思考過(guò)，主鍵的設(shè)計(jì)對(duì)整個(gè)數(shù)據(jù)庫(kù)的設(shè)計(jì)影響很大，因此我們不得不要重視起來(lái)。主鍵的必要性:有些朋友可能不提倡數(shù)據(jù)庫(kù)表必須要主鍵，但在我的思考中，覺(jué)得每個(gè)表都應(yīng)該具有主鍵，不管是單主鍵還是雙主鍵，主鍵的存在就代表著表結(jié)構(gòu)的完整性，表的記錄必須得有唯一區(qū)分的字段，主鍵主要是用于其他表的外鍵關(guān)聯(lián)，本記錄的修改與刪除，當(dāng)我們沒(méi)有主鍵時(shí)，這些操作會(huì)變的非常麻煩。主鍵的無(wú)意義性：我強(qiáng)調(diào)主鍵不應(yīng)該具有實(shí)際的意義，這可能對(duì)于一些朋友來(lái)說(shuō)不太認(rèn)同，比如訂單表吧，會(huì)有“訂單編號(hào)”字段，而這個(gè)字段呢在業(yè)務(wù)實(shí)際中本身就是應(yīng)該具有唯一性，具有唯一標(biāo)識(shí)記錄的功能，但我是不推薦采用訂單編號(hào)字段作為主鍵的，因?yàn)榫哂袑?shí)際意義的字段，具有“意義更改”的可能性，比如訂單編號(hào)在剛開始的時(shí)候我們一切順利，后來(lái)客戶說(shuō)“訂單可以作廢，并重新生成訂單，而且訂單號(hào)要保持原訂單號(hào)一致”，這樣原來(lái)的主鍵就面臨危險(xiǎn)了。因此，具有唯一性的實(shí)際字段也代表可以作為主鍵。因此，我推薦是新設(shè)一個(gè)字段專門用為主鍵，此主鍵本身在業(yè)務(wù)邏輯上不體現(xiàn)，不具有實(shí)際意義。而這種主鍵在一定程序增加了復(fù)雜度，所以要視實(shí)際系統(tǒng)的規(guī)模大小而定，對(duì)于小項(xiàng)目，以后擴(kuò)展不會(huì)很大的話，也查允許用實(shí)際唯一的字段作主鍵的。主鍵的選擇我們現(xiàn)在在思考一下，應(yīng)該采用什么來(lái)作表的主鍵比較合理，申明一下，主鍵的設(shè)計(jì)沒(méi)有一個(gè)定論，各人有各人的方法，哪怕同一個(gè)，在不同的項(xiàng)目中，也會(huì)采用不同的主鍵設(shè)計(jì)原則。第一：編號(hào)作主鍵此方法就是采用實(shí)際業(yè)務(wù)中的唯一字段的“編號(hào)”作為主鍵設(shè)計(jì)，這在小型的項(xiàng)目中是推薦這樣做的，因?yàn)檫@可以使項(xiàng)目比較簡(jiǎn)單化，但在使用中卻可能帶來(lái)一些麻煩，比如要進(jìn)行“編號(hào)修改”時(shí)，可能要涉及到很多相關(guān)聯(lián)的其他表，就象黎叔說(shuō)的“后果很嚴(yán)重”;還有就是上面提到的“業(yè)務(wù)要求允許編號(hào)重復(fù)時(shí)”，我們?cè)倌敲聪戎?，都無(wú)法知道業(yè)務(wù)將會(huì)修改成什么?第二：自動(dòng)編號(hào)主鍵這種方法也是很多朋友在使用的，就是新建一個(gè)id字段，自動(dòng)增長(zhǎng)，非常方便也滿足主鍵的原則，優(yōu)點(diǎn)是：數(shù)據(jù)庫(kù)自動(dòng)編號(hào)，速度快，而且是增量增長(zhǎng)，聚集型主鍵按順序存放，對(duì)于檢索非常有利;數(shù)字型的，占用空間小，易排序，在程序中傳遞也方便;如果通過(guò)非系統(tǒng)增加記錄（比如手動(dòng)錄入，或是用其他工具直接在表里插入新記錄，或老系統(tǒng)數(shù)據(jù)導(dǎo)入）時(shí)，非常方便，不用擔(dān)心主鍵重復(fù)問(wèn)題。缺點(diǎn)：其實(shí)缺點(diǎn)也就是來(lái)自其優(yōu)點(diǎn)，就是因?yàn)樽詣?dòng)增長(zhǎng)，在手動(dòng)要插入指定id的記錄時(shí)會(huì)顯得麻煩，尤其是當(dāng)系統(tǒng)與其他系統(tǒng)集成時(shí)，需要數(shù)據(jù)導(dǎo)入時(shí)，很難保證原系統(tǒng)的id不發(fā)生主鍵沖突（前提是老系統(tǒng)也是數(shù)字型的）;如果其他系統(tǒng)主鍵不是數(shù)字型那就麻煩更大了，會(huì)導(dǎo)致修改主鍵數(shù)據(jù)類型了，這也會(huì)導(dǎo)致其他相關(guān)表的修改，后果同樣很嚴(yán)重;就算其他系統(tǒng)也是數(shù)字型的，在導(dǎo)入時(shí)，為了區(qū)分新老數(shù)據(jù)，可能想在老數(shù)據(jù)主鍵前統(tǒng)一加一個(gè)“o”(old)來(lái)表示這是老數(shù)據(jù)，那么自動(dòng)增長(zhǎng)的數(shù)字型又面臨一個(gè)挑戰(zhàn)。第三：max加一由于自動(dòng)編號(hào)存在那些問(wèn)題，所以有些朋友就采用自己生成，同樣是數(shù)字型的，只是把自動(dòng)增長(zhǎng)去掉了，采用在insert時(shí)，讀取max值后加一，這種方法可以避免自動(dòng)編號(hào)的問(wèn)題，但也存在一個(gè)效率問(wèn)題，如果記錄非常大的話，那么max()也會(huì)影響效率的;更嚴(yán)重的是并發(fā)性問(wèn)題，如果同時(shí)有兩人讀到相同的max后，加一后插入的id值會(huì)重復(fù)，這已經(jīng)是有經(jīng)驗(yàn)教訓(xùn)的了。第四：自制加一考慮max加一的效率后，有人采用自制加一，也就是建一個(gè)特別的表，字段為：表名，當(dāng)前序列值。這樣在往表中插入值時(shí)，先從此表中找到相應(yīng)表的最大值后加一，進(jìn)行插入，有人可能發(fā)現(xiàn)，也可能會(huì)存在并發(fā)處理，這個(gè)并發(fā)處理，我們可以采用lock線程的方式來(lái)避免，在生成此值的時(shí)，先lock，取到值以后，再unlock出來(lái)，這樣不會(huì)有兩人同時(shí)生成了。這比max加一的速度要快多了。但同樣存在一個(gè)問(wèn)題：在與其他系統(tǒng)集成時(shí)，脫離了系統(tǒng)中的生成方法后，很麻煩保證自制表中的最大值與導(dǎo)入后的保持一致，而且數(shù)字型都存在上面講到的“o”老數(shù)據(jù)的導(dǎo)入問(wèn)題。因此在“自制加一”中可以把主鍵設(shè)為字符型的。字符型的自制加一我倒是蠻推薦的，應(yīng)該字符型主鍵可以應(yīng)付很多我們意想不到的情況。第五：guid主鍵目前一個(gè)比較好的主鍵是采用guid，當(dāng)然我是推薦主鍵還是字符型的，但值由guid生成，guid是可以自動(dòng)生成，也可以程序生成，而且鍵值不可能重復(fù)，可以解決系統(tǒng)集成問(wèn)題，幾個(gè)系統(tǒng)的guid值導(dǎo)到一起時(shí)，也不會(huì)發(fā)生重復(fù)，就算有“o”老數(shù)據(jù)也可以區(qū)分，而且效率很高，在.net里可以直接使用system.guid.newguid()進(jìn)行生成，在sql里也可以使用newid()生成。優(yōu)點(diǎn)是：同identity列相比，uniqueidentifier列可以通過(guò)newid()函數(shù)提前得知新增加的行id，為應(yīng)用程序的后續(xù)處理提供了很大方便。便于數(shù)據(jù)庫(kù)移植，其它數(shù)據(jù)庫(kù)中并不一定具有identity列，而guid列可以作為字符型列轉(zhuǎn)換到其它數(shù)據(jù)庫(kù)中，同時(shí)將應(yīng)用程序中產(chǎn)生的guid值存入數(shù)據(jù)庫(kù)，它不會(huì)對(duì)原有數(shù)據(jù)帶來(lái)影響。便于數(shù)據(jù)庫(kù)初始化，如果應(yīng)用程序要加載一些初始數(shù)據(jù)，identity列的處理方式就比較麻煩，而uniqueidentifier列則無(wú)需任何處理，直接用t-sql加載即可。便于對(duì)某些對(duì)象或常量進(jìn)行永久標(biāo)識(shí)，如類的classi