SQL SERVER数据库表主键设计（一）

SQL SERVER数据库表主键设计（一）

　1. 序言

　　当前，随着信息量的急剧增加，对于数据的存储和管理方式，各企业都逐渐摆脱了之前的依靠文件系统（文本文件或者Excel）或者一些桌面型的小型数据库系统（如Access、FoxBASE或者DBase）的状态，转而通过一些大型数据库来管理企业的信息。这些大型数据库系统包括Oracle、MS SQL Server或者IBM DB2。尽管目前数据库系统也在向面向对象的数据库系统方向发展，但是上述的传统的关系型数据库系统依然占据着主要位置。

　　笔者从九十年代末开始以关系型数据库系统为基础为客户进行管理软件的定制化开发。主要是以PowerBuilder为前台开发工具，开发出一些列的C/S结构的软件。进入到本世纪，尤其是最近两年，笔者又以Visual Studio 2003为工具，开发了一些B/S结构的应用。但是，无论是使用何种开发工具，还是开发何种结构的软件，其后台数据库系统依旧是关系型数据库系统。根据客户的应用环境，主要是在MS SQL Server数据库上进行开发，当然也有基于Oracle数据库的软件开发。

　　也正是因为数据量信息量的增加，采用大型的关系型数据库系统作为企业的数据存储管理方式，也就要求基于数据库开发的开发者在数据库设计时必须遵循相应的规范。关于数据库逻辑设计，最重要的就是数据库表的设计，都有一套相应的理论支持，比如要满足相应的范式要求。一般而言，数据库表设计满足第二或者第三范式即可。

　　在开发过程中，也尽量遵循这些相应的规则，但由于之前的经验所限或者是在详细设计时做的工作不够充分，导致一些表结构不是很合理。正是这些不合理，目前出现一些问题，并且已经在部分系统中有所体现。

　　关于数据库的逻辑设计，是一个很广泛的问题。本文主要针对笔者开发应用中的现状，论述在MS SQL Server上进行表设计时，对表的主键设计应注意的问题以及相应的解决办法。

　　2.主键设计现状和问题

　　2.1 现状

　　关于数据库表的主键设计，一般而言，是根据业务需求情况，以业务逻辑为基础，形成主键。

　　比如，销售时要记录销售情况，一般需要两个表，一个是销售单的概要描述，记录诸如销售单号、总金额一类的情况，另外一个表记录每种商品的数量和金额。对于第一个表（主表），通常我们以单据号为主键；对于商品销售的明细表（从表），我们就需要将主表的单据号也放入到商品的明细表中，使其关联起来形成主从关系。同时该单据号与商品的编码一起，形成明细表的联合主键。这只是一般情况，我们稍微将这个问题延伸一下：假如在明细中，我们每种商品又可能以不同的价格方式销售。有部分按折扣价格销售，有部分按正常价格销售。要记录这些情况，那么我们就需要第三个表。而这第三个表的主键就需要第一个表的单据号以及第二个表的商品号再加上自身需要的信息一起构成联合主键；又或者其他情况，在第一个主表中，本身就是以联合方式构成联合主键，那么也需要在从表中将主表的多个字段添加进来联合在一起形成自己的主键。

　　笔者在以前的项目中，也基本上是采取这样的表设计思路来设计系统的表结构和主键。

　　2.2 存在的问题

　　在上面小节中，我们描述了当前在表的主键设计时的现状。从中我们不难看出存在这样的问题：

• 数据冗余存储：随着这种主从关系的延伸，数据库中需要重复存储的数据将变得越来越庞大。或者当主表本身就是联合主键时，就必须在从表中将所有的字段重新存储一次。
• SQL复杂度增加：当存在多个字段的联合主键时，我们需要将主表的多个字段与子表的多个字段关联以获取满足某些条件的所有详细情况记录。
• 程序复杂度增加：可能需要传递多个参数。
• 效率降低：数据库系统需要判断更多的条件，SQL语句长度增加。同时，联合主键自动生成联合索引
• WEB分页困难：由于是联合主键方式（对于多数的子表），那么在WEB页面上要进行分页处理时，在自关联时，难于处理。

　　3. 解决方案
　
　　3.1 概述

　　从上面，我们已经看到现有结构存在着相当多的弊端，主要是导致程序复杂、效率降低并且不利于分页。

　　为解决上述问题，本文提出：当应用系统后台数据库表间存在主从关系时，数据库表额外增加一非业务字段作为主键，该字段为数值型；或者当该表需要在应用中进行分页查询时，也应考虑如此设计。一般地，我们也可以几乎为任何表增加一个与业务逻辑无关的字段作为该表的主键字段。

　　3.2 字段定义方式

　　由于该字段要作为表的主键，那么其首要条件是要保证在该表中要具有唯一性。同时，结合SQL Server数据库自身的特性，可以为其建立一个自增列：

　　create TABLE T_PK_DEMO

　　(

        U_ID           BIGINT          NOT NULL IDENTITY(1,1),--唯一标识记录的ID

        COL_OTHER  VARchar(20)     NOT NULL              ,--其他列

        CONSTRAINT PK_T_PK_DEMO PRIMARY KEY  NONCLUSTERED (U_ID)--定义为主键

　　)

　　但是，SQL Server中的自增列却存在一个比较尴尬的事实，那就是该字段一旦定义和使用，用户无法直接干预该字段的值，完全由数据库系统自身控制：

• 完全数据库系统控制，用户无法修改值
• 在数据库的发布和订阅时，使用自增列会比较麻烦
• 恢复部分数据时，使用自增列会比较麻烦
• 该列的值必须在插入数据后才能获取

　　鉴于此，建议不以自增列的方式来定义，而是参考Oracle数据库系统中序列，在SQL Server系统中实现类似Oracle数据库系统序列功能。这个具体在下面的小节中介绍。我们只需要按照普通字段的定义方式修改表定义为：

　　create TABLE T_PK_DEMO

　　(

        U_ID           BIGINT         NOT NULL ,--唯一标识记录的ID

        COL_OTHER  VARchar(20)     NOT NULL ,--其他列

        CONSTRAINT PK_T_PK_DEMO PRIMARY KEY  NONCLUSTERED (U_ID)--定义为主键

　　)

　　3.3    序列的实现

　　参照Oracle序列的功能，我们需要在SQL Server数据库中创建一个新表，以管理序列值：

　　create TABLE T_DB_SEQ

　　(

    SEQ_NAME        VARchar(50)     NOT NULL              ,--序列名称

    SEQ_OWNER       VARchar(50)     NOT NULL DEFAULT ’DBO’,--序列所有者(SYSTEM_USER)

    SEQ_CURRENT     BIGINT          NOT NULL DEFAULT 0    ,--序列当前值

    SEQ_MIN         BIGINT          NOT NULL DEFAULT 0    ,--序列最小值

    SEQ_MAX         BIGINT          NOT NULL DEFAULT 0    ,--序列最小值

    SEQ_MAX         BIGINT          NOT NULL DEFAULT 0    ,--序列最大值

    SEQ_STEP        INT             NOT NULL DEFAULT 1    ,--序列增长步长

    IF_CYCLE        INT             NOT NULL DEFAULT 0    ,--是否循环(0,不循环;1,循环)

    CONSTRAINT T_DB_SEQ PRIMARY KEY  CLUSTERED (SEQ_NAME,SEQ_OWNER)--主键

　　)

　　应用系统为需要创建自增列的表创建一个序列名称，在表“T_DB_SEQ”中反映为数据库中的一行。

　　3.4    使用序列

　　第一，需要为需要建立序列的表创建一个序列。采用方法：F_create_SEQ(序列名)。该函数传入序列的名称，在表“T_DB_SEQ”插入一行。序列的所有者，采用系统变量SYSTEM_USER。

　　第二，获取下一个值。采用方法：F_GET_NEXT_SEQ_VAL(序列名)。该函数根据序列名获取该序列的下一个值，根据当前值与增长步长得到。同时，该函数保证在同时获取同一个序列时，应保证并发一致性。

　　第三、将返回值返回到应用使用。

　　此外，为保证应用的完整性，可能还需要提供一些方法的重载方法，同时提供一些其他方法：

• 获取序列当前值：F_GET_SEQ_CUR_VAL(序列名)
• 设置序列值：F_SET_SEQ_VAL(序列名)
• 删除序列：F_DEL_SEQ(序列名)
• 判断序列是否存在：F_SEQ_exists(序列名)

　　在主从关系的表设计中，子表也使用序列字段作为唯一主键，将父表的序列字段作为外键关联：

　　create TABLE T_PK_DEMO_C

　　(

    U_ID           BIGINT         NOT NULL ,--唯一标识记录的ID

    COL_OTHER  VARchar(20)     NOT NULL ,--其他列

    P_ID       INT             NOT NULL ,--父表ID

    CONSTRAINT PK_T_PK_DEMO_C PRIMARY KEY  NONCLUSTERED (U_ID)--定义为主键

    CONSTRAINT FK_T_PK_DEMO_C FOREIGN KEY (P_ID) REFERENCES T_PK_DEMO(U_ID) ON delete CASCADE,

　　)