如何在sql server表中创建字母数字自动生成的列并生成相同的列主键。 [英] How to create an alpha numeric autogenerated column into sql server table and make the same column primary key.

问题描述

如何在sql server表中创建一个字母数字自动生成的列并使该列成为主键。

请帮帮我。

解决方案

您可以使用CHAR或VARCHAR作为列类型，具体取决于您是否需要固定长度。

然后将其设置为IDENTITY列。



对于自动生成部件，你必须自己动手。



您可以在存储过程中或在插入行之前触发的触发器。

无论哪种方式，你都需要有一种方法来正确增加字符串的序列号部分。

基本上你是什么要说的是你想拥有一个由前缀组成的密钥，例如TK-NO-，以及一个带有3位数的唯一序列号部分，例如000-999。

这个设计给出了在用完钥匙之前，你只有1000个唯一的数字，所以也许你需要更多的数字或更改前缀的方法。



另一个小问题是依赖插入的行数。如果您以后决定允许从表中删除行，该怎么办？

然后您肯定会得到重复项。

更好的方法是获取最后插入的行，提取主键的数字部分并递增1.

  CREATE   PROCEDURE  AddRow（ IN  _someData  VARCHAR ） 
  BEGIN  
  SET   @前缀 = '  TK-NO  - ';   -   除非您想将此作为参数 
 
   -   获取表格中最后插入的行 
  SET   @ currentPK  = IDENT_CURRENT（'tablename'）; 
 
   -   提取序列号并转换为int并递增1  
  SET   @ sequenceNo  =  CONVERT （ INT ， RIGHT （currentPK， 6 ））+  1 ; 
 
   -   创建新密钥 
  SET   @ newKey  = CONCATE（ @ prefix ， @ sequenceNo ）; 
  END  

另请参阅本文使用SQL Server自定义自动生成的序列 [< a href =http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-servertarget =_ blanktitle =New Window> ^ ]

How to create an alpha numeric autogenerated column into sql server table and make this column primary key.
Please Help me .

解决方案

You can use CHAR or a VARCHAR as the column type, depending on if you want to have a fixed length or not.
Then just set it as the IDENTITY column.

For the auto-generation part, you have to do that your self.

You can do it either in a stored procedure or in a trigger that is fired before the row is inserted.
Either way you need to have a way to correctly increment the sequence number part of the string.
Basically what you are saying is that you want to have a key that consists of a prefix, such as TK-NO-, and a unique sequence number part with 3 digits, such as 000-999.
This design gives you only a 1000 unique numbers before you run out of keys, so maybe you need more digits or a way to change the prefix.

Another small issue is to rely on the number of rows inserted. What if you later on decide that you are going to allow rows to be deleted from the table?
Then you will end up with duplicates for sure.
A better way is to get the last inserted row, extract the numeric part of the primary key and increment by 1.

CREATE PROCEDURE AddRow(IN _someData VARCHAR)
BEGIN
    SET @prefix = 'TK-NO-'; -- Unless you want this as a parameter

    -- Get the last inserted row in the table
    SET @currentPK = IDENT_CURRENT(‘tablename’);

    -- Extract the sequence number and convert to an int and increment by 1
    SET @sequenceNo = CONVERT(INT, RIGHT(currentPK, 6)) + 1;

    -- Create the new key
    SET @newKey = CONCATE(@prefix, @sequenceNo);
END

See also this article Custom Auto-Generated Sequences with SQL Server[^]

这篇关于如何在sql server表中创建字母数字自动生成的列并生成相同的列主键。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！