sql-server - SQL：如何拥有具有自动 SET 行为的非唯一“身份列”？

Question

这个想法

因此，标题可能看起来有些模糊，但这是我想要的（在 Microsoft SQL Server 中，最新版本），可用于 ASP.NET C# 应用程序：

• 具有普通主键的表，定义为“官方”标识列
• 其他一些列
• 额外的“逻辑标识”列

附加的“逻辑同一性”列应具有以下属性

• 是整数类型
• 不是严格唯一的（多行可以具有相同的“本地标识”）
• 强制的
• 不可变的（一旦设置，它可能永远不会改变）。但是必须允许删除行。
• 当INSERT 未提供时，设置为尚未使用的值

最后一点可能是最难实现的，所以这就是问题所在：

问题

当 INSERT 脚本未提供强制值时，如何强制（最好在数据库级别）始终将强制值设置为唯一值？

想法

我还考虑过：

• 在该列上具有正常的“身份”是不可能的，因为它在现有值中不是唯一的
• 不可能有随机值，因为它对于新值必须是唯一的
• 扩展 =SaveChanges= 方法会有问题，因为它需要查询其中的数据库
• 也许是数据库触发功能，但我希望有更简单的解决方案

上下文

• 在某些情况下，特别是当将有一个具有相同“逻辑标识”插入的附加行时，应用程序已经定义了“逻辑标识”，应该使用它。
• 目前，当应用程序将一个值设置为“逻辑 ID”时，它将在现有值中。因此，我可以强制数据库只接受至少存在一次的 INSERTed 值。当需要提供新的、独特的价值时，这将有助于它。
• 但是，如果这是某种新项目，则系统应在插入时即时提供新的“本地身份”。必须确定的是，没有为此重用现有值。
• 我将使用实体框架（版本 6）作为我的 ORM。
• 如果不满足上述要求，则应在“添加”上抛出异常
• 如果要更改此类值，则应在“更新”上引发异常

标签： sql-serverentity-framework-6schemasql-insert

Answer 1

一种选择是SEQUENCE分配有DEFAULT约束的值。不可变要求是最大的挑战，因为 SQL Server 不提供一种声明方式来指定只读列，因此需要触发器实现。

下面是示例 DDL。我不知道这种技术是否会给 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