精华内容
下载资源
问答
  • 数据库主键设计

    2019-02-20 10:02:35
    数据库主键

    数据库主键设计

    数据库模型设计——主键的设计
    在数据库设计时,主要就是对实体和关系的设计,实体表现出来就是表,关系表现出来就是外键。而对于一个表,由两部分组成:主键和属性。主键的简单定义就是表中为每一行数据的唯一标识。其实更准确的说法,每一行数据的唯一标识是候选键(Candidate Key),一个表中可以有很多个候选键,主键是候选键中的一个,主要用于更方便的检索和管理数据。一个表中可以有多个候选键,但是只有一个主键。由于主键常常用于检索数据,也用于表之间的关联,所以主键的设计的好坏将会严重影响数据操作的性能。下面来介绍下主键设计的几个考虑因素。

    主键的数据类型
    最常见的主键数据类型是数字类型、固定长度的字符类型和GUID类型。通常情况下,RDBMS会在主键上建立聚集索引(SQL Server默认都这么做),由于我们使用B-Tree的数据结构来存储索引数据,所以一般对主键有以下两个要求:

    越短越好——越短在一个Page中存储的节点越多,检索速度就越快。
    顺序增长——如果每一条插入的数据的主键都比前面的主键大,那么B-Tree上的节点也是顺序增长的,不会造成频繁的B-Tree分割。
    越短越好是为了查询的速度快,顺序增长是为了插入速度快。

    有了这两个要求,我们再来分析下各个数据类型:

    数字类型:根据数据量决定是用Int16还是Int32或者Int64,能用Int32的就不需要使用Int64。
    字符类型:基本不满足前面提到的2点要求,字符类型一般不会很短,而且也很可能不是顺序增长的,所以不是特别推荐的主键类型。当然如果确实业务需求使用字符类型,那么也尽量使用char(XX)而不要使用varchar(XX),因为在RDBMS中,对于定长字符串和变成字符串的数据结构和处理是不一样的,varchar的性能更差。
    GUID类型:这个类型并不是所有数据库都有对应的数据类型,SQL Server有uniqueidentifier,MySQL没有。GUID类型在SQL Server中是16个字节,不算短,比4个字节的Int32长多了。在插入新数据时,GUID一般都是使用NewId()这样的生成随机GUID的方式生成的,所以也不是顺序增长的,在插入速度上不会很快。
    通过上面的比较,我们知道使用数字类型是更好的方式,那么我们为什么还会有人使用GUID和字符串来当主键呢?那是因为:

    相对于数字类型,字符类型更易读易记,在检索关联的数据时,更方便直接。

    GUID的优势是全球唯一,也就是说同样的系统,如果部署了多套环境,那么里面的数据的主键仍然是唯一的,这样有助于数据的集成。典型的例子就是一个系统在全国每个省份都部署一套,每个省份的数据各种录入,互不干扰,然后再把每个省的数据集成起来为总部做分析。

    数据库主键与业务主键
    前面说到一个表可能有很多个唯一标识的候选键,那么这么多候选键中,哪个应该拿来做主键呢?一种方案是再新建一个独立的字段作为主键,该字段并没有业务含义,只是一个自增列或者流水号,用于唯一标识每一行数据,这是数据库主键。另外一种方案是选择其中较短较常用的属性作为主键,这是业务主键。个人建议是不要使用任何有业务含义的字段作主键,而是使用一个自增的(或者系统生成的)没有实际业务意义的字段作为主键。为什么呢?主要是出于以下考虑:

    具有业务意义的字段很可能是用户从系统录入的,不要信任用户的任何输入,只要是用户自己录入的,那么就很有可能录错了,如果发现录入错误,这个时候再对主键进行修改,将会涉及到大量关联的外键表的修改,是很麻烦的一件事情。比如在做人员表的时候,就不要使用员工号或者身份证号做主键。

    具有业务意义的字段虽然在当前阶段是唯一的,是不变的,但是并不能保证随着公司政策变动、业务调整等原因,导致该业务字段需要修改,以满足新的业务要求,这个时候要修改主键也是很麻烦的事情。比如部门表,我们以部门Code作为主键,但是后来部门变动,Code修改,则系统部门表的主键也得更改。

    还有一个原因是业务主键在数据录入的时候不一定是明确知道的,有时我们会在不知道业务主键的情况下,就录入其他相关信息,这个时候,如果使用业务主键做数据库的主键,那么数据将无法录入。比如员工表把员工号作为主键,那么员工还没有入职,没有员工号的时候,HR需要先维护一些该预入职员工的信息是不可能的。

    联合主键
    联合主键就是以多个字段来唯一标识每一行数据。前面已经说到主键应该越短越好,而且是建议是一个没有意义的自增列,那么是不是就不会再需要联合主键呢?答案是否定的,我们仍然可能会使用到联合主键。联合主键主要使用在多对多的关系时,中间表就需要使用联合主键。在简单的多对多关系中,我们不需要为中间的关联建立实体,所以中间表可能就只需要两列,分别是两个实体表的主键。

    主键值的生成
    主键值的生成可以参考NHibernate的配置,概况下来主要有这么几种生成方式:

    自增,这是SQL Server常用的主键生成方式,完全由数据库管理主键的值。
    Sequence对象,这是Oracle常用的主键生成方式,现在SQL Server已支持。主要是在数据库中有一个Sequence对象,通过该对象生成主键。
    GUID,这是用于GUID类型的主键,可以使用newid()这种数据库提供的函数,或者使用程序生成Guid并赋值。
    Hilo值,这是一种使用高低位算法生成的数字值的主键。该值由NHibernate程序内部生成。
    其他程序赋值,完全由程序根据自己的算法生成并赋值。
    更详细的主键生成,我们可以参见:http://www.cnblogs.com/chenkai/archive/2009/04/13/1434912.html

    主键与索引
    在概念和作用上,主键与索引是完全两个不同的东西,但是由于我们大部分情况下都是使用主键检索数据,所以大部分数据库的默认实现,在建立主键时会自动建立对应的索引。

    以SQL Server为例,默认情况下,建立主键的列,就会建立聚集索引,但是实际上,我们可以在建立主键时不使用聚集索引。另外还有一个唯一约束(索引)的概念,该索引中的数据必须是唯一不能重复的,感觉和主键的意义一样,但是还是有一点点区别。

    主键是只能由一个,而唯一约束(索引)在一个表中可以有多个。

    主键不能为空,而唯一约束(索引)是可以为空的。

    【本文章出自博客园深蓝居,转载请注明作者出处,如果您觉得博主的文章对您有很大帮助,欢迎支付宝(studyzy@163.com)对博主进行打赏。】
    分类: Database Design

    展开全文
  • MySql数据库主键外键与数据库设计

    千次阅读 2019-05-20 01:38:57
    MySql数据库主键外键与数据库设计

    MySql数据库主键外键与数据库设计

    首先要指出的:
    列、字段、属性是一个概念
    行、记录、元组是一个概念

    MySQL数据库CONSTRAINT约束:非空约束,唯一约束,主键约束,外键约束
    show create table table_name\G;可以查看已建表的相关信息,包括主外键,外键名,存储引擎,编码方式等等

    主键约束

    主键:表中经常有一个列或者多列的组合,其值能唯一的标识表中的每一行,这样的一列或多列称为表的主键,通过它可以强制表的实体完整性,当创建或更改标识可以通过定义primary key约束来创建主键,一个表只能有一个primary key约束,而且primary key约束的列不能接受空值,由于primary key约束确保该列数据的唯一性,所以经常用来定义标识列。主键自带索引,并且一个表只能有一个主键约束
        实体完整性这项规则要求每个数据表都必须有主键,而作为主键的所有字段,其属性必须是独一及非空值。

    主键的作用
    用作标识列,区分其他字段
    强制表的实体完整性
    用于其他表的外键关联
    用于记录的修改与删除
    加速查询

    • 创建主键约束
    1. 建表时创建主键
      create table Student(id int primary key,name varchar(32) default null)
    2. 建表后添加主键
      create table Student(id int,name varchar(32));
      alter table Student add primary key(id);
    3. 建表后新加字段并设为主键
      create table Mother(id int,name varchar(32));
      alter tabler Mother add age int primary key;

    删除主键约束
    alter table Student drop primary key;

    联合主键:用两个或两个以上的字段组成的主键,用这个主键包含的字段作为主键约束,这个组合在数据表中是唯一,且加了主键索引。

    创建表时创建联合主键
    create table STUDENT(id int auto_increment,courser int,name varchar(10),primary key(id,courser));

    已有的表添加联合主键
    create table Person(id int,province varchar(8),city varchar(8),country varchar(8));
    alter table Person add constraint main_key primary key(id,province);
    Person是表名,main_key 是联合主键名。在建好的表中我们添加了如下数据:
    在这里插入图片描述

    由于id、province是联合主键,所以我们想要创立
    在这里插入图片描述
    时,会出现如下报错
    在这里插入图片描述
    但是我们可以让一个联合主键的一个字段名一样,另一个字段名不一样来创建,即联合主键的字段名不完全一致:
    在这里插入图片描述

    删除联合主键的主键约束,由于主键约束在表单中的唯一性,我们可以直接删除:
    alter table Person drop primary key;

    外键约束

    为了满足关系型数据库的第三范式,降低数据冗余,在表与表之间需要满足响应的关系,可以为表和表之间设置外键。
    外键约束:用于两个表的数据的数据连接,一个表可以有多个外键,MySQL中的innodb存储引擎支持外键;
    外键对应的是参照完整性,一个表的外键可以是空值,若不为空值,则每一个外键的值必须等于另一个表中的主键的某个值。定义一个外键后,不允许删除另一个表中具有关联关系的行;外键的主要作用是保持数据的一致性、完整性。一个主键表某一字段的和跟他有关联的外键表的某一字段有直接的关联。
    对于有外键关联的两个表来说:
    主表(父表):主键所在的表
    从表(字表):外键所在的表

    mysql设置外键约束字段选取
    父表必须是已经存在或者正在创建的表
    父表主键不能包含空值,但外键可以有
    外键列的数目必须和父表的主键中列的数目相同
    从表外键列和主表主键列对应的列的数据类型相同

    创建表时设置外键约束
    create table friend(id int ,name varchar(20),foreign key(id) references child(id));
    父表child,子表friend

    在已有的表中设置外键约束
    alter table subject add foreign key(id) references child(id);
    注意:子表的外键必须关联父表的主键,且关联字段的数据类型必须匹配,如果类型不一样,则创建子表时会出现错误。

    删除外键约束
    有外键约束的列必须先删除外键,然后才能删除该列:
    alter table subject drop foreign key subject_ibfk_1;
    subject_ibfk_1是外键的名字,我们可以自己设,也可以系统生成,这样的话我们要查看建表信息才能知道外键名字是什么。

    查看表中的外键
    show create table table_name
    select * from information_schema.key_column_usage;

    数据库设计

    数据库范式与设计:一对一,一对多,多对多
    关系型数据库的特点:基本组成但对为二维表,各二维表之间存在一定的关系,数据库范式级别越高,数据冗余越低
    第一范式:字段不可再分,只要是个二维表都符合第一范式
    第二范式:每个表都有一个能区分每条记录的主键(非空 唯一 索引)
    第三范式:表中所用数据元素不但能惟一的被主关键字所标识,而且它们之间还必须相互独立,不存在其他的函数关系,只有通过外键来互相联系

    表与表之间的三种关系:
    一对一:如学生分数表与学生信息表,两表有一样的主键ID
    一对多:如教学楼和教室,班级表和学生表的班级ID,多个从表通过外键连接主表主键完成一对多的关系
    多对多:如学生表和课程表,演员和电影;以演员和电影为例,分别建立演员表和课程表,演员表电影表不重复,然后建立一个关系表,将两个表连接起来,变成连个一对多的关系:
    在这里插入图片描述

    建立演员表
    create table actors(id int auto_increment primary key,name varchar(10));
    建立电影表
    create table films(id int auto_increment primary key,name varchar(20));
    建立中间表,并通过外键连接其他相关表
    create table link(id int auto_increment primary key,id_a int,id_f int);
    alter table link add foreign key(id_a) references films(id);
    alter table link add foreign key(id_f) references actors(id);

    这里我们想查询成龙演过的电影:
    select name,f_name from(select * from actors inner join(select id_a,id_f,name as f_name from link inner join films on films.id=link.id_f) as a on a.id_a=id having id=1) as b;

    展开全文
  • 浅谈数据库主键策略

    2016-12-04 20:34:02
    浅谈数据库主键策略 数据库表的主键很多童鞋都非常熟悉了,主键就是Primary Key,简称PK。 数据库主键的作用是唯一标识一条记录,所以在同一张表中,任意一条记录的主键都是唯一的,不然,数据库系统就无法...

    浅谈数据库主键策略

    数据库表的主键很多童鞋都非常熟悉了,主键就是Primary Key,简称PK。

    数据库主键的作用是唯一标识一条记录,所以在同一张表中,任意一条记录的主键都是唯一的,不然,数据库系统就无法根据主键直接定位记录。

    虽然数据库系统本身对主键没有特别的要求,但是,写程序的时候,要考虑清楚使用什么类型的主键。正确地使用主键是存储数据成功的一半,错误地使用主键会让一个应用逐渐走向崩溃。

    主键不可修改

    对于数据库来说,主键其实是可以修改的,只要不和其他主键冲突就可以。但是,对于应用来说,如果一条记录要修改主键,那就会出大问题。

    因为主键的第二个作用是让其他表的外键引用自己,从而实现关系结构。一旦某个表的主键发生了变化,就会导致所有引用了该表的数据必须全部修改外键。很多Web应用的数据库并不是强约束(仅仅引用主键但并没有设置外键约束),修改主键会导致数据完整性直接被破坏。

    业务字段不可用于主键

    所有涉及到业务的字段,无论它看上去是否唯一,都决不能用作主键。例如,用户表的Email字段是唯一的,但是,如果用它作主键,就会导致其他表到处引用Email字段,从而泄露用户信息。

    此外,修改Email实际上是一个业务操作,这个操作就直接违反了上一条原则。

    那么,主键应该使用哪个字段呢?

    主键必须使用单独的,完全没有业务含义的字段,也就是主键本身除了唯一标识和不可修改这两个责任外,主键没有任何业务含义。

    类似的,看上去唯一的用户名、身份证号等,也不能用作主键。对这些唯一字段,应该加上unique索引约束。

    主键应该用什么类型

    主键应该使用整数还是字符串?(用浮点数的请自觉充值智商)

    我强烈建议使用字符串。

    为什么?

    我们先看使用整数的问题。

    使用整数有两个选择:数据库自增和自己生成。

    自己生成其实也是自增,无非就是把上次使用的值保存到某个地方,下次使用的时候继续自增。常见的做法是用一个单独的表存储上次用的最大值。这种方式实现复杂,可靠性低,还不如数据库自增。

    数据库自增最大的问题还不在于数据库单点造成无法水平切分,因为绝大部分公司还撑不到业务需要分库的情况就倒闭了。

    自增主键最大的问题是把公司业务的关键运营数据完全暴露给了竞争对手和VC。举个例子,用户表采用自增主键,只需要每周一早上去注册一个用户,把上周注册的ID和本周注册的ID一比,立刻就知道了该公司一周的新增用户数量。如果网站声称新增了10万用户,但ID却只增加了1千,就只能呵呵了。

    因为主键的本质是保证唯一记录,并不要求主键是连续的。实际上不连续的更好,这样既避免了运营数据泄露,也给黑客预测ID制造了障碍,具有更高的安全性。

    用字符串主键就不存在这个问题。如果我们用一个UUID作为主键,即varchar(32),除了占用的存储空间较多外,字符串主键具有不可预测性。

    有人觉得UUID完全随机,主键本身没有按时间递增,不利于直接主键排序。其实解决这个问题很简单。

    方法一,直接用时间戳+UUID构造一个主键,时间戳注意补0,这样生成的主键就是按时间排序的。这个方法简单粗暴,缺点是主键更长了。

    方法二,自定义一个算法,时间戳放高位,序列号放低位,还可以保留机器位,然后用base32编码,可以把长度控制在20个字符内。

    有人会问,根据方法二,构造包含时间戳和序列号的64位整数作为主键是否可行?

    理论上来说是可行的,因为时间戳0xffffffff可以表示到2100年。但是剩下的位不是ffffffff而是只有fffff,如果给机器分配ff作为标识,那么每秒只能最多生成0xfff+1=4096个主键,对一些大型应用不太够用。

    为啥64位整数除掉时间戳只能用后面的fffff位呢?这是因为JavaScript的Number类型是56位精度,它能表示的最大整数是0x1fffffffffffff,而我们迟早会用REST跟JavaScript打交道,所以要把64位整数的范围限制在0x1fffffffffffff内,否则与JavaScript交互就会出错。

    虽然理论上64位整数做时间戳+序列号的主键是没问题的,但是实践中是没法绕开与JavaScript交互的,综合考虑,字符串主键最可靠。

    展开全文
  • 关于数据库主键和外键(终于弄懂啦)

    万次阅读 多人点赞 2019-06-18 15:59:59
    转 关于数据库主键和外键(终于弄懂啦) 2018年07月16日 19:44:24 f45056231p 阅读数 19385 ...

    关于数据库主键和外键(终于弄懂啦)

    一、什么是主键、外键:

    关系型数据库中的一条记录中有若干个属性,若其中某一个属性组(注意是组)能唯一标识一条记录,该属性组就可以成为一个主键 
    比如  
    学生表(学号,姓名,性别,班级) 
    其中每个学生的学号是唯一的,学号就是一个主键 
    课程表(课程编号,课程名,学分) 
    其中课程编号是唯一的,课程编号就是一个主键 
    成绩表(学号,课程号,成绩) 
    成绩表中单一一个属性无法唯一标识一条记录,学号和课程号的组合才可以唯一标识一条记录,所以 学号和课程号的属性组是一个主键 
      
    成绩表中的学号不是成绩表的主键,但它和学生表中的学号相对应,并且学生表中的学号是学生表的主键,则称成绩表中的学号是学生表的外键 
      
    同理 成绩表中的课程号是课程表的外键 
      
    定义主键和外键主要是为了维护关系数据库的完整性,总结一下:
    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; 

    展开全文
  • 数据库主键和外键

    万次阅读 2018-05-17 18:19:28
    刚开始学数据库的时候,刚接触到的一个较为陌生的概念就是主键和外键。啥叫主键,从字面意思上来看感觉一脸懵。简单点说主键就类似每个人的身份证,代表一个唯一的某个人,而且主键不能为空,估计你也没见过哪个人...
  • 数据库主键与外键 我们知道,好的数据结构会影响速度,当然,好的数据库表设计会影响数据库操作效率。 特别是数据特别多的时候如果表的结构不好的话,操作将会变得特别复杂。其中,主键和外键值得我们去了解。 在...
  • 数据库主键外键区别

    2017-03-16 15:56:12
    数据库是主数据库的备份,当主数据库变化时从数据库要更新,这些数据库软件可以设计更新周期。这是提高信息安全的手段。...2,一对多,引用主外键,‘一’相当于主键,‘多’即是引用主键的外键。 例如:一
  • 数据库主键外键设计原则

    千次阅读 2012-05-27 10:49:32
    主键和外键是把多个表组织为一个有效的关系数据库的粘合剂。主键和外键的设计对物理数据库的性能和可用性都有着决定性的影响。 必须将数据库模式从理论上的逻辑设计转换为实际的物理设计。而主键和外键的结构是这个...
  • 关系数据库主键和外键

    千次阅读 2013-08-03 15:31:20
    关系数据库中的一条记录中有若干个属性,若其中某一个属性组(注意是组)能唯一标识一条记录,该属性组就可以成为一个主键  比如  学生表(学号,姓名,性别,班级)  其中每个学生的学号是唯一的,学号就是一个...
  • 数据库主键和外键的区别

    千次阅读 2017-09-14 11:47:53
    关系数据库中的一条记录中有若干个属性,若其中某一个属性组(注意是组)能唯一标识一条记录,该属性组就可以成为一个主键  比如  学生表(学号,姓名,性别,班级)  其中每个学生的学号是唯一的,学号就是一个...
  • 数据库 主键 外键 唯一键区别

    千次阅读 2018-11-13 19:57:15
    下面我通过一个小栗子来说明我们应该如何选择主键,外键和唯一键。 现在我们想建立学生表,用来存储,一个系统的登陆信息。建表如下: create table student4 ( stu_id int primary key identity,--identity表示...
  • 数据库主键、外键和唯一键的区别

    千次阅读 2019-10-24 17:37:29
    数据库主键、外键和唯一键的区别 现在我们想建立学生表,用来存储,一个系统的登陆信息。建表如下: create table student4 ( stu_id int primary key identity,--identity表示自增 stu_name nvarchar(10) unique ...
  • 关于数据库主键和外键 终于弄懂啦

    千次阅读 2017-02-26 16:50:34
    一、什么是主键、外键 二、主键、外键和索引的区别 三、数据库中主键和外键的设计原则 四、数据库主键选取策略 1.自动增长型字段 2.手动增长型字段 3.使用UniqueIdentifier 4.使用”COMB(Combine)”类型
  • 主键策略数据库主键介绍不用多说了,接触数据库的童鞋都很清楚。在业务开发时那什么做主键?数据库主键的作用是唯一标识一条记录,所以在同一张表中,任意一条记录的主键都是唯一的,不然,数据库系统就无法根据主键...
  • 数据库主键选择策略

    千次阅读 2011-11-30 15:21:17
    我们在建立数据库的时候,需要为每张表指定一个主键,所谓主键就是能够唯一标识表中某一行的属性或属性组,一个表只能有一个主键,但可以有多个候选索引。因为主键可以唯一标识某一行记录,所以可以确保执行数据更新...
  • 数据库主键生成策略

    千次阅读 2013-06-11 21:17:31
    可以生成long、int、short类型的主键,由hibernate在内存中生成主键,每次增量为1,不依赖于底层数据库,但是因为是由hibernate生成的,所以只能有一个hibernate进程访问数据库,否则就会产生主键冲突,所以不能在...
  • 浅谈数据库主键和外键及索引

    万次阅读 2017-04-08 22:36:19
    定义主键主要是为了维护关系数据库的完整性。 2、外键: 外键用于与另一张表的关联,是能确定另一张表记录的字段。外键是另一个表的主键,可以重复,可以有多个,也可以是空值。定义外键主要是为了保持数据的一致...
  • 关系数据库主键外键

    千次阅读 2021-01-11 21:55:07
    多对多:必须通过单独的一张表来实现(一个老师多个学生,一个学生多个老师)班级一张表,老师一张表,班级和老师的关系一张表 主键: 定义:能够唯一标示一个事物的一个字段或者多个字段的组合 含有主键的叫做主键表...
  • 在我过去的工作经验中,参与数据库的设计不少,刚好最近遇到了一个问题,这个问题就是数据库主键设计引起的。【问题描述】相信系统使用Oracle数据库,主键采用Oracle里面的sequence生成id,基本上每个table都有一个 ...
  • 关于数据库主键和外键,索引

    千次阅读 2018-06-28 18:18:29
    关系数据库中的一条记录中有若干个属性,若其中某一个属性组(注意是组)能唯一标识一条记录,该属性组就可以成为一个主键 比如 学生表(学号,姓名,性别,班级) 其中每个学生的学号是唯一的,学号就是一个主键 ...
  • MySQL 数据库主键的策略

    千次阅读 2018-04-19 13:17:21
    1.是否要采用GUID作为主键用GUID作主键有它的优势与不足.优势是GUID具有唯一性,在任何情况下,可以产生全球唯一的值.这是GUID最大的优势,也...而且数据太长,影响数据库效率.GUID的产生不是以一定的次序产生,对于按...
  • 建立一个数据库,其中的某些实体是否可以不设置主键,只设置外键完成二者的关系建立
  • 数据库主键、唯一键、范式

    千次阅读 2015-07-29 11:36:14
    主键: 能够唯一表示数据表中的每个记录的【字段】或者【字段】的组合就称为主码(主键)。一个主键是唯一识别一个表的每一记录,但这只是其作用的一部分,主键的主要作用是将记录和存放在其他表中的数据进行关联。在...
  • [MySQL]数据库主键设计之思考

    千次阅读 2016-10-21 11:02:30
    数据库主键设计之思考一1.是否要采用GUID作为主键用GUID作主键有它的优势与不足.优势是GUID具有唯一性,在任何情况下,可以产生全球唯一的值.这是GUID 最大的优势,也方便数据导入,比如要求从另一个系统中把数据导入...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 197,298
精华内容 78,919
关键字:

关系数据库的主键