In this step by step SQL Tutorial I will show you how to create DEFAULT constraint. For the purpose of this tutorial I will use SQL Server 2012.
Other Versions of SQL Server:
- SQL Server 2012
- This blog post has been written using SQL Server 2012
- SQL Server 2008 / R2
- The examples we used should work in SQL Server 2008 and R2
- SQL Server 2005
- We suspect that this will work in SQL Server 2005 as well
Who is it for?
- People who would like to learn basics of using DEFAULT constraint.
- Those who are preparing for Microsoft certifications and need to cover this topic.
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.
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