sqlserver主键设计的注意点
编程学习 2021-07-05 13:42www.dzhlxh.cn编程入门
在数据库设计中，主键用于惟一地标识表中的某一条记录
在设计主键的时候往往需要考虑以下几点：
1.无意义性：此处无意义是从用户的角度来定义的。这种无意义在一定程度上也会减少数据库的信息冗余。常常有人称呼主键为内部标识，为什么会这样称呼，原因之一在于“内部”，所谓内部从某种程度上来说就是指表记录，从大的范围来说就是数据库，如果你在设计的时候选择了对用户来说有意义的信息来作为主键，那么迟早会面对用户提出对这块信息进行更新的需求，那么你就违背了它应有的静态。
2.静态性：主键除了唯一地标识一条记录及外键的关联外，应不再考虑其他的意义，最理想的状态就是在产生后不再变动，所以在主键值产生后应考虑不对他进行更新等操作。如果进行了更新操作那么至少说明这块信息对于用户来说是有一定的意义，那么你就违背了应有的无意义性。（对数据进行整合等操作时可能需要对主键进行处理，这样做是为了保证数据库的完整性——记录的唯一，不在此考虑范围之内。）
无意义性往往可以决定其静态性。
3.简短性：既包含主键组成字段数量要少，还包含主键中单个字段存储类型简短，一般采用整形；对于前者主要考虑的是外键关联的因素；对于后者主要考虑的是性能。主键的简短对表的关联便捷性及检索的性能有极大的帮助。
看看狼蚁网站SEO优化具有缺陷的“主生产计划表”主键设计方案(MsSQL)：
--主表
CREATE TABLE PP_MPSHeader(
BillNo VARCHAR(20) NOT NULL PRIMARY KEY,
PlanDate DATETIME NOT NULL
)
--从表
CREATE TABLE PP_MPSBody(
BillNo VARCHAR(20) NOT NULL,
LineNumber SMALLINT NOT NULL,
ProductID INT NOT NULL,
ProductQty DECIMAL(18,2) NOT NULL,
PRIMARY KEY(BillNo,LineNumber)
)
--设置外键
ALTER TABLE PP_MPSBody
ADD CONSTRAINT FK_PP_MPSHeader_MPSBody FOREIGN KEY(BillNo) REFERENCES PP_MPSHeader(BillNo)
这是典型的主从表结构。主表记录什么时候下达哪个单号的主计划，从表记录的是此计划生产哪些产品各多少数量，通过BillNo进行关联。当用户在下达一份主生产计划后，很可能会发现由于粗心大意输错了BillNo中计划单号信息，那么在他修改单号时，代码编写者需要在代码中控制从表的单号跟随主表的单号进行变动，否则单据将在外键的约束下无法保存，如果没有外键的约束，那么数据将失去其完整性。
如果按照上面的3个注意点，解决方案如下(MsSQL)：
--主表
CREATE TABLE PP_MPSHeader(
BillId INT PRIMARY KEY,
BillNo VARCHAR(20) NOT NULL,
PlanDate DATETIME NOT NULL
)
--从表
CREATE TABLE PP_MPSBody(
BillId INT PRIMARY KEY,
LineNumber SMALLINT NOT NULL,
ProductID INT NOT NULL,
ProductQty DECIMAL(18,2) NOT NULL,
PRIMARY KEY(BillId,LineNumber)
)
--设置外键
ALTER TABLE PP_MPSBody
ADD CONSTRAINT FK_PP_MPSHeader_MPSBody FOREIGN KEY(BillId) REFERENCES PP_MPSHeader(BillId)
现在，主从表通过BillId进行关联，当产生一份生产计划时，生成一个BillId，对于用户来说根本没有意义，在随后单据信息的改动中也不会出现上面的主从信息协调问题。同时从表的信息量小于上面的缺陷设计。因为原外键BillNo的长度从20个字节变成了现在的BillId4个字节，减少了信息的冗余。
这样的例子其实很多，比如：
有的设计原材料表时，使用零部件图号作为主键，那就意味着采购、生产、销售等等相关表中都会出现零部件图号的外键信息，当零部件图号信息发生变动时，这些所有先关的信息都需要跟着变动，这种缺陷如果不从根本上解决，那么你可能需要写个零部件图号变动处理过程，来批量处理这些问题，在处理的过程中可能你还得考虑处理的顺序问题……；
有的设计，使用身份证件号作为人员表的主键，但是身份证后来从15位变成了18位，这就意味着人员表中每个人的人员身份证信息都需要变动，如果你是某个社保机构此应用程序的设计人员，那么你就需要更新上百万条记录；那些所有由人员表通过身份证件号外联出去的信息记录将会以亿计数，那么也许余生你就不需要做其他工作了。
所以选择无意义的键值来作为主键的一部分，也是从长远意义上来避免类似这种改动的发生。
1.无意义性：此处无意义是从用户的角度来定义的。这种无意义在一定程度上也会减少数据库的信息冗余。常常有人称呼主键为内部标识，为什么会这样称呼，原因之一在于“内部”，所谓内部从某种程度上来说就是指表记录，从大的范围来说就是数据库，如果你在设计的时候选择了对用户来说有意义的信息来作为主键，那么迟早会面对用户提出对这块信息进行更新的需求，那么你就违背了它应有的静态。
2.静态性：主键除了唯一地标识一条记录及外键的关联外，应不再考虑其他的意义，最理想的状态就是在产生后不再变动，所以在主键值产生后应考虑不对他进行更新等操作。如果进行了更新操作那么至少说明这块信息对于用户来说是有一定的意义，那么你就违背了应有的无意义性。（对数据进行整合等操作时可能需要对主键进行处理，这样做是为了保证数据库的完整性——记录的唯一，不在此考虑范围之内。）
无意义性往往可以决定其静态性。
3.简短性：既包含主键组成字段数量要少，还包含主键中单个字段存储类型简短，一般采用整形；对于前者主要考虑的是外键关联的因素；对于后者主要考虑的是性能。主键的简短对表的关联便捷性及检索的性能有极大的帮助。
看看狼蚁网站SEO优化具有缺陷的“主生产计划表”主键设计方案(MsSQL)：
代码如下:
--主表
CREATE TABLE PP_MPSHeader(
BillNo VARCHAR(20) NOT NULL PRIMARY KEY,
PlanDate DATETIME NOT NULL
)
--从表
CREATE TABLE PP_MPSBody(
BillNo VARCHAR(20) NOT NULL,
LineNumber SMALLINT NOT NULL,
ProductID INT NOT NULL,
ProductQty DECIMAL(18,2) NOT NULL,
PRIMARY KEY(BillNo,LineNumber)
)
--设置外键
ALTER TABLE PP_MPSBody
ADD CONSTRAINT FK_PP_MPSHeader_MPSBody FOREIGN KEY(BillNo) REFERENCES PP_MPSHeader(BillNo)
这是典型的主从表结构。主表记录什么时候下达哪个单号的主计划，从表记录的是此计划生产哪些产品各多少数量，通过BillNo进行关联。当用户在下达一份主生产计划后，很可能会发现由于粗心大意输错了BillNo中计划单号信息，那么在他修改单号时，代码编写者需要在代码中控制从表的单号跟随主表的单号进行变动，否则单据将在外键的约束下无法保存，如果没有外键的约束，那么数据将失去其完整性。
如果按照上面的3个注意点，解决方案如下(MsSQL)：
代码如下:
--主表
CREATE TABLE PP_MPSHeader(
BillId INT PRIMARY KEY,
BillNo VARCHAR(20) NOT NULL,
PlanDate DATETIME NOT NULL
)
--从表
CREATE TABLE PP_MPSBody(
BillId INT PRIMARY KEY,
LineNumber SMALLINT NOT NULL,
ProductID INT NOT NULL,
ProductQty DECIMAL(18,2) NOT NULL,
PRIMARY KEY(BillId,LineNumber)
)
--设置外键
ALTER TABLE PP_MPSBody
ADD CONSTRAINT FK_PP_MPSHeader_MPSBody FOREIGN KEY(BillId) REFERENCES PP_MPSHeader(BillId)
现在，主从表通过BillId进行关联，当产生一份生产计划时，生成一个BillId，对于用户来说根本没有意义，在随后单据信息的改动中也不会出现上面的主从信息协调问题。同时从表的信息量小于上面的缺陷设计。因为原外键BillNo的长度从20个字节变成了现在的BillId4个字节，减少了信息的冗余。
这样的例子其实很多，比如：
有的设计原材料表时，使用零部件图号作为主键，那就意味着采购、生产、销售等等相关表中都会出现零部件图号的外键信息，当零部件图号信息发生变动时，这些所有先关的信息都需要跟着变动，这种缺陷如果不从根本上解决，那么你可能需要写个零部件图号变动处理过程，来批量处理这些问题，在处理的过程中可能你还得考虑处理的顺序问题……；
有的设计，使用身份证件号作为人员表的主键，但是身份证后来从15位变成了18位，这就意味着人员表中每个人的人员身份证信息都需要变动，如果你是某个社保机构此应用程序的设计人员，那么你就需要更新上百万条记录；那些所有由人员表通过身份证件号外联出去的信息记录将会以亿计数，那么也许余生你就不需要做其他工作了。
所以选择无意义的键值来作为主键的一部分，也是从长远意义上来避免类似这种改动的发生。