SQL - 只能在计算列上创建UNIQUE或PRIMARY KEY约束

I am trying to create a table with a computed column called profileID, however when I try this:

我正在尝试使用名为profileID的计算列创建一个表，但是当我尝试这个时：

CREATE TABLE Profiles 
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [profileID] AS ((id * 19379 - 62327) % 99991) NOT NULL
)

However when I goto create it, I get this error:

但是，当我转到创建它时，我收到此错误：

Only UNIQUE or PRIMARY KEY constraints can be created on computed columns, while CHECK, FOREIGN KEY, and NOT NULL constraints require that computed columns be persisted.

只能在计算列上创建UNIQUE或PRIMARY KEY约束，而CHECK，FOREIGN KEY和NOT NULL约束要求保留计算列。

I've tried to adjust the profileID line to this

我试图将profileID行调整为此

[profileID] as ( (id * 19379 - 62327) % 99991) NOT NULL UNIQUE

But I still get the same error.

但我仍然得到同样的错误。

FYI I have another column called id and its the primary key and is unique and auto_incremented.

仅供参考我有一个名为id的列，它是主键，并且是唯一的并且是auto_incremented。

1 个解决方案

#1

The error message is very clear - You can only use unique or primary key constraints on a computed column. You can't create a computed column with a not null constraint unless it's persisted.

错误消息非常清楚 - 您只能对计算列使用唯一或主键约束。您不能创建具有非空约束的计算列，除非它是持久的。

So either create that column as persisted:

因此要么将该列创建为持久化：

CREATE TABLE Profiles 
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [profileID] as ( (id * 19379 - 62327) % 99991) PERSISTED NOT NULL
)

Or simply remove the not null (it's never going to be null anyway)

或者只是删除not null（它永远不会是null）

CREATE TABLE Profiles 
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [profileID] as ( (id * 19379 - 62327) % 99991) 
)

#1