快捷搜索:

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,

)

4.应用序列的问题及办理法子

4.1 若何包管营业逻辑约束

因为系统应用一个额外增添一个字段作为主键,是以没有为营业逻辑建立主键约束。比如在企业用户信息表中,要求企业顶用户登录名必须独一。一样平常在创建表时,以登录名作为主键,这个时刻在数据库层自然的创建另一个主键独一性约束。而现在没有应用登录名作为主键,那么就没有这个约束。办理法子:

一是在数据库层办理。可以为该表创建一个独一(UNIQUE)约束或者独一索引。如:

alter TABLE T_PK_DEMO ADD CONSTRAINT C_T_PK_DEMO UNIQUE NONCLUSTERED(COL_OTHER)-独一约束

create UNIQUE INDEX IX_T_PK_DEMO ON T_PK_DEMO(COL_OTHER) – 独一索引

二是在利用端办理。也便是在利用中判断该列是否有重复值,然后根据判断结果来包管独一性。

4.2为何主键采纳非聚蔟要领

我们留意到,在之前的例子中,主键采纳了NONCLUSTERED(非聚蔟)的索引要领。关于若何设计索引,不是本文的重点,在这里仅供给一个建立索引时采纳聚蔟要领照样非聚蔟要领的一个一样平常原则:

动作描述

应用凑集索引

应用非凑集索引

列常常被分组排序

返回某范围内的数据

×

一个或极少不合值

×

×

小数目的不合值

×

大年夜数目的不合值

×

频繁更新的列

×

外键列

主键列

频繁改动索引列

×

作为非营业字段的主键列,是一个没有重复值的、基础不进行更新操作的列。并且,在SQL Server数据库中,聚蔟索引在一个表中只能有一个。是以,聚蔟索引异常紧张,必要留给更紧张的字段来应用。是以,对比上表和根据聚蔟索引的紧张程度,在此处采纳非聚蔟要领创建其索引。

5. 详细利用

采纳这种主键设计要领,有诸多好处,这已经在前文阐明。现在就以一个详细的利用来阐明若何应用这个主键。

当前的利用系统基础上都已经采纳B/S要领,只管现在的收集速率已经有大年夜幅度的前进,然则因为在WEB利用上用户数量浩繁、同时基础上所有的运算都集中在WEB利用办事器上,以是在WEB设计上更要斟酌到机能的优化,以削减收集流量和对办事器的压力。最常见的一个利用便是列表要领展现时的分页要领。一样平常的,在数据量小的环境下,一样平常不会怎么留意这个问题,平日采纳将数据完全掏出,然后在WEB办事器长进行分页。然则,当数据量宏大年夜时,这种要领就会导致速率低落,以致根本弗成用。以是,一样平常采纳存储历程,在数据库端进行分页。下面就供给一个通用的分页存储历程SP_COMMON_PAGER:

您可能还会对下面的文章感兴趣: