关于数据库主键和外键
一、什么是主鍵、外鍵：
關系型數據庫中的一條記錄中有若干個屬性，若其中某一個屬性組(注意是組)能唯一標識一條記錄，該屬性組就可以成為一個主鍵?
比如??
學生表(學號，姓名，性別，班級)?
其中每個學生的學號是唯一的，學號就是一個主鍵?
課程表(課程編號,課程名,學分)?
其中課程編號是唯一的,課程編號就是一個主鍵?
成績表(學號,課程號,成績)?
成績表中單一一個屬性無法唯一標識一條記錄，學號和課程號的組合才可以唯一標識一條記錄，所以?學號和課程號的屬性組是一個主鍵?
??
成績表中的學號不是成績表的主鍵，但它和學生表中的學號相對應，并且學生表中的學號是學生表的主鍵，則稱成績表中的學號是學生表的外鍵?
??
同理?成績表中的課程號是課程表的外鍵?
??
定義主鍵和外鍵主要是為了維護關系數據庫的完整性，總結一下：
1.主鍵是能確定一條記錄的唯一標識，比如，一條記錄包括身份正號，姓名，年齡。
身份證號是唯一能確定你這個人的，其他都可能有重復，所以，身份證號是主鍵。?
2.外鍵用于與另一張表的關聯。是能確定另一張表記錄的字段，用于保持數據的一致性。
比如，A表中的一個字段，是B表的主鍵，那他就可以是A表的外鍵。
?
?
二、??主鍵、外鍵和索引的區別?
主鍵、外鍵和索引的區別？
| ? | 主鍵 | 外鍵 | 索引 |
| 定義： | 唯一標識一條記錄，不能有重復的，不允許為空 | 表的外鍵是另一表的主鍵,?外鍵可以有重復的,?可以是空值 | 該字段沒有重復值，但可以有一個空值 |
| 作用： | 用來保證數據完整性 | 用來和其他表建立聯系用的 | 是提高查詢排序的速度 |
| 個數： | 主鍵只能有一個 | 一個表可以有多個外鍵 | 一個表可以有多個惟一索引 |
?
聚集索引和非聚集索引的區別？
聚集索引一定是唯一索引。但唯一索引不一定是聚集索引。??
聚集索引，在索引頁里直接存放數據，而非聚集索引在索引頁里存放的是索引，這些索引指向專門的數據頁的數據。
?
?
?
?
三、數據庫中主鍵和外鍵的設計原則
主鍵和外鍵是把多個表組織為一個有效的關系數據庫的粘合劑。主鍵和外鍵的設計對物理數據庫的性能和可用性都有著決定性的影響。
必須將數據庫模式從理論上的邏輯設計轉換為實際的物理設計。而主鍵和外鍵的結構是這個設計過程的癥結所在。一旦將所設計的數據庫用于了生產環境，就很難對這些鍵進行修改，所以在開發階段就設計好主鍵和外鍵就是非常必要和值得的。
主鍵：
??關系數據庫依賴于主鍵---它是數據庫物理模式的基石。
??主鍵在物理層面上只有兩個用途：
????????1.?惟一地標識一行。
????????2.?作為一個可以被外鍵有效引用的對象。
??基于以上這兩個用途，下面給出了我在設計物理層面的主鍵時所遵循的一些原則：
????????1.?主鍵應當是對用戶沒有意義的。如果用戶看到了一個表示多對多關系的連接表中的數據，并抱怨它沒有什么用處，那就證明它的主鍵設計地很好。
????????2.?主鍵應該是單列的，以便提高連接和篩選操作的效率。
????????注：使用復合鍵的人通常有兩個理由為自己開脫，而這兩個理由都是錯誤的。其一是主鍵應當具有實際意義，然而，讓主鍵具有意義只不過是給人為地破壞數據庫提供了方便。其二是利用這種方法可以在描述多對多關系的連接表中使用兩個外部鍵來作為主鍵，我也反對這種做法，理由是：復合主鍵常常導致不良的外鍵，即當連接表成為另一個從表的主表，而依據上面的第二種方法成為這個表主鍵的一部分，然，這個表又有可能再成為其它從表的主表，其主鍵又有可能成了其它從表主鍵的一部分，如此傳遞下去，越靠后的從表，其主鍵將會包含越多的列了。
????????3.?永遠也不要更新主鍵。實際上，因為主鍵除了惟一地標識一行之外，再沒有其他的用途了，所以也就沒有理由去對它更新。如果主鍵需要更新，則說明主鍵應對用戶無意義的原則被違反了。
???????注：這項原則對于那些經常需要在數據轉換或多數據庫合并時進行數據整理的數據并不適用。
????????4.?主鍵不應包含動態變化的數據，如時間戳、創建時間列、修改時間列等。
????????5.?主鍵應當有計算機自動生成。如果由人來對主鍵的創建進行干預，就會使它帶有除了惟一標識一行以外的意義。一旦越過這個界限，就可能產生認為修改主鍵的動機，這樣，這種系統用來鏈接記錄行、管理記錄行的關鍵手段就會落入不了解數據庫設計的人的手中。
?
四、數據庫主鍵選取策略
我們在建立數據庫的時候，需要為每張表指定一個主鍵，所謂主鍵就是能夠唯一標識表中某一行的屬性或屬性組，一個表只能有一個主鍵，但可以有多個候選索引。因為主鍵可以唯一標識某一行記錄，所以可以確保執行數據更新、刪除的時候不會出現張冠李戴的錯誤。當然，其它字段可以輔助我們在執行這些操作時消除共享沖突，不過就不在這里討論了。主鍵除了上述作用外，常常與外鍵構成參照完整性約束，防止出現數據不一致。所以數據庫在設計時，主鍵起到了很重要的作用。
常見的數據庫主鍵選取方式有：
·?自動增長字段
·?手動增長字段
·?UniqueIdentifier
·?“COMB（Combine）”類型
1自動增長型字段
很多數據庫設計者喜歡使用自動增長型字段，因為它使用簡單。自動增長型字段允許我們在向數據庫添加數據時，不考慮主鍵的取值，記錄插入后，數據庫系統會自動為其分配一個值，確保絕對不會出現重復。如果使用SQL?Server數據庫的話，我們還可以在記錄插入后使用@@IDENTITY全局變量獲取系統分配的主鍵鍵值。
盡管自動增長型字段會省掉我們很多繁瑣的工作，但使用它也存在潛在的問題，那就是在數據緩沖模式下，很難預先填寫主鍵與外鍵的值。假設有兩張表：
Order(OrderID,?OrderDate)
OrderDetial(OrderID,?LineNum,?ProductID,?Price)
Order表中的OrderID是自動增長型的字段。現在需要我們錄入一張訂單，包括在Order表中插入一條記錄以及在OrderDetail表中插入若干條記錄。因為Order表中的OrderID是自動增長型的字段，那么我們在記錄正式插入到數據庫之前無法事先得知它的取值，只有在更新后才能知道數據庫為它分配的是什么值。這會造成以下矛盾發生：
首先，為了能在OrderDetail的OrderID字段中添入正確的值，必須先更新Order表以獲取到系統為其分配的OrderID值，然后再用這個OrderID填充OrderDetail表。最后更新OderDetail表。但是，為了確保數據的一致性，Order與OrderDetail在更新時必須在事務保護下同時進行，即確保兩表同時更行成功。顯然它們是相互矛盾的。
除此之外，當我們需要在多個數據庫間進行數據的復制時（SQL?Server的數據分發、訂閱機制允許我們進行庫間的數據復制操作），自動增長型字段可能造成數據合并時的主鍵沖突。設想一個數據庫中的Order表向另一個庫中的Order表復制數據庫時，OrderID到底該不該自動增長呢？
ADO.NET允許我們在DataSet中將某一個字段設置為自動增長型字段，但千萬記住，這個自動增長字段僅僅是個占位符而已，當數據庫進行更新時，數據庫生成的值會自動取代ADO.Net分配的值。所以為了防止用戶產生誤解，建議大家將ADO.NET中的自動增長初始值以及增量都設置成-1。此外，在ADO.NET中，我們可以為兩張表建立DataRelation，這樣存在級聯關系的兩張表更新時，一張表更新后另外一張表對應鍵的值也會自動發生變化，這會大大減少了我們對存在級聯關系的兩表間更新時自動增長型字段帶來的麻煩。
2手動增長型字段
既然自動增長型字段會帶來如此的麻煩，我們不妨考慮使用手動增長型的字段，也就是說主鍵的值需要自己維護，通常情況下需要建立一張單獨的表存儲當前主鍵鍵值。還用上面的例子來說，這次我們新建一張表叫IntKey，包含兩個字段，KeyName以及KeyValue。就像一個HashTable，給一個KeyName，就可以知道目前的KeyValue是什么，然后手工實現鍵值數據遞增。在SQL?Server中可以編寫這樣一個存儲過程，讓取鍵值的過程自動進行。代碼如下：
CREATE?PROCEDURE?[GetKey]
@KeyName?char(10),?
@KeyValue?int?OUTPUT?
AS
UPDATE?IntKey?SET?@KeyValue?=?KeyValue?=?KeyValue?+?1?WHERE?KeyName?=?@KeyName
Go
這樣，通過調用存儲過程，我們可以獲得最新鍵值，確保不會出現重復。若將OrderID字段設置為手動增長型字段，我們的程序可以由以下幾步來實現：首先調用存儲過程，獲得一個OrderID，然后使用這個OrderID填充Order表與OrderDetail表，最后在事務保護下對兩表進行更新。
使用手動增長型字段作為主鍵在進行數據庫間數據復制時，可以確保數據合并過程中不會出現鍵值沖突，只要我們為不同的數據庫分配不同的主鍵取值段就行了。但是，使用手動增長型字段會增加網絡的RoundTrip，我們必須通過增加一次數據庫訪問來獲取當前主鍵鍵值，這會增加網絡和數據庫的負載，當處于一個低速或斷開的網絡環境中時，這種做法會有很大的弊端。同時，手工維護主鍵還要考慮并發沖突等種種因素，這更會增加系統的復雜程度。
3使用UniqueIdentifier
SQL?Server為我們提供了UniqueIdentifier數據類型，并提供了一個生成函數NEWID(?)，使用NEWID(?)可以生成一個唯一的UniqueIdentifier。UniqueIdentifier在數據庫中占用16個字節，出現重復的概率非常小，以至于可以認為是0。我們經常從注冊表中看到類似
{45F0EB02-0727-4F2E-AAB5-E8AEDEE0CEC5}
的東西實際上就是一個UniqueIdentifier，Windows用它來做COM組件以及接口的標識，防止出現重復。在.NET里管UniqueIdentifier稱之為GUID（Global?Unique?Identifier）。在C#中可以使用如下命令生成一個GUID：
Guid?u?=?System.Guid.NewGuid();
對于上面提到的Order與OrderDetail的程序，如果選用UniqueIdentifier作為主鍵的話，我們完全可以避免上面提到的增加網絡RoundTrip的問題。通過程序直接生成GUID填充主鍵，不用考慮是否會出現重復。
UniqueIdentifier字段也存在嚴重的缺陷：首先，它的長度是16字節，是整數的4倍長，會占用大量存儲空間。更為嚴重的是，UniqueIdentifier的生成毫無規律可言，要想在上面建立索引（絕大多數數據庫在主鍵上都有索引）是一個非常耗時的操作。有人做過實驗，插入同樣的數據量，使用UniqueIdentifier型數據做主鍵要比使用Integer型數據慢，所以，出于效率考慮，盡可能避免使用UniqueIdentifier型數據庫作為主鍵鍵值。
4使用“COMB（Combine）”類型
既然上面三種主鍵類型選取策略都存在各自的缺點，那么到底有沒有好的辦法加以解決呢？答案是肯定的。通過使用COMB類型（數據庫中沒有COMB類型，它是Jimmy?Nilsson在他的“The?Cost?of?GUIDs?as?Primary?Keys”一文中設計出來的），可以在三者之間找到一個很好的平衡點。
COMB數據類型的基本設計思路是這樣的：既然UniqueIdentifier數據因毫無規律可言造成索引效率低下，影響了系統的性能，那么我們能不能通過組合的方式，保留UniqueIdentifier的前10個字節，用后6個字節表示GUID生成的時間（DateTime），這樣我們將時間信息與UniqueIdentifier組合起來，在保留UniqueIdentifier的唯一性的同時增加了有序性，以此來提高索引效率。也許有人會擔心UniqueIdentifier減少到10字節會造成數據出現重復，其實不用擔心，后6字節的時間精度可以達到1/300秒，兩個COMB類型數據完全相同的可能性是在這1/300秒內生成的兩個GUID前10個字節完全相同，這幾乎是不可能的！在SQL?Server中用SQL命令將這一思路實現出來便是：
DECLARE?@aGuid?UNIQUEIDENTIFIER
SET?@aGuid?=?CAST(CAST(NEWID()?AS?BINARY(10))?
+?CAST(GETDATE()?AS?BINARY(6))?AS?UNIQUEIDENTIFIER)
經過測試，使用COMB做主鍵比使用INT做主鍵，在檢索、插入、更新、刪除等操作上仍然顯慢，但比Unidentifier類型要快上一些。關于測試數據可以參考我2004年7月21日的隨筆。
除了使用存儲過程實現COMB數據外，我們也可以使用C#生成COMB數據，這樣所有主鍵生成工作可以在客戶端完成。C#代碼如下：
//================================================================
///<summary>
///?返回?GUID?用于數據庫操作，特定的時間代碼可以提高檢索效率
///?</summary>
///?<returns>COMB?(GUID?與時間混合型)?類型?GUID?數據</returns>
public?static?Guid?NewComb()?
{?
?????byte[]?guidArray?=?System.Guid.NewGuid().ToByteArray();?
?????DateTime?baseDate?=?new?DateTime(1900,1,1);?
?????DateTime?now?=?DateTime.Now;?
?????//?Get?the?days?and?milliseconds?which?will?be?used?to?build?the?byte?string?
?????TimeSpan?days?=?new?TimeSpan(now.Ticks?-?baseDate.Ticks);?
?????TimeSpan?msecs?=?new?TimeSpan(now.Ticks?-?(new?DateTime(now.Year,?now.Month,?now.Day).Ticks));?
?????//?Convert?to?a?byte?array?
?????//?Note?that?SQL?Server?is?accurate?to?1/300th?of?a?millisecond?so?we?divide?by?3.333333?
?????byte[]?daysArray?=?BitConverter.GetBytes(days.Days);?
?????byte[]?msecsArray?=?BitConverter.GetBytes((long)(msecs.TotalMilliseconds/3.333333));?
?????//?Reverse?the?bytes?to?match?SQL?Servers?ordering?
?????Array.Reverse(daysArray);?
?????Array.Reverse(msecsArray);?
?????//?Copy?the?bytes?into?the?guid?
?????Array.Copy(daysArray,?daysArray.Length?-?2,?guidArray,?guidArray.Length?-?6,?2);?
?????Array.Copy(msecsArray,?msecsArray.Length?-?4,?guidArray,?guidArray.Length?-?4,?4);?
?????return?new?System.Guid(guidArray);?
}?
//================================================================
///?<summary>
///?從?SQL?SERVER?返回的?GUID?中生成時間信息
///?</summary>
///?<param?name="guid">包含時間信息的?COMB?</param>
///?<returns>時間</returns>
public?static?DateTime?GetDateFromComb(System.Guid?guid)?
{?
?????DateTime?baseDate?=?new?DateTime(1900,1,1);?
?????byte[]?daysArray?=?new?byte[4];?
?????byte[]?msecsArray?=?new?byte[4];?
?????byte[]?guidArray?=?guid.ToByteArray();?
?????//?Copy?the?date?parts?of?the?guid?to?the?respective?byte?arrays.?
?????Array.Copy(guidArray,?guidArray.Length?-?6,?daysArray,?2,?2);?
?????Array.Copy(guidArray,?guidArray.Length?-?4,?msecsArray,?0,?4);?
?????//?Reverse?the?arrays?to?put?them?into?the?appropriate?order?
?????Array.Reverse(daysArray);?
?????Array.Reverse(msecsArray);?
?????//?Convert?the?bytes?to?ints?
?????int?days?=?BitConverter.ToInt32(daysArray,?0);?
?????int?msecs?=?BitConverter.ToInt32(msecsArray,?0);?
?????DateTime?date?=?baseDate.AddDays(days);?
?????date?=?date.AddMilliseconds(msecs?*?3.333333);?
?????return?date;?
}?
總結
以上是生活随笔為你收集整理的关于数据库主键和外键的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 咋得水晶
- 下一篇： hash函数查找和ASL计算