Change primary key from null to not null

Question

I have a field ID that has been set as a primary key and null, I tried to change it to not null with the code below but I got an error saying ALTER TABLE ALTER COLUMN ID failed because one or more objects access this column. how can i change it without dropping the table.

ALTER TABLE [dbo].[RCHOLE]
ALTER COLUMN ID varchar(18) NOT NULL

Answer 1

You Got This Error Because RCHOLE_Block_ID is Dependent On ID Column so You have To Remove Dependency to add NOT Null Constraint.In You Case Error show that So You have To Remove That Dependency To Apply NOT NULL constraint

Answer 2

I was able to change the ID field to Not Null, by simply going to Tools from SSMS, Options, Designers, Table and Database Designers and unticked the following option "prevent saving changes that require table re-creation" then I executed the following script.

ALTER TABLE [dbo].[RCHOLE] 
ALTER COLUMN ID varchar(18) NOT NULL

Answer 3

Potential answer:
- The RCHOLE.ID column may be a FK in the OtherTable (on OtherTable.RCHOLE_BLOCK_ID). Try to temporary disable that FK, retry adding the not null constraint and then re-add the FK (with check).