mysql数据库外键、主键详解
⼀、什么是主键、外键：
关系型数据库中的⼀条记录中有若⼲个属性，若其中某⼀个属性组(注意是组)能唯⼀标识⼀条记录，该属性组就可以成为⼀个主键
⽐如
学⽣表(学号，姓名，性别，班级)
其中每个学⽣的学号是唯⼀的，学号就是⼀个主键
课程表(课程编号，课程名，学分)
其中课程编号是唯⼀的，课程编号就是⼀个主键
成绩表(学号，课程号，成绩)
成绩表中单⼀⼀个属性⽆法唯⼀标识⼀条记录，学号和课程号的组合才可以唯⼀标识⼀条记录，所以学号和课程号的属性组是⼀个主键
成绩表中的学号不是成绩表的主键，但它和学⽣表中的学号相对应，并且学⽣表中的学号是学⽣表的主键，则称成绩表中的学号是学⽣表的外键
同理成绩表中的课程号是课程表的外键
定义主键和外键主要是为了维护关系数据库的完整性，总结⼀下：
1.主键是能确定⼀条记录的唯⼀标识，⽐如，⼀条记录包括⾝份正号，姓名，年龄。
⾝份证号是唯⼀能确定你这个⼈的，其他都可能有重复，所以，⾝份证号是主键。
2.外键⽤于与另⼀张表的关联。是能确定另⼀张表记录的字段，⽤于保持数据的⼀致性。
⽐如，A表中的⼀个字段，是B表的主键，那他就可以是A表的外键。
⼆、主键、外键和索引的区别
sql语句会⾃动判定查询字段有⽆索引，继⽽使⽤索引去检索
主键、外键和索引的区别？
主键外键索引
定义：唯⼀标识⼀条记录，不能有重复的，不允许
为空
表的外键是另⼀表的主键, 外键可以有重复的, 可以
是空值
该字段没有重复值，但可以有⼀个
空值
作
⽤：
⽤来保证数据完整性⽤来和其他表建⽴联系⽤的是提⾼查询排序的速度
个
数：
主键只能有⼀个⼀个表可以有多个外键⼀个表可以有多个惟⼀索引
聚集索引和⾮聚集索引的区别？
聚集索引⼀定是唯⼀索引。但唯⼀索引不⼀定是聚集索引。
聚集索引，在索引页⾥直接存放数据，⽽⾮聚集索引在索引页⾥存放的是索引，这些索引指向专门的数据页的数据。
三、数据库中主键和外键的设计原则
主键和外键是把多个表组织为⼀个有效的关系数据库的粘合剂。主键和外键的设计对物理数据库的性能和可⽤性都有着决定性的影响。
必须将数据库模式从理论上的逻辑设计转换为实际的物理设计。⽽主键和外键的结构是这个设计过程的症结所在。⼀旦将所设计的数据库⽤于了⽣产环境，就很难对这些键进⾏修改，所以在开发阶段就设计好主键和外键就是⾮常必要和值得的。
主键：
关系数据库依赖于主键---它是数据库物理模式的基⽯。
主键在物理层⾯上只有两个⽤途：
1. 惟⼀地标识⼀⾏。
2. 作为⼀个可以被外键有效引⽤的对象。
基于以上这两个⽤途，下⾯给出了我在设计物理层⾯的主键时所遵循的⼀些原则：
1. 主键应当是对⽤户没有意义的。如果⽤户看到了⼀个表⽰多对多关系的连接表中的数据，并抱怨它没有什么⽤处，那就证明它的主键设计地很好。
2. 主键应该是单列的，以便提⾼连接和筛选操作的效率。
注：使⽤复合键的⼈通常有两个理由为⾃⼰开脱，⽽这两个理由都是错误的。其⼀是主键应当具有实际意义，然⽽，让主键具有意义只不过是给⼈为地破坏数据库提供了⽅便。其⼆是利⽤这种⽅法可以在描述多对多关系的连接表中使⽤两个外部键来作为主键，我也反对这种做法，理由是：复合主键常常导致不良的外键，即当连接表成为另⼀个从表的主表，⽽依据上⾯的第⼆种⽅法成为这个表主键的⼀部分，然，这个表⼜有可能再成为其它从表的主表，其主键⼜有可能成了其它从表主键的⼀部分，如此传递下去，越靠后的从表，其主键将会包含越多的列了。
3. 永远也不要更新主键。实际上，因为主键除了惟⼀地标识⼀⾏之外，再没有其他的⽤途了，所以也就没有理由去对它更新。如果主键需要更新，则说明主键应对⽤户⽆意义的原则被违反了。
注：这项原则对于那些经常需要在数据转换或多数据库合并时进⾏数据整理的数据并不适⽤。
4. 主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等。
5. 主键应当有计算机⾃动⽣成。如果由⼈来对主键的创建进⾏⼲预，就会使它带有除了惟⼀标识⼀⾏以外的意义。⼀旦越过这个界限，就可能产⽣认为修改主键的动机，这样，这种系统⽤来链接记录⾏、管理记录⾏的关键⼿段就会落⼊不了解数据库设计的⼈的⼿中。
四、数据库主键选取策略
我们在建⽴数据库的时候，需要为每张表指定⼀个主键，所谓主键就是能够唯⼀标识表中某⼀⾏的属性或属性组，⼀个表只能有⼀个主键，但可以有多个候选索引。因为主键可以唯⼀标识某⼀⾏记录，所以可以确保执⾏数据更新、删除的时候不会出现张冠李戴的错误。当然，其它字段可以辅助我们在执⾏这些操作时消除共享冲突，不过就不在这⾥讨论了。主键除了上述作⽤外，常常与外键构成参照完整性约束，防⽌出现数据不⼀致。所以数据库在设计时，主键起到了很重要的作⽤。
常见的数据库主键选取⽅式有：
· ⾃动增长字段
· ⼿动增长字段
· UniqueIdentifier
· “COMB（Combine）”类型
1⾃动增长型字段
很多数据库设计者喜欢使⽤⾃动增长型字段，因为它使⽤简单。⾃动增长型字段允许我们在向数据库添加数据时，不考虑主键的取值，记录插⼊后，数据库系统会⾃动为其分配⼀个值，确保绝对不会出现重复。如果使⽤SQL Server数据库的话，我们还可以在记录插⼊后使⽤
@@IDENTITY全局变量获取系统分配的主键键值。
尽管⾃动增长型字段会省掉我们很多繁琐的⼯作，但使⽤它也存在潜在的问题，那就是在数据缓冲模式下，很难预先填写主键与外键的值。假设有两张表：
Order(OrderID, OrderDate)mysql删除重复的数据保留一条
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;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。