DEFAULT CONSTRAINT

ALTER TABLE ADD DEFAULT CONSTRAINT Introduction

DEFAULT CONSTRAINT is used to insert a default value into a column during insert operation if no other value is specified for that column,

In my example I will show you how to set DEFAULT Constraint on TotalChildren field. It might happen that someone hasn't got any children and no value is specified in this case DEFAULT value will be added to all New rows as a zero (0). That means during insert even if TotalChildren is not used value 0 will be used.

ALTER TABLE ADD DEFAULT CONSTRAINT SYNTAX

ALTER TABLE TableName 
ADD CONSTRAINT def_DefaultName DEFAULT (value) FOR FieldName

In the Object Explorer I open required database in this case it is AdventureWorksDW2012 then I expand Tables folder and find table which will use to ALTER TABLE ADD CONSTRAINT DEFAULT example. In this case it is dbo.DimCustomer table. 

ALTER TABLE ADD DEFAULT CONSTRAINT Example

I create new query and type my code to create DEFAULT Constraint def_TotalChildren. and specify value 0.
I run my query and I can see in a Messages window that was successfully.

I go to Object Explorer to check if my Default Constraint was created. In a Constraints folder I see def_TotalChildren constraint appears.


I hope that helps
Take care