SQL Server Computed Column as Primary Key

Question

I've created a table with a computed column as the primary key. Table is created fine.And here is the script..

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

The problem is when I change this table (Add/edit a column ) using Designer View,it gives me the following error.

error

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

When I use script to do modifications,it works. And Even I have declared the computed column as NOT NULL. How this happen??

Answer 1

Something is wrong with the designer. SQL Server is quite clear in the documentation that computed columns can be used for primary keys (for instance, here).

My guess is that the designer is dropping all constraints on the table and adding them back in. It ends up adding them in the wrong order, so the primary key is assigned before the not null on the computed column. I have no idea if there is any work-around other than the obvious one of not using the designer.