sql server:根据计数器和另一列值生成主键 [英] sql server: generate primary key based on counter and another column value

问题描述

我正在创建一个带有公司父表的客户表.已经指示(懊恼)我将为客户表创建一个主键，该主键是公司 ID 的组合，该公司 ID 是客户表中现有的 varchar(4) 列，例如客户.公司

I am creating a customer table with a parent table that is company. It has been dictated(chagrin) that I shall create a primary key for the customer table that is a combination of the company id which is an existing varchar(4) column in the customer table, e.g. customer.company

varchar(9) 主键的其余部分应是一个零填充计数器，随着该公司内的客户数量递增.

The rest of the varchar(9) primary key shall be a zero padded counter incrementing through the number of customers within that company.

例如其中公司 = MSFT，这是 MSFT 记录的第一次插入:PK 应为 MSFT00001在随后的插入中，PK 将是 MSFT00001、MSFT00002 等.那么当 company = INTL 并且插入它的第一条记录时，第一条记录将是 INTL00001

E.g. where company = MSFT and this is a first insert of an MSFT record: the PK shall be MSFT00001 on subsequent inserts the PK would be MSFT00001, MSFT00002 etc. Then when company = INTL and its first record is inserted, the first record would be INTL00001

我从我从其他 stackoverflow 响应中创建的一个而不是触发器和一个 udf 开始.

I began with an instead of trigger and a udf that I created from other stackoverflow responses.

ALTER FUNCTION [dbo].[GetNextID]
(
  @in varchar(9)
)
RETURNS varchar(9) AS
BEGIN
    DECLARE @prefix varchar(9);
    DECLARE @res varchar(9);
    DECLARE @pad varchar(9);
    DECLARE @num int;
    DECLARE @start int;


if LEN(@in)<9


 begin
   set @in = Left(@in + replicate('0',9) , 9)
  end

SET @start = PATINDEX('%[0-9]%',@in);
SET @prefix = LEFT(@in, @start - 1 );


declare @tmp int;
 set @tmp = len(@in)
 declare @tmpvarchar varchar(9);
 set @tmpvarchar = RIGHT( @in, LEN(@in) - @start + 1 )
    SET @num = CAST(  RIGHT( @in, LEN(@in) - @start + 1 ) AS int  ) + 1
    SET @pad = REPLICATE( '0', 9 - LEN(@prefix) - CEILING(LOG(@num)/LOG(10)) );
    SET @res = @prefix + @pad + CAST( @num AS varchar);

    RETURN @res
END

我将如何编写我的而不是触发器来插入值并增加此主键.或者我应该放弃它并开始割草业务?

How would I write my instead of trigger to insert the values and increment this primary key. Or should I give it up and start a lawnmowing business?

抱歉，tmpvarchar 变量 SQL 服务器在没有它的情况下给了我奇怪的结果.

Sorry for that tmpvarchar variable SQL server was giving me strange results without it.

推荐答案

虽然我同意反对者的看法，但恕我直言，接受无法改变的"原则往往会降低整体压力水平.尝试以下方法.

Whilst I agree with the naysayers, the principle of "accepting that which cannot be changed" tends to lower the overall stress level, IMHO. Try the following approach.

缺点

• 仅限单行插入.您不会对新客户表进行任何批量插入，因为每次要插入行时都需要执行存储过程.
• 一定数量的密钥生成表争用，因此可能会阻塞.

不过，从好的方面来说，这种方法没有任何与之相关的竞争条件，而且它并不太严重，真正冒犯了我的敏感性.所以...

On the up side, though, this approach doesn't have any race conditions associated with it, and it isn't too egregious a hack to really and truly offend my sensibilities. So...

首先，从密钥生成表开始.它将包含每个公司的 1 行，其中包含您的公司标识符和一个整数计数器，每次执行插入时我们都会增加该计数器.

First, start with a key generation table. It will contain 1 row for each company, containing your company identifier and an integer counter that we'll be bumping up each time an insert is performed.

create table dbo.CustomerNumberGenerator
(
  company     varchar(8) not null ,
  curr_value  int        not null default(1) ,

  constraint CustomerNumberGenerator_PK primary key clustered ( company ) ,

)

其次，您将需要一个这样的存储过程(实际上，您可能希望将此逻辑集成到负责插入客户记录的存储过程中.稍后会详细介绍).此存储过程接受公司标识符(例如MSFT")作为其唯一参数.此存储过程执行以下操作:

Second, you'll need a stored procedure like this (in fact, you might want to integrate this logic into the stored procedure responsible for inserting the customer record. More on that in a bit). This stored procedure accepts a company identifier (e.g. 'MSFT') as its sole argument. This stored procedure does the following:

• 将公司 ID 转换为规范形式(例如大写并修剪前导/尾随空格).
• 如果该行不存在，则将其插入到键生成表中(原子操作).
• 在单个原子操作(更新语句)中，获取指定公司的计数器的当前值，然后递增.
• 然后以指定方式生成客户编号，并通过 1 行/1 列 SELECT 语句返回给调用方.

• Puts the company id into canonical form (e.g. uppercase and trimmed of leading/trailing whitespace).
• Inserts the row into the key generation table if it doesn't already exist (atomic operation).
• In a single, atomic operation (update statement), the current value of the counter for the specified company is fetched and then incremented.
• The customer number is then generated in the specified way and returned to the caller via a 1-row/1-column SELECT statement.

给你:

create procedure dbo.GetNewCustomerNumber

  @company         varchar(8)

as

  set nocount                 on
  set ansi_nulls              on
  set concat_null_yields_null on
  set xact_abort              on

  declare
    @customer_number varchar(32)

  --
  -- put the supplied key in canonical form
  --
  set @company = ltrim(rtrim(upper(@company)))

  --
  -- if the name isn't already defined in the table, define it.
  --
  insert dbo.CustomerNumberGenerator ( company )
  select id = @company
  where not exists ( select *
                     from dbo.CustomerNumberGenerator
                     where company = @company
                   )

  --
  -- now, an interlocked update to get the current value and increment the table
  --
  update CustomerNumberGenerator
  set @customer_number = company + right( '00000000' + convert(varchar,curr_value) , 8 ) ,
      curr_value       = curr_value + 1
  where company = @company

  --
  -- return the new unique value to the caller
  --
  select customer_number = @customer_number
  return 0

go

您可能希望将其集成到将行插入到客户表中的存储过程中的原因是，它可以将所有数据整合到一个事务中；否则，当插入失败时，您的客户数量可能/将会出现缺口，土地被回滚.

The reason you might want to integrate this into the stored procedure that inserts a row into the customer table is that it makes globbing it all together into a single transaction; without that, your customer numbers may/will get gaps when an insert fails land gets rolled back.

这篇关于sql server:根据计数器和另一列值生成主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！