CODEWITHSUNDEEP
sqlsql-server-2005t-sqlsql-server-2008
Hari Menon
Hari Menon

Reputation: 35395

Create non-nullable column but populate existing NULLs with some given value

I want to add a new column "CreatedBy" to a table PerformanceData. I want this new column to be nnon-nullable, and I want existing NULLs to be populated as 'NA'. This is how I am doing this right now. Do we have a better way? 

ALTER TABLE PerformanceData ADD CreatedBy VARCHAR(50) NOT NULL 
CONSTRAINT DF1 DEFAULT('NA')   

ALTER TABLE PerformanceData DROP CONSTRAINT DF1

I create a DEFAULT constraint and drop it later, because I don't want values to be inserted by default in future.

Upvotes: 2

Views: 872

Answers (2)

gbn
gbn

Reputation: 432180

I'd do it your way in 2 steps with a default constraint.

Upvotes: 1

TcKs
TcKs

Reputation: 26632

It looks good for me. If you want another way, you can do it in three steps:

ALTER TABLE PerformanceData ADD CreatedBy VARCHAR(50) NULL 

UPDATE PerformanceData SET CreatedBy = 'NA'

ALTER TABLE PerformanceData ALTER COLUMN CreatedBy VARCHAR(50) NOT NULL

Upvotes: 4

Related Questions