1、關(guān)于數(shù)據(jù)庫主鍵和外鍵一、什么是主鍵、外鍵關(guān)系型數(shù)據(jù)庫中的一條記錄中有若干個屬性若其中某一個屬性組（注意是組）能唯一標(biāo)識一條記錄該屬性組就可以成為一個主鍵比如學(xué)生表（學(xué)號姓名性別班級），其中每個學(xué)生的學(xué)號是唯一的，學(xué)號就是一個主鍵，課程表（課程編號，課程名，學(xué)分）。其中課程編號是唯一的，課程編號就是一個主鍵。成績表（學(xué)號，課程號，成績）。成績表中單一一個屬性無法唯一標(biāo)識一條記錄學(xué)號和課程號的組合才可以唯一標(biāo)識一條記錄所以學(xué)號和課程號的屬性組是一個主鍵。成績表中的學(xué)號不是成績表的主鍵但它和學(xué)生表中的學(xué)號相對應(yīng)。并且學(xué)生表中的學(xué)號是學(xué)生表的主鍵則稱成績表中的學(xué)號是學(xué)生表的外鍵。同理成績表中的課程號是

2、課程表的外鍵定義主鍵和外鍵主要是為了維護關(guān)系數(shù)據(jù)庫的完整性。總結(jié)一下1 .主鍵是能確定一條記錄的唯一標(biāo)識比如一條記錄包括身份正號姓名年齡。身份證號是唯一能確定你這個人的其他都可能有重復(fù)所以身份證號是主鍵。2 .外鍵用于與另一張表的關(guān)聯(lián)。是能確定另一張表記錄的字段用于保持數(shù)據(jù)的一致性。比如A表中的一個字段是B表的主鍵那他就可以是A表的外鍵。二、主鍵、外鍵和索引的區(qū)別主鍵、外鍵和索引的區(qū)別？主鍵外鍵索引定義：唯一標(biāo)識一條記錄，不能有重復(fù)的，不允許為空表的外鍵是另一表的主鍵，外鍵可以有重復(fù)的，可以是空值該字段沒有重復(fù)值，但可以有一個空值。作用：用來保證數(shù)據(jù)完整性用來和其他表建立聯(lián)系用的是提高查詢排序

3、的速度。個數(shù)：主鍵只能有一個一個表可以有多個外鍵一個表可以有多個惟一索引。聚集索引和非聚集索引的區(qū)別？聚集索引一定是唯一索引。但唯一索引不一定是聚集索引。聚集索引，在索引頁里直接存放數(shù)據(jù)，而非聚集索引在索引頁里存放的是索引，這些索引指向?qū)ｉT的數(shù)據(jù)頁的數(shù)據(jù)。三、數(shù)據(jù)庫中主鍵和外鍵的設(shè)計原則主鍵和外鍵的設(shè)計對物主鍵和外鍵是把多個表組織為一個有效的關(guān)系數(shù)據(jù)庫的粘合劑。理數(shù)據(jù)庫的性能和可用性都有著決定性的影響。必須將數(shù)據(jù)庫模式從理論上的邏輯設(shè)計轉(zhuǎn)換為實際的物理設(shè)計。而主鍵和外鍵的結(jié)構(gòu)是這個設(shè)計過程的癥結(jié)所在。一旦將所設(shè)計的數(shù)據(jù)庫用于了生產(chǎn)環(huán)境就很難對這些鍵進行修改所以在開發(fā)階段就設(shè)計好主鍵和外鍵就是非

4、常必要和值得的。主鍵：關(guān)系數(shù)據(jù)庫依賴于主鍵-它是數(shù)據(jù)庫物理模式的基石。主鍵在物理層面上只有兩個用途：1 .惟一地標(biāo)識一行。2 .作為一個可以被外鍵有效引用的對象。基于以上這兩個用途下面給出了我在設(shè)計物理層面的主鍵時所遵循的一些原則：1 .主鍵應(yīng)當(dāng)是對用戶沒有意義的。如果用戶看到了一個表示多對多關(guān)系的連接表中的數(shù)據(jù)并抱怨它沒有什么用處那就證明它的主鍵設(shè)計地很好。2 .主鍵應(yīng)該是單列的以便提高連接和篩選操作的效率。注：使用復(fù)合鍵的人通常有兩個理由為自己開脫而這兩個理由都是錯誤的。其一是主鍵應(yīng)當(dāng)具有實際意義。然而，讓主鍵具有意義只不過是給人為地破壞數(shù)據(jù)庫提供了方便。其二是利用這種方法可以在描述多對多

5、關(guān)系的連接表中使用兩個外部鍵來作為主鍵。我也反對這種做法，理由是，復(fù)合主鍵常常導(dǎo)致不良的外鍵，即當(dāng)連接表成為另一個從表的主表。而依據(jù)上面的第二種方法成為這個表主鍵的一部分，然，這個表又有可能再成為其它從表的主表。其主鍵又有可能成了其它從表主鍵的一部分，如此傳遞下去，越靠后的從表，其主鍵將會包含越多的列了。3 .永遠也不要更新主鍵。實際上，因為主鍵除了惟一地標(biāo)識一行之外，再沒有其他的用途了。所以也就沒有理由去對它更新。如果主鍵需要更新，則說明主鍵應(yīng)對用戶無意義的原則被違反了。注：這項原則對于那些經(jīng)常需要在數(shù)據(jù)轉(zhuǎn)換或多數(shù)據(jù)庫合并時進行數(shù)據(jù)整理的數(shù)據(jù)并不適用。4 .主鍵不應(yīng)包含動態(tài)變化的數(shù)據(jù)如時間戳

6、、創(chuàng)建時間列、修改時間列等。5 .主鍵應(yīng)當(dāng)有計算機自動生成。如果由人來對主鍵的創(chuàng)建進行干預(yù)，就會使它帶有除了惟一標(biāo)識一行以外的意義。一旦越過這個界限，就可能產(chǎn)生認為修改主鍵的動機。這樣，這種系統(tǒng)用來鏈接記錄行、管理記錄行的關(guān)鍵手段就會落入不了解數(shù)據(jù)庫設(shè)計的人的手中。四、數(shù)據(jù)庫主鍵選取策略我們在建立數(shù)據(jù)庫的時候，需要為每張表指定一個主鍵。所謂主鍵就是能夠唯一標(biāo)識表中某一行的屬性或?qū)傩越M。一個表只能有一個主鍵，但可以有多個候選索引。因為主鍵可以唯一標(biāo)識某一行記錄，所以可以確保執(zhí)行數(shù)據(jù)更新、刪除的時候不會出現(xiàn)張冠李戴的錯誤。當(dāng)然，其它字段可以輔助我們在執(zhí)行這些操作時消除共享沖突，不過就不在這里討論了

7、。主鍵除了上述作用外，常常與外鍵構(gòu)成參照完整性約束，防止出現(xiàn)數(shù)據(jù)不一致。所以數(shù)據(jù)庫在設(shè)計時，主鍵起到了很重要的作用。常見的數(shù)據(jù)庫主鍵選取方式有 自動增長字段 手動增長字段 Uniqueidentifier "COMB(Combine)”類型1自動增長型字段很多數(shù)據(jù)庫設(shè)計者喜歡使用自動增長型字段，因為它使用簡單。自動增長型字段允許我們在向數(shù)據(jù)庫添加數(shù)據(jù)時，不考慮主鍵的取值，記錄插入后，數(shù)據(jù)庫系統(tǒng)會自動為其分配一個值確保絕對不會出現(xiàn)重復(fù)。如果使用SQLServer數(shù)據(jù)庫的話，我們還可以在記錄插入后使用IDENTITY全局變量獲取系統(tǒng)分配的主鍵鍵值。盡管自動增長型字段會省掉我們很多繁瑣的工

8、作，但使用它也存在潛在的問題，那就是在數(shù)據(jù)緩沖模式下很難預(yù)先填寫主鍵與外鍵的值。假設(shè)有兩張表Order(OrderID,OrderDate)OrderDetial(OrderID,LineNum,ProductID,Price)Order表中的OrderID是自動增長型的字段。現(xiàn)在需要我們錄入一張訂單，包括在Order表中插入一條記錄以及在OrderDetail表中插入若干條記錄。因為Order表中的OrderID是自動增長型的字段，那么我們在記錄正式插入到數(shù)據(jù)庫之前無法事先得知它的取值，只有在更新后才能知道數(shù)據(jù)庫為它分配的是什么值。這會造成以下矛盾發(fā)生：首先，為了能在OrderDetail的

9、OrderID字段中添入正確的值，必須先更新Order表以獲取到系統(tǒng)為其分配的OrderID值，然后再用這個OrderID填充OrderDetail表。最后更新OderDetail表。但是，為了確保數(shù)據(jù)的一致性，Order與OrderDetail在更新時必須在事務(wù)保護下同時進行，即確保兩表同時更行成功。顯然它們是相互矛盾的。除此之外，當(dāng)我們需要在多個數(shù)據(jù)庫間進行數(shù)據(jù)的復(fù)制時(SQLServer的數(shù)據(jù)分發(fā)、訂閱機制允許我們進行庫間的數(shù)據(jù)復(fù)制操作)，自動增長型字段可能造成數(shù)據(jù)合并時的主鍵沖突。設(shè)想一個數(shù)據(jù)庫中的Order表向另一個庫中的Order表復(fù)制數(shù)據(jù)庫時OrderID到底該不該自動增長呢？A

10、DO.NET允許我們在DataSet中將某一個字段設(shè)置為自動增長型字段。但千萬記住，這個自動增長字段僅僅是個占位符而已，當(dāng)數(shù)據(jù)庫進行更新時，數(shù)據(jù)庫生成的值會自動取代ADO.NET分配的值。所以為了防止用戶產(chǎn)生誤解，建議大家將ADO.NET中的自動增長初始值以及增量都設(shè)置成-1。此外，在ADO.NET中我們可以為兩張表建立DataRelation這樣存在級聯(lián)關(guān)系的兩張表更新時，一張表更新后另外一張表對應(yīng)鍵的值也會自動發(fā)生變化，這會大大減少了我們對存在級聯(lián)關(guān)系的兩表間更新時自動增長型字段帶來的麻煩。2手動增長型字段既然自動增長型字段會帶來如此的麻煩我們不妨考慮使用手動增長型的字段，也就是說主鍵的值

11、需要自己維護，通常情況下需要建立一張單獨的表存儲當(dāng)前主鍵鍵值。還用上面的例子來說，這次我們新建一張表叫IntKey,包含兩個字段，KeyName以及KeyValue。就像一個HashTable,給一個KeyName,就可以知道目前的KeyValue是什么然后手工實現(xiàn)鍵值數(shù)據(jù)遞增。在SQLServer中可以編寫這樣一個存儲過程，讓取鍵值的過程自動進行。代碼如下：CREATEPROCEDUREGetKeyKeyNamechar(10),KeyValueintOUTPUTASUPDATEIntKeySETKeyValue=KeyValue=KeyValue+1WHEREKeyName=KeyName

12、GO這樣，通過調(diào)用存儲過程，我們可以獲得最新鍵值，確保不會出現(xiàn)重復(fù)。若將OrderID字段設(shè)置為手動增長型字段，我們的程序可以由以下幾步來實現(xiàn)，首先調(diào)用存儲過程獲得一個OrderID,然后使用這個OrderID填充Order表與OrderDetail表，最后在事務(wù)保護下對兩表進行更新。使用手動增長型字段作為主鍵在進行數(shù)據(jù)庫間數(shù)據(jù)復(fù)制時，可以確保數(shù)據(jù)合并過程中不會出現(xiàn)鍵值沖突，只要我們?yōu)椴煌臄?shù)據(jù)庫分配不同的主鍵取值段就行了。但是，使用手動增長型字段會增加網(wǎng)絡(luò)的RoundTrip,我們必須通過增加一次數(shù)據(jù)庫訪問來獲取當(dāng)前主鍵鍵值這會增加網(wǎng)絡(luò)和數(shù)據(jù)庫的負載，當(dāng)處于一個低速或斷開的網(wǎng)絡(luò)環(huán)境中時這種做

13、法會有很大的弊端。同時，手工維護主鍵還要考慮并發(fā)沖突等種種因素這更會增加系統(tǒng)的復(fù)雜程度。3 使用UniqueIdentifierSQLServer為我們提供了UniqueIdentifier數(shù)據(jù)類型，并提供了一個生成函數(shù)NEWID(),使用NEWID()可以生成一個唯一的UniqueIdentifier。UniqueIdentifier在數(shù)據(jù)庫中占用16個字節(jié)，出現(xiàn)重復(fù)的概率非常小，以至于可以認為是0。我們經(jīng)常從注冊表中看到類似45F0EB02-0727-4F2E-AAB5-E8AEDEE0CEC5的東西實際上就是一個UniqueIdentifier,Windows用它來做COM組件以及接口的

14、標(biāo)識防止出現(xiàn)重復(fù)。在.NET里管UniqueIdentifier稱之為GUID(GlobalUniqueIdentifier)。在C#中可以使用如下命令生成一個GUID:Guidu=System.Guid.NewGuid()對于上面提到的Order與OrderDetail的程序，如果選用UniqueIdentifier作為主鍵的話我們完全可以避免上面提到的增加網(wǎng)絡(luò)RoundTrip的問題。通過程序直接生成GUID填充主鍵不用考慮是否會出現(xiàn)重復(fù)。UniqueIdentifier字段也存在嚴(yán)重的缺陷。首先，它的長度是16字節(jié)，是整數(shù)的4倍長會占用大量存儲空間。更為嚴(yán)重的是，UniqueIdenti

15、fier的生成毫無規(guī)律可言，要想在上面建立索引(絕大多數(shù)數(shù)據(jù)庫在主鍵上都有索引)是一個非常耗時的操作。有人做過實驗，插入同樣的數(shù)據(jù)量，使用UniqueIdentifier型數(shù)據(jù)做主鍵要比使用Integer型數(shù)據(jù)慢。所以，出于效率考慮，盡可能避免使用UniqueIdentifier型數(shù)據(jù)庫作為主鍵鍵值。4 使用"COMB(Combine)”類型既然上面三種主鍵類型選取策略都存在各自的缺點，那么到底有沒有好的辦法加以解決呢？答案是肯定的。通過使用COMB類型(數(shù)據(jù)庫中沒有COMB類型。它是JimmyNilsson在他的"TheCostofGUIDsasPrimaryKeys&q

16、uot;文中設(shè)at出來的),可以在三者之間找到一個很好的平衡點。COMB數(shù)據(jù)類型的基本設(shè)計思路是這樣的既然Uniqueidentifier數(shù)據(jù)因毫無規(guī)律可言造成索引效率低下，影響了系統(tǒng)的性能，那么我們能不能通過組合的方式，保留Uniqueidentifier的前10個字節(jié)，用后6個字節(jié)表示GUID生成的時間(DateTime)這樣我們將時間信息與Uniqueidentifier組合起來。在保留Uniqueidentifier的唯一性的同時增加了有序性，以此來提高索引效率。也許有人會擔(dān)心Uniqueidentifier減少到10字節(jié)會造成數(shù)據(jù)出現(xiàn)重復(fù)。其實不用擔(dān)心，后6字節(jié)的時間精度可以達到1/

17、300秒，兩個COMB類型數(shù)據(jù)完全相同的可能性是在這1/300秒內(nèi)生成的兩個GUID前10個字節(jié)完全相同，這幾乎是不可能的！在SQLServer中用SQL命令將這一思路實現(xiàn)出來便是：DECLAREaGuidUNIQUEIDENTIFIERSETaGuid=CAST(CAST(NEWID()ASBINARY(10)+CAST(GETDATE()ASBINARY(6)ASUNIQUEIDENTIFIER)經(jīng)過測試使用COMB做主鍵比使用INT做主鍵在檢索、插入、更新、刪除等操作上仍然顯慢，但比Unidentifier類型要快上一些。關(guān)于測試數(shù)據(jù)可以參考我2004年7月21日的隨筆。除了使用存儲過程

18、實現(xiàn)COMB數(shù)據(jù)外我們也可以使用C姓成COMB數(shù)據(jù)，這樣所有主鍵生成工作可以在客戶端完成。C#(弋碼如下：/=/summary/返回GUID用于數(shù)據(jù)庫操作特定的時間代碼可以提高檢索效率/summary/returnsCOMB(GUID與時間混合型)類型GUID數(shù)據(jù)/returnspublicstaticGuidNewComb()(byteguidArray=System.Guid.NewGuid().ToByteArray()DateTimebaseDate=newDateTime(1900,1,1)DateTimenow=DateTime.Now/Getthedaysandmilliseco

19、ndswhichwillbeusedtobuildthebytestringTimeSpandays=newTimeSpan(now.Ticks-baseDate.Ticks)TimeSpanmsecs=newTimeSpan(now.Ticks-(newDateTime(now.Year,now.Month,now.Day).Ticks)/Converttoabytearray/NotethatSQLServerisaccurateto1/300thofamillisecondsowedivideby3.333333bytedaysArray=BitConverter.GetBytes(da

20、ys.Days);bytemsecsArray=BitConverter.GetBytes(long)(msecs.TotalMilliseconds/3.333333);/ReversethebytestomatchSQLServersorderingArray.Reverse(daysArray)Array.Reverse(msecsArray)/CopythebytesintotheguidArray.Copy(daysArray,daysArray.Length-2,guidArray,guidArray.Length-6,2)Array.Copy(msecsArray,msecsArray.Length-4,guidArray,guidArray.Length-4,4)ReturnnewSystem.Guid(guidArray);/=/summary/從SQLSERVE越回的GUID中生成時間信息/summary/p