Database scheme, autoincrement

Question

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 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.

I this possible?

Answer 1

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.

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

Now for the test - insert 4 records:

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

Select:

SELECT *
FROM T

Results:

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

You can see a live demo on rextester.