数据库方案，自增 [英] Database scheme, autoincrement

问题描述

数据库问题在这里.是否可以对辅助 ID 或第三方 ID 进行自动增量?我需要做一些基于版本的东西，所以想象一下:

Database question here. Is it possible to make an autoincrement on a secondary or a thirtiary ID? I need to make something versionbased, so imagine this:

ID  Phrase  PhraseID    PhraseVersion
1   ""      1           1
2   ""      1           2
3   ""      1           3
4   ""      2           1

添加到数据库时，PhraseID 可以是相同的数字.如果 PhraseID 存在，我希望 PhraseVersion 自动增加数字.如果 PhraseID 不存在，我希望 PhraseVersion 重新开始，从 1 开始计数.

PhraseID can be the same number, when added to the database. If the PhraseID exists, i want PhraseVersion to autoincrement in number. If the PhraseID doesnt exist, i want PhraseVersion to start over, counting from 1.

这可能吗?

推荐答案

我会为 PhraseVersion 使用一个计算列，它会计算具有相同 PhraseID 和 Id 小于或等于当前行.

I would go with a computed column for PhraseVersion, that will take the count of rows with the same PhraseID and Id lower or equal to the current row.

为此，您需要创建一个 UDF 来计算 PhraseVersion:

To do that, you need to create a UDF to calculate the PhraseVersion:

CREATE FUNCTION dbo.GetPhraseVersion (
    @PhraseId int,
    @id int
)
RETURNS INT
AS
BEGIN
    RETURN (
        SELECT COUNT(*) 
        FROM T 
        WHERE PhraseId = @PhraseId 
        AND Id <= @id
    )
END
GO

然后，使用计算列创建表:

Then, Create the table with the computed column:

CREATE TABLE T
(
    id int identity(1,1),
    PhraseId int,
    PhraseVersion as dbo.GetPhraseVersion(PhraseId, id)
)

GO

现在进行测试 - 插入 4 条记录:

Now for the test - insert 4 records:

INSERT INTO T (PhraseId) VALUES(1),(1),(1),(2)

选择:

SELECT *
FROM T

结果:

id  PhraseId    PhraseVersion
1   1           1
2   1           2
3   1           3
4   2           1

您可以在 rextester 上观看现场演示.

这篇关于数据库方案，自增的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！