SQL Server计算列作为主键

我创建了一个以计算列为主键的表.
表创建得很好.这里是脚本..

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

我的猜测是设计者正在删除表上的所有约束并将它们重新添加.它最终以错误的顺序添加它们,因此主键在计算列上的非空值之前分配.除了显而易见的不使用设计器之外,我不知道是否有任何解决方法.

SQL Server计算列作为主键

解决方法

猜你在找的MsSQL相关文章