MS Sql Server: Alter Table set default value

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.

About these ads
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

31 Responses to MS Sql Server: Alter Table set default value

  1. Soeren Pedersen says:

    Great stuff Vijay, just what i was looking for.

    Thanks mate.

  2. Hiren says:

    Really great article

  3. Jayabal says:

    Very very Useful Info. Thanks Vijay

  4. Samir Mistry says:

    Great. Saved me a good bit of time.

  5. Suresh Kumar says:

    This is what i searched in web. Thanks

  6. How can I copy and paste from MSDN too?

  7. Vijay Modi says:

    Michael,

    I do not understand your problem.

    Give detail,

    Regards,
    Vijay Modi

  8. reetesh says:

    HI Vijay,

    I want to alter a existing column and add defalult value.

    Can u Please Provide me Solution for this.

    Thanks

  9. Vijay Modi says:

    Hi Reetesh,

    the following will work:

    ALTER TABLE tblTable
    MODIFY isTempTest bit NOT NULL
    CONSTRAINT [DF_tblTable_isTempTest] DEFAULT ((0))

    Regards,
    Vijay Modi

  10. H Kamboj says:

    Hello Vijay,

    In the above example for “altering an existing column and adding defalult value”, I am getting the following error in SQL Server:

    Incorrect syntax near ‘MODIFY’.

    Can you provide some solution for this.
    Thanks.

  11. Vijay Modi says:

    Hi H Kamboj,

    Please find the following:

    ALTER TABLE tblTable
    ALTER isTempTest bit NOT NULL
    CONSTRAINT [DF_tblTable_isTempTest] DEFAULT ((0))

    Check this one and I think it will help you.

    Thanks & Regards,
    Vijay Modi

  12. Rich says:

    Is the use of a named constraint mandatory for adding a default value to a column?
    Thanks!

  13. DavidNL says:

    I don’ think its mandatory: From the SQL server 2000 help file:

    “constraint_name is the new constraint. Constraint names must follow the rules for identifiers, except that the name cannot begin with a number sign (#). If constraint_name is not supplied, a system-generated name is assigned to the constraint.”

  14. DavidNL says:

    Hmm, i have tested it now and i can not get it to work without supplying a contraint name >.<

    This works:

    ALTER TABLE MyTable
    ADD
    CONSTRAINT [test] DEFAULT (0) FOR FIELD1

    And this does not work:

    ALTER TABLE MyTable
    ADD
    CONSTRAINT DEFAULT (0) FOR FIELD1

  15. Steven says:

    What about:

    IF (SELECT Count(*) FROM sysobjects o, syscolumns c WHERE o.name = ‘DefinitionA’ and c.name = ‘gd_var’ and o.id = c.id) = 0
    BEGIN
    ALTER TABLE DefinitionA
    ADD gd_var BIT NOT NULL DEFAULT 0
    END

    ?
    :)

  16. scott says:

    Vijay, I was looking for information on how to add a default to an existing column and found your site.

    Michael O above was being sarcastic because your post is similar to the MSDN documentation. Don’t worry, your site is better.

    While I had to look elsewhere for my solution, I thought I’d add it to your site because it is so popular:

    USE [database]
    GO
    ALTER TABLE [dbo].[table_name] ADD CONSTRAINT [DF_table_name_column_name] DEFAULT (getdate()) FOR [column_name]
    GO

    Similar syntax can be used to drop the constraint.

  17. arunvishnoi says:

    my query is that if the value of the textbox in c# then alter table use in the query is
    “alter table Packing_Obj ADD ‘”+ textBox1.Text +”‘ varchar(50)”
    how can be enter the colume nane in the database

  18. Zain Shaikh says:

    wow, really good article, thanks, it helped me solving the issue i was facing. (Y)

  19. Ushar says:

    Hi Vijay Modi,

    A default column value can be equal to the number of records in other tables?

    For Example :

    CREATE TABLE Orders
    (
    O_Id int NOT NULL,
    OrderNo int NOT NULL,
    P_Id int,
    OrderDate date DEFAULT ‘Select Count(id) etc. etc.’

    )

    Best regards.

  20. Jarek says:

    thanks, it was helpful

  21. Vanden says:

    Hi Vijay Modi,

    another – keep it simple – example….

    ALTER TABLE mytable ADD field_usemaptxt bit DEFAULT 0 NOT NULL, field_maptxt ntext DEFAULT (”) NOT NULL

    …without CONSTRAINT

  22. NatFlooft says:

    Thanks for making such a valuable blog, sincerely Kobos Mathers.

    Gucci Shoes

  23. vel says:

    when the column value is null. how to set the default value?

  24. deepika says:

    Hi,
    am working with MSAccess DB. Whats the query to alter the table with default value as 0.?

  25. Jalpa says:

    Hi Vijay

    Here i want to set default for int in sql server 2008 R2
    Can you help me please
    Something like this
    [ItemId1] [int] NOT NULL default(1),

    but it is raise error when i try to insert null value in Item1
    it is not raise error when i create table

    Thanks

  26. Johnny says:

    I had an existing Table: POP and in the Table had Column: ABC

    I want to use ALTER TABLE to Set Default value… May i know how?

  27. Gajraj singh says:

    Hi
    Gajraj Singh

    Thanks

  28. Gajraj singh says:

    I Want to set Default value in table ……………..May i know How

    Thanks

  29. srikanth says:

    i want to alter the existing default value on column without dropping the constraint. please help me.

    I dont want to loss the existing data on column in production and just want to alter the default value so that future entried will set to new value…

    I would appriciate your help.

  30. Hi..

    Vijay Sir,

    This article helpful to me sir..!!! Thank you sir.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s