Alter Default set value for a column [Resolved]

Posted by Vasanthmvp under Sql Server on 11/2/2012 | Points: 10 | Views : 1349 | Status : [Member] | Replies : 3
Hi All,
Can someone demonstrate me on
-- How to change the default set value of a column (Default constraint enforced on it) with an example in sql server 2008.
No links plz..

Regards,

Awesome Coding !! :)


Responses

Posted by: Pandians on: 11/2/2012 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
Check It Out!

/*Creating a sample table with Default constraint on it*/
Create Table Tbl_DefaultSample

(
ID Int Identity(1,1) Primary Key,
Name Varchar(100),
Gender Varchar(10) Default 'Male',
Age Int
)
Go
/*Using default value on "Gender" column*/
Insert Tbl_DefaultSample(Name,Age) Values('Aequea',25)

Go
/*
ID Name Gender Age
1 Aequea Male 25
*/
/*Explicitly inserting value on "Gender" column*/
Insert Tbl_DefaultSample(Name,Gender,Age) Values('Salino','Female',20)

Go

/*
ID Name Gender Age
1 Aequea Male 25
2 Salino Female 20
*/
/*DEFAULT constraint can not be changed. It has to be DROPPED and Recreated*/
SELECT Name FROM SYS.DEFAULT_CONSTRAINTS

WHERE parent_object_id = OBJECT_ID('Tbl_DefaultSample')
GO
/*Drop the Default constraint*/
ALTER TABLE Tbl_DefaultSample DROP CONSTRAINT DF__Tbl_Defau__Gende__14E61A24

GO
/*Creating Default constraint again with different value*/
ALTER TABLE Tbl_DefaultSample ADD CONSTRAINT DF__Tbl_Defau__Gende__14E61A24 DEFAULT 'Female' FOR Gender

GO
/*Using default value on "Gender" column*/
Insert Tbl_DefaultSample(Name,Age) Values('Setaceo',19)

Go

/*
ID Name Gender Age
1 Aequea Male 25
2 Salino Female 20
3 Setaceo Female 19
*/
You got any idea ?

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Vasanthmvp, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vasanthmvp on: 11/2/2012 [Member] Starter | Points: 25

Up
0
Down
Hello Sir,
Very good explanation.. Thanks.
I got the solution this way sir..
Is there any other way without dropping the constraint.. the way we alter a column.

Regards,

Awesome Coding !! :)

Vasanthmvp, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Saratvaddilli on: 11/3/2012 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi vasanthmvp good question ,
we can't alter directly with out dropping the default constraint on it
ALTER TABLE TableName ALTER COLUMN ColumnName DEFAULT (10)
Error message is displayed trying to execute this: "Incorrect syntax near the keyword 'DEFAULT'".
Mistake.

go through this link it might give you better idea
http://bypsoft.blogspot.in/2007/10/changing-default-column-values-sql.html

Thanks and Regards
V.SaratChand
Show difficulties that how difficult you are

Vasanthmvp, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response