SQL: How to have a non-unique "identity column", with automatic SET behaviour?

Question

The idea

So, the title may seem somewhat vague but this is what I would like to have (in Microsoft SQL Server, recent version), to be used from an ASP.NET C# application:

• A table with an ordinary primary key, defined as an "official" identity column
• some other columns
• An additional "logical identity" column

The additional "logical idendity" column should have the following properties

• be of type integer
• not strictly unique (multiple rows can have the same "locigal idendity")
• mandatory
• immutable (once set, it may never change). However DELETE of the row must be allowed.
• When not provided at INSERT, set to a not yet used value

The last point is probably the hardest to achieve, so that's the question:

The question

How to enforce (preferably on the database level) that a mandatory value is always set to a yet unique value, when not provided by the INSERT script?

The thoughts

What I have considered yet:

• Having a normal "identity" on that column is not possible because it's not unique among the existing values
• Having a random value is not possible, because it must be unique for new values
• Extending the =SaveChanges= Method would be problematic, because it would require to query the database in it
• Maybe a database triggered function, but I would hope that there are easier solutions

The context

• On some occations, especially when there will be an additional row with the same "logical idendity" insert, the application already defines the "loigcal idendity", and it should be used.
• Currently, when the application sets a value as "logical ID" it will be among the existing values. Thus, I could force the database to accept only INSERTed values that at least exist once. This would help it when required to provide new, unique values.
• However, if this is some sort of new item, the system should provide a new "locigal idendity" on the fly, while inserting. It must be sure, that no existing value is reused for this.
• I will use Entity Framework (Version 6) as my ORM.
• If the above requirements are not met, an exception should be thrown on the "Add"
• If such a value would be changed, an exception should be thrown on the "Update"

Answer 1

One option is with a SEQUENCE value assigned with a DEFAULT constraint. The immutable requirement is the biggest challenge because SQL Server doesn't provide a declarative way to specify a read-only column so one needs a trigger implementation.

Below is example DDL. I don't know if this technique will pose challenges with EF.

CREATE SEQUENCE SQ_Example_Sequence
    AS int START WITH 1 INCREMENT BY 1;
GO
CREATE TABLE dbo.Example(
     IdentityColumn int NOT NULL IDENTITY
        CONSTRAINT PK_Example PRIMARY KEY CLUSTERED
    ,SomeDataColumn int
    ,SequenceColumn int NOT NULL 
        CONSTRAINT DF_Example_SequenceColumn DEFAULT NEXT VALUE FOR SQ_Example_Sequence
);
GO
CREATE TRIGGER TR_Example_Update
    ON dbo.Example FOR UPDATE 
AS
IF EXISTS(SELECT 1
    FROM inserted
    JOIN deleted ON inserted.IdentityColumn = deleted.IdentityColumn
    WHERE inserted.SequenceColumn <> deleted.SequenceColumn
    )
BEGIN
    THROW 50000, 'SequenceColumn value cannot be changed', 16;
END;
GO