我创建了一个以计算列作为主键的表。
表创建良好。这是脚本。

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ARITHABORT ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [planning.A062].[RMAllocation](
    [Id] [int] IDENTITY(100,1) NOT NULL,
    [RMAllocatonId]  AS ('RMA_'+CONVERT([nvarchar](100),[Id])) PERSISTED NOT NULL,
    [RequsitionNo] [nvarchar](100) NULL,
    [RMDemandId] [nvarchar](104) NULL,
    [HierarchyId] [nvarchar](102) NULL,
    [Season] [nvarchar](50) NULL,
    [VendorSupplierNo] [nvarchar](100) NULL,
    [Year] [int] NULL,
    [Month] [int] NULL,
    [Week] [int] NULL,
    [Day] [int] NULL,
    [PlannedQty] [int] NULL,
    [ConfirmedQty] [int] NULL,
    [Status] [int] NULL,
    [CreatedBy] [int] NULL,
    [SyncId] [nvarchar](100) NULL,
    [CreatedOn] [datetime2](7) NULL,
    [UpdatedBy] [int] NULL,
    [UpdatedOn] [datetime2](7) NULL,
    [IsActive] [bit] NULL,
    [RecordDateTime] [datetime2](7) NULL,
 CONSTRAINT [PK_RMAllocation] PRIMARY KEY CLUSTERED
(
    [RMAllocatonId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

问题是当我使用Designer View 更改此表(添加/编辑列)时,出现以下错误。

错误
Unable to create index 'PK_RMAllocation'.
Cannot define PRIMARY KEY constraint on nullable column in table 'RMAllocation'.
Could not create constraint. See previous errors.

当我使用脚本进行修改时,它可以工作。甚至我已经将计算列声明为NOT NULL。这是怎么发生的?

最佳答案

这个评论太长了。设计师出了点问题。 SQL Server在文档中非常清楚地表明,计算列可用于主键(例如here)。

我的猜测是,设计人员将所有约束都放到表上,然后又将它们重新添加。最终以错误的顺序添加了约束,因此在计算列上的not null之前分配了主键。除了不使用设计器这很明显之外,我不知道是否有其他解决方法。

10-07 14:15