Hi Friends,
Do you want to know how to Alter Table for adding new column with Default value? The sysntax of sql server is:
ALTER TABLE table
{
ADD
{
column_name data_type [ ( size ) ]
[ DEFAULT value ]
{ [ NULL | NOT NULL ] | [ { PRIMARY KEY | UNIQUE } ] }
{ [ CONSTRAINT constraint_name ] }
}
|
ALTER COLUMN column_name
{
data_type [ ( size ) ] [ ( precision [ , scale ] ) ]
[ NULL | NOT NULL ]
}
|
DROP { COLUMN column_name | [ CONSTRAINT ] constraint_name }
}
- table
- Specifies which table is to be altered.
- column_name
- The name of the column being added, altered, or dropped.
- data_type
- The data type of the column being added or altered.
- size
- Is the length of the data that can be entered into a field.
- DEFAULT value
- Is the default value for the column being altered.
- NULL | NOT NULL
- Is a parameter that indicates whether a column can or cannot contain null values.
- PRIMARY KEY
- Is a parameter that identifies the column or set of columns whose values uniquely identify each row in a table. Each table can only have one primary key constraint.
- UNIQUE
- Is a constraint that enforces the uniqueness of the values in a set of columns.
- constraint_name
- The name of the constraint to be added or dropped.
- precision
- Specifies the precision for the data type.
- scale
- Specifies the scale for the data type.
The ALTER TABLE statement can be used to modify an existing table by adding, altering, or dropping columns and indexes.
Reference: http://www.devguru.com/technologies/t-sql/7120.asp
So when you want to set the default value when you are adding a new column in the databse table, you can use the following example. I have created a column named ‘tempTest’ and its datatype is ‘bit’ and default value is ‘0′ i.e. false.
ALTER TABLE tblTable
ADD isTempTest bit NOT NULL
CONSTRAINT [DF_tblTable_isTempTest] DEFAULT ((0))
I have added CONSTRAINT ‘DF_tblTable_isTempTest’ to set the default value 0.
You can add you comment here.
