精华内容
下载资源
问答
  • 2.使用自增主键的场景:a:中找不出3个以内字段可以表示的业务主键b:没有集中大量的插入操作c:不使用自增id作为外键(即id变化不影响系统逻辑和功能)此外自增主键的规律很明显,为了不让别...

    主键的设置方法有3种:

    一种是设置自增长主键,第二种是采用业务主键,第三种是生成唯一序列(使用uuid/guid);

    如何选择主键设置方式:

    1.主键的使用目的:

    a.为了保证数据查找唯一;

    b.提高存储效率和索引效率。

    2.使用自增主键的场景:

    a:表中找不出3个以内字段可以表示的业务主键

    b:没有集中大量的插入操作

    c:不使用自增id作为外键(即id变化不影响系统逻辑和功能)

    此外自增主键的规律很明显,为了不让别人轻易的采集数据,同时符合以上条件,则优先选择自增主键;

    3.使用业务主键做id:

    a.如果你设计的表上存在非重复数据列,且常以该列检索数据,或该列还关联其他表的外键,譬如学号、会员卡号、身份证号这些,可以用来做为主键,主键在默认设置下      是聚集索引,这样检索学号、卡号这些效率上就会比较高。

    4.使用生成唯一的序列

    a.如果不满足使用自增的要求,则建议优先使用字符主键。类似网站数据库,可以采用uuid、guid这类无规则字符做主键。因为字符主键查询速度不比自增主键慢。

    b.考虑自增主键的弊端。很多表的ID会被其他表引用为外键,而且自增主键弊端是,用DELETE删除后,不会从新接着自增,而是接着删除前的ID自增;在多数据库迁移          下,自增则会很繁琐,需要另外做联合主键才能保证数据的唯一性。

    展开全文
  • 缺点:无法转移数据库,比如把中的一批数据 转移 或 附带到 另一个中,那么由于是自增长字段,那么会导致无法转移,因为另外一个可能已经存在部分数据,会造成主键冲突。自增长字段的缺陷。业务数据的完整性,...

    1、旧系统或者单部署系统,一般都采用自增主键,主要是便捷性考虑。优缺点如下:

    优点:自增长字段往往用integer bigint类型,最多占8个字节。索引与外键 所占用的空间连带减少,增删改查 效率高。业务变化,不影响,不需要更新主键。
    缺点:无法转移数据库,比如把表中的一批数据 转移 或 附带到 另一个表中,那么由于是自增长字段,那么会导致无法转移,因为另外一个表可能已经存在部分数据,会造成主键冲突。自增长字段的缺陷。业务数据的完整性,无法保证。

    2、对于高并发业务型数据表,尤其是分布式部署架构,一般建议尽量使用业务主键,主要是考虑到查询效率、安全性以及分表分库等的情况,优缺点如下:

    优点:可以转移数据库,最大化节省了空间,因为并没有多增加一个非业务字段做主键。可以保证业务逻辑的完整性。避免产生垃圾数据,银行就是用业务字段做主键的,虽然效率低,但是安全。
    缺点:如果业务发生改变,有可能需要修改主键,举例:国家A表用身份证号做主键,然后其他很多表中的身份证号这列都是来自身份证表A中的主键(即外键),那么如果身份证号升级,比如从1代升级到2代,那么连带的表的外键 的索引 通通都得发生变化,效率极低 因为会连带更新一串用到这个外键的表,可见用业务字段做主键的话,要保证主键不经常变化。

    展开全文
  • 三、数据库主键和外键的设计原则主键和外键是把多个组织为一个有效的关系数据库的粘合剂。主键和外键的设计对物理数据库的性能可用性都有着决定性的影响。必须将数据库模式从理论上的逻辑设计转换为实际的物理...

    三、数据库中主键和外键的设计原则

    主键和外键是把多个表组织为一个有效的关系数据库的粘合剂。主键和外键的设计对物理数据库的性能和可用性都有着决定性的影响。

    必须将数据库模式从理论上的逻辑设计转换为实际的物理设计。而主键和外键的结构是这个设计过程的症结所在。一旦将所设计的数据库用于了生产环境,就很难对这些键进行修改,所以在开发阶段就设计好主键和外键就是非常必要和值得的。

    主键:

    关系数据库依赖于主键---它是数据库物理模式的基石。主键在物理层面上只有两个用途:

    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中可以编写这样一个存储过程,让取键值的过程自动进行。代码如下:

    a4c26d1e5885305701be709a3d33442f.pngCREATE PROCEDURE [GetKey]

    a4c26d1e5885305701be709a3d33442f.png

     a4c26d1e5885305701be709a3d33442f.png

     @KeyName char(10), a4c26d1e5885305701be709a3d33442f.png

     @KeyValue int OUTPUT a4c26d1e5885305701be709a3d33442f.png

     a4c26d1e5885305701be709a3d33442f.png

    AS

    a4c26d1e5885305701be709a3d33442f.png

    UPDATE IntKey SET @KeyValue = KeyValue = KeyValue + 1 WHERE KeyName = @KeyName

    a4c26d1e5885305701be709a3d33442f.png

    GO

    a4c26d1e5885305701be709a3d33442f.png

    这样,通过调用存储过程,我们可以获得最新键值,确保不会出现重复。若将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:

    a4c26d1e5885305701be709a3d33442f.pngGuid 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命令将这一思路实现出来便是:

    a4c26d1e5885305701be709a3d33442f.pngDECLARE @aGuid UNIQUEIDENTIFIER

    a4c26d1e5885305701be709a3d33442f.png

     a4c26d1e5885305701be709a3d33442f.png

     SET @aGuid = CAST(CAST(NEWID() AS BINARY(10)) a4c26d1e5885305701be709a3d33442f.png

    + CAST(GETDATE() AS BINARY(6)) AS UNIQUEIDENTIFIER)

    经过测试,使用COMB做主键比使用INT做主键,在检索、插入、更新、删除等操作上仍然显慢,但比Unidentifier类型要快上一些。关于测试数据可以参考我2004年7月21日的随笔。

    除了使用存储过程实现COMB数据外,我们也可以使用C#生成COMB数据,这样所有主键生成工作可以在客户端完成。C#代码如下:

    a4c26d1e5885305701be709a3d33442f.png//================================================================a4c26d1e5885305701be709a3d33442f.png///a4c26d1e5885305701be709a3d33442f.png///返回 GUID 用于数据库操作,特定的时间代码可以提高检索效率

    a4c26d1e5885305701be709a3d33442f.png///a4c26d1e5885305701be709a3d33442f.png///COMB (GUID 与时间混合型) 类型 GUID 数据

    a4c26d1e5885305701be709a3d33442f.png

     public static Guid NewComb() a4c26d1e5885305701be709a3d33442f.png

    a4c26d1e5885305701be709a3d33442f.png

     byte[] guidArray = System.Guid.NewGuid().ToByteArray(); a4c26d1e5885305701be709a3d33442f.png

     DateTime baseDate = new DateTime(1900,1,1); a4c26d1e5885305701be709a3d33442f.png

     DateTime now = DateTime.Now; a4c26d1e5885305701be709a3d33442f.png

     //Get the days and milliseconds which will be used to build the byte stringa4c26d1e5885305701be709a3d33442f.png TimeSpan days = new TimeSpan(now.Ticks - baseDate.Ticks); a4c26d1e5885305701be709a3d33442f.png

     TimeSpan msecs = new TimeSpan(now.Ticks - (new DateTime(now.Year, now.Month, now.Day).Ticks)); a4c26d1e5885305701be709a3d33442f.png

     a4c26d1e5885305701be709a3d33442f.png

     //Convert to a byte array a4c26d1e5885305701be709a3d33442f.png//Note that SQL Server is accurate to 1/300th of a millisecond so we divide by 3.333333a4c26d1e5885305701be709a3d33442f.png byte[] daysArray = BitConverter.GetBytes(days.Days); a4c26d1e5885305701be709a3d33442f.png

     byte[] msecsArray = BitConverter.GetBytes((long)(msecs.TotalMilliseconds/3.333333)); a4c26d1e5885305701be709a3d33442f.png

     a4c26d1e5885305701be709a3d33442f.png

     //Reverse the bytes to match SQL Servers orderinga4c26d1e5885305701be709a3d33442f.png Array.Reverse(daysArray); a4c26d1e5885305701be709a3d33442f.png

     Array.Reverse(msecsArray); a4c26d1e5885305701be709a3d33442f.png

     a4c26d1e5885305701be709a3d33442f.png

     //Copy the bytes into the guida4c26d1e5885305701be709a3d33442f.png Array.Copy(daysArray, daysArray.Length - 2, guidArray, guidArray.Length - 6, 2); a4c26d1e5885305701be709a3d33442f.png

     Array.Copy(msecsArray, msecsArray.Length - 4, guidArray, guidArray.Length - 4, 4); a4c26d1e5885305701be709a3d33442f.png

     a4c26d1e5885305701be709a3d33442f.png

     return new System.Guid(guidArray); a4c26d1e5885305701be709a3d33442f.png

     } a4c26d1e5885305701be709a3d33442f.png

     a4c26d1e5885305701be709a3d33442f.png

     //================================================================a4c26d1e5885305701be709a3d33442f.png///a4c26d1e5885305701be709a3d33442f.png///从 SQL SERVER 返回的 GUID 中生成时间信息

    a4c26d1e5885305701be709a3d33442f.png///a4c26d1e5885305701be709a3d33442f.png///name="guid">包含时间信息的 COMBa4c26d1e5885305701be709a3d33442f.png///时间

    a4c26d1e5885305701be709a3d33442f.png

     public static DateTime GetDateFromComb(System.Guid guid) a4c26d1e5885305701be709a3d33442f.png

    a4c26d1e5885305701be709a3d33442f.png DateTime baseDate = new DateTime(1900,1,1); a4c26d1e5885305701be709a3d33442f.png

     byte[] daysArray = new byte[4]; a4c26d1e5885305701be709a3d33442f.png

     byte[] msecsArray = new byte[4]; a4c26d1e5885305701be709a3d33442f.png

     byte[] guidArray = guid.ToByteArray(); a4c26d1e5885305701be709a3d33442f.png

     a4c26d1e5885305701be709a3d33442f.png

     //Copy the date parts of the guid to the respective byte arrays.a4c26d1e5885305701be709a3d33442f.png Array.Copy(guidArray, guidArray.Length - 6, daysArray, 2, 2); a4c26d1e5885305701be709a3d33442f.png

     Array.Copy(guidArray, guidArray.Length - 4, msecsArray, 0, 4); a4c26d1e5885305701be709a3d33442f.png

     a4c26d1e5885305701be709a3d33442f.png

     //Reverse the arrays to put them into the appropriate ordera4c26d1e5885305701be709a3d33442f.png Array.Reverse(daysArray); a4c26d1e5885305701be709a3d33442f.png

     Array.Reverse(msecsArray); a4c26d1e5885305701be709a3d33442f.png

     a4c26d1e5885305701be709a3d33442f.png

     //Convert the bytes to intsa4c26d1e5885305701be709a3d33442f.png int days = BitConverter.ToInt32(daysArray, 0); a4c26d1e5885305701be709a3d33442f.png

     int msecs = BitConverter.ToInt32(msecsArray, 0); a4c26d1e5885305701be709a3d33442f.png

     a4c26d1e5885305701be709a3d33442f.png

     DateTime date = baseDate.AddDays(days); a4c26d1e5885305701be709a3d33442f.png

     date = date.AddMilliseconds(msecs * 3.333333); a4c26d1e5885305701be709a3d33442f.png

     a4c26d1e5885305701be709a3d33442f.png

    return date;

    a4c26d1e5885305701be709a3d33442f.png}

    展开全文
  • 一、什么是主键、外键:关系型数据库中的一条记录中有若干个属性,若其中某一个属性组(注意是组)能唯一标识一条记录,该属性组就可以成为一个主键比如学生(学号,姓名,性别,班级)其中每个学生的学号是唯一的,...

    一、什么是主键、外键:

    关系型数据库中的一条记录中有若干个属性,若其中某一个属性组(注意是组)能唯一标识一条记录,该属性组就可以成为一个主键

    比如

    学生表(学号,姓名,性别,班级)

    其中每个学生的学号是唯一的,学号就是一个主键

    课程表(课程编号,课程名,学分)

    其中课程编号是唯一的,课程编号就是一个主键

    成绩表(学号,课程号,成绩)

    成绩表中单一一个属性无法唯一标识一条记录,学号和课程号的组合才可以唯一标识一条记录,所以 学号和课程号的属性组是一个主键

    成绩表中的学号不是成绩表的主键,但它和学生表中的学号相对应,并且学生表中的学号是学生表的主键,则称成绩表中的学号是学生表的外键

    同理 成绩表中的课程号是课程表的外键

    定义主键和外键主要是为了维护关系数据库的完整性,总结一下:

    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#代码如下:

    //================================================================

    ///

    /// 返回 GUID 用于数据库操作,特定的时间代码可以提高检索效率

    /// 

    /// COMB (GUID 与时间混合型) 类型 GUID 数据

    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);

    }

    //================================================================

    /// 

    /// 从 SQL SERVER 返回的 GUID 中生成时间信息

    /// 

    /// 包含时间信息的 COMB 

    /// 时间

    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;

    }

    展开全文
  • 1.C.J.Date关于分布式数据库的12条告诫本地站点的独立性。每个本地站点都有一个独立自主的集中式DBMS,每个站点都必须保证安全性,并发控制,备份恢复中心站点。在网络中的站点都与中心站点或其它站点无关,所有...
  • mysql数据库外键、主键详解

    千次阅读 2021-01-18 18:41:25
    一、什么是主键、外键:关系型数据库中的一条记录中有若干个属性,若其中某一个属性组(注意是组)能唯一标识一条记录,该属性组就可以成为一个主键比如学生(学号,姓名,性别,班级)其中每个学生的学号是唯一的,...
  • MySQL数据库的主键外键详解主键主键的定义主键:中经常有一个列或多列的组合,其值能唯一地标识中的每一行。这样的一列或多列称为的主键,通过它可强制的实体完整性。当创建或更改时可通过定义 PRIMARY ...
  • 主键和外键是把多个组织为一个有效的关系数据库的粘合剂。主键和外键的设计对物理数据库的性能可用性都有着决定性的影响。必须将数据库模式从理论上的逻辑设计转换为实际的物理设计。而主键和...
  • 主键和外键是把多个组织为一个有效的关系数据库的粘合剂。主键和外键的设计对物理数据库的性能可用性都有着决定性的影响。必须将数据库模式从理论上的逻辑设计转换为实际的物理设计。而主键和外键的结构是这个...
  • 一般来说关系型数据库,绝大多数都有数据库主键数据库主键的创建,一般有如下几种形式:1. 使用数据库自增长主键的语法。有些数据库,比如 MS SQL Server, MySQL ,都有对应的语法,可以在创建数据库表的时候,...
  • 数据库 既是主键又是外键

    千次阅读 2021-01-27 19:16:43
    数据蒋堂 | JOIN延伸 - 维度概念谈到数据分析时常常会用到维度这个词,针对数据立方体的钻取、旋转、切片等操作都是围绕维度进行的,几乎所有的数据分析人员都知道...文章技术小能手2017-12-291112浏览量数据库对象...
  • 设置MySQL数据表主键

    千次阅读 2021-01-28 00:39:07
    设置MySQL数据表主键:使用“primary key”关键字创建主键数据列。被设置为主键列不允许出现重复的值,很多情况下与“auto_increment”递增数字相结合。如下SQL语句所示:Mysql>createtablebooks(bookidint(11)...
  • 数据库的索引与主键

    2021-04-19 15:23:04
    首先提出问题: 1、CLUSTER是什么,主键是聚集索引吗? 2、提高数据库查询性能,分区中的数据... 一种索引,该索引中键值的逻辑顺序决定了中相应行的物理顺序(我们把这种正文内容本身就是一种按照一定规则排列的..
  • 本篇文章简单介绍一下聚集索引和主键的区别1主键的概念主关键字(主键,primary key)是被挑选出来,作的行的唯一标识的候选关键字。一个只有一个主关键字。主关键字又可以称为主键主键可以由一个字段,也可以....
  • 什么是主键、外键: 关系型数据库中的一条记录中有若干个属性,若其中某一个属性组(注意是组)能唯一标识一条记录,该属性组就可以成为一个主键 比如: 学生(学号,姓名,性别,班级)其中每个学生的学号是唯一的,...
  • 主键的必要性:有些朋友可能不提倡数据库表必须要主键,但在我的思考中,觉得每个都应该具有主键,不管是单主键还是双主键主键的存在就代表着结构的完整性,的记录必须得有唯一区分的字段,主键主要是用于...
  • 在MySQL中如何设置主键和外键

    千次阅读 2021-01-19 12:59:41
    3、如原已有主键,先把原来的主键删除掉,通过DROP PRIMARY KEY命令:ALTER TABLE `jingyan` DROP PRIMARY KEY;。4、主键已经没有了。5、通过命令:ADD PRIMARY KEY来添加ALTER TABLE `jingy...
  • 一、确立主键规范每个了解数据库设计的人都知道,主键对于一张来说是一个很重要,甚至必需的部分。这确实是事实,主键是好的数据库设计的一部分。主键数据库确保数据行在整张唯一性的保障。它是定位到一条记录...
  • 建表CREATE TABLE (ID INTEGER not null,NAME NVARCHAR2(256),DEL_FLG int,CONSTRAINT "PF_" PRIMARY KEY ("ID"))二.创建序列CREATE SEQUENCE _IDINCREMENT BY 1 --指定序列号之间的间隔,该值可为正的或负的...
  • 主外键约束:主主键是从的外键 数据的增,删,改,查操作(CRUD操作) 注意了兄弟们:别人说数据库的CRUD操作,可千万别再回答说啥CRUD啊,我只会增删改查(CURD就是增删改查) 查询语句:SELECT … FROM… ...
  •  每个了解数据库设计的人都知道,主键对于一张来说是一个很重要,甚至必需的部分。这确实是事实,主键是好的数据库设计的一部分。主键数据库确保数据行在整张唯一性的保障。它是定位到一条记录并且确保不会...
  • 逻辑设计数据库设计三大范式数据库设计第一大范式数据库表中所有的字段都只具有单一属性单一属性的列是由基本数据类型所构成设计出来的都是简单的二维表数据库设计的第二大范式要求中只有一个业务主键,也就是说...
  • 再实际开发中很多数据中都会采用标记删除,而不是物理删除的方式,因为在业务中,一个中的数据的数量会有成百上千万条,为了查询速度需要,在数据中建立索引,而频繁的对数据库中的数据进行插入删除操作,会...
  • 目录1. 主键定义... 52. 主键设计原则... 52.1 确保主键的无意义性... 52.2 采用整型主键... 52.3 减少主键的变动...... 73.3 ID物理主键+UUID逻辑主键... 74. 总结... 81. 主键定义中经常有一个列或多列的...
  • 一、确立主键规范每个了解数据库设计的人都知道,主键对于一张来说是一个很重要,甚至必需的部分。这确实是事实,主键是好的数据库设计的一部分。主键数据库确保数据行在整张唯一性的保障。它是定位到一条记录...
  • 数据库:“主键

    2021-05-12 11:39:26
    数据库主键 本文主要是根据对数据库的相应学习,并对数据库中的一个重要的知识点——主键 进行总结!...主键和外键是把多个组织为一个有效的关系数据库的粘合剂。数据库模式从理论上的逻辑设计转换为实际
  • 需要按照要求给创建主键、创建索引,创建外键(并设置删除更新规则) 一、创建主键 ​ 自己创建一个数据库和最少两个(student course ),然后上图:点击创建主键 [外链图片转存失败,源站可能有防盗...
  • 一起学习在一个数据库设计里,假如使用了逻辑主键,那么你一般都需要一个ID生成器去生成逻辑主键。在许多数据库里面,都提供了ID生成的机制,如Oracle中的sequence,MSSQL中的identity,可惜这些方法各种数据库都...
  • 逻辑表中数据拆分到多个数据库分片上的算法。Hash:数据分布比较均匀。Range:适合范围类操作较多的场景。算法元数据:该参数仅当“拆分算法”为“Range”时有效。用于定义数据库分片对应的数据拆分规则,格式如下...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 151,827
精华内容 60,730
关键字:

数据库表逻辑主键和业务主键