How to add non-nullable columns with default values

Question

Often we need to add a non-nullable column to a table, and it is quite a mission. Using a default constraint as is doesn’t work, so we have to create nullable columns, update them to default values, then make them non-nullable. Is there not an easier way to do this?

ProfK · Accepted Answer · 2008-10-08 14:21:15Z

13

Yes, the WITH VALUES modifier to a DEFAULT constraint applies the default value to existing rows, eliminating all the 'hard' work described in the question.

IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id=OBJECT_ID('[caConfig]') AND [Name]='ExportWizardVersion')
ALTER TABLE [caConfig] 
    ADD 
        [ExportWizardVersion] varchar(5) not null CONSTRAINT DF_caConfig_ExportWizardVersion DEFAULT '5.8' WITH VALUES,
        [ExportPeriodEnd] varchar(10) not null CONSTRAINT DF_caConfig_ExportPeriodEnd DEFAULT 'MonthEnd' WITH VALUES

answered Oct 8, 2008 at 14:21

ProfK

51.4k127 gold badges419 silver badges809 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to add non-nullable columns with default values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related