How to create or alter existing or drop Default column in SQL Server.
How to create a table with default value in SQL Server
In SQL Server we can create a default column, which inserts predefined values, if not provided in SQL statement.
Let us jump to the topic and do some practical example to understand it better.
Create Default column Syntax:
Create Table YourTable_Name (yourColumn_Name1 DataType Constraint Const_Name Default (DefaultValue) , yourColumn_Name2 DataType )
Let`s create a table using this syntax:
Create Table TableWithdefaultvalue (ID Int Constraint DF_ID DEFAULT(0) , name Varchar(10) , Country Varchar(10) )
In the above example, we have an ID column which will accept the value 0 (zero) as default value in TableWithdefaultvalue table. Default constraint works with all data types, let us try again, but this time with Varchar data type.
Create Table TableWithdefaultvalueVarchar (ID Int ,name Varchar(10) ,Country Varchar(10) constraint DF_Name default 'India' )
It is also possible to create a default constraint to an existing table using ALTER command.
Alter Table Add Default Constraint Syntax:
Alter Table Table_Name Add Constraint Const_Name Default(Default_Value) For Column_Name
Let us use the above syntax in the example below:
Alter table TableWithdefaultvalueVarchar add CONSTRAINT DEF_ID default(1) for ID
In the above example, we are adding default value "0" for ID column. We can also drop default constraint if we don`t want it to use in the table using below syntax.
Drop (Default) Constraint Syntax:
Alter Table TableName Drop Constraint ConstraintName
Let us use the above syntax to drop "DF_ID" constraint of the TableWithdefaultvalue table.
Alter table TableWithdefaultvalue drop CONSTRAINT DF_ID
Test What We Learnt above
In this tutorial, we learnt that how we can set a column to default value, so let us put some data in the table and see the result.
insert into TableWithdefaultvalueVarchar (name) values ('Neeraj')
select * From TableWithdefaultvalueVarchar
We have only inserted data for NAME column. But we can see the values for ID and Country columns as well because those are Default values for the respective columns.
Default column doesn`t mean that default column will only contain predefined values, we can insert data in those columns as well see below:
insert into TableWithdefaultvalueVarchar values (2,'Tom' , 'England')
select * From TableWithdefaultvalueVarchar