SQL Server:自动递增 char pk

2022-01-12 00:00:00 char sql-server auto-increment

表上是否可以有一个 char 主键?比如'WC001'会自动加1，所以pk的下一条记录就是'WC002'等等.

Is it possible to have a char primary key on a table? For example 'WC001' then will it automatically increment by 1, so the next record for the pk will be 'WC002' and so on.

谁能给我举个例子?

谢谢

推荐答案

不是直接 - 但你可以有一个普通的 INT IDENTITY 自动递增数字 ID，然后定义一个计算的持久列(SQL Server2005 年及更新版本) - 类似于:

Not directly - but you could have a normal INT IDENTITY auto-incrementing numerical ID and then defines a computed persisted column (SQL Server 2005 and newer) - something like:

CREATE TABLE dbo.YourTable
(ID INT IDENTITY(1,1),
 CharID AS 'WC' + RIGHT('000' + CAST(ID AS VARCHAR(3)), 3) PERSISTED,

 CONSTRAINT PK_YourTable PRIMARY KEY(CharID)
)

将值插入此表将导致 ID 列为 1, 2, 3, 4, 5, ..... 和 CharID 列将自动为 WC001、WC002、WC003 等.

Inserting values into this table will cause the ID column to be 1, 2, 3, 4, 5, ..... and the CharID column will automatically be WC001, WC002, WC003 and so forth.

由于它是一个持久化计算列，其值始终是最新的，您甚至可以在其上放置一个索引(如主键).

Since it's a persisted computed column, the values is always up to date, and you can even put an index (like the primary key) on it.

相关文章