-1

I want to add a Column to a table in my Database. As several data already exist in that particular table , MSSQL does not allow me to add a not Null field. So I can use a default keyword to solve this problem and run the below mentioned Query - 

  Alter table ServiceDetails 
  Add  OCF_Internal_ID varchar(50) not null default 'OCF';

But now I want to make the "OCF_Internal_ID" field Primary key and I need to insert unique values to the every record of that particular filed. Please give me any suggestion.

How can I add Unique values to all the existing records ?

Rajesh Loganathan
  • 11,129
  • 4
  • 78
  • 90
Paul S
  • 81
  • 2
  • 13
  • Why `varchar(50)` rather than numeric and `identity`? Or `uniqueidentifier`? – Martin Smith Dec 08 '14 at 06:12
  • Sounds like you don't care what the value is. Why don't you modify the table and add a Int Identity Primary Key Clustered column instead of a varchar(50) which makes a poor primary key. – Mike Burdick Dec 08 '14 at 06:12

1 Answers1

1

You can use one of these options

Alter table ServiceDetails 
  Add  OCF_Internal_ID integer NOT NULL IDENTITY (1,1);

OR 

Alter table ServiceDetails 
  Add  OCF_Internal_ID uniqueidentifier NOT NULL default newid();

Then run this do define a primary key constraint

ALTER TABLE ServiceDetails  
ADD CONSTRAINT PK_ServiceDetails_OCF_Internal_ID  PRIMARY KEY CLUSTERED (OCF_Internal_ID);
GO
Raj
  • 10,653
  • 2
  • 45
  • 52