Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Change DEFAULT value for a column


Change DEFAULT value for a column

Author
Message
Richard Lesh
Richard Lesh
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 105

I have a column:

ALTER TABLE dbo.bat_Batches ADD Version int NOT NULL CONSTRAINT DF_bat_Batches_Version DEFAULT 1

I now want to set the default to 2. I can do it thru Enterprise Manager, but what is the proper SQL DDL statement to change it?


ramses2nd
ramses2nd
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 294
Generally in this case I would just drop the existing constraint and add the new new one. If you do this through Enterprise manager while running a server trace you will find that this is how it accomplishes this as well.



Richard Lesh
Richard Lesh
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 105

Can you be more specific? I can't seem to find the right syntax. Have tried things like:

ALTER TABLE dbo.bat_Batches ALTER COLUMN
Version int NOT NULL CONSTRAINT DF_bat_Batches_Version DEFAULT 2

and

ALTER TABLE dbo.bat_Batches ALTER COLUMN
Version SET DEFAULT 2

and others. I can't seen to get the right format.


Richard Lesh
Richard Lesh
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 105

Oh! Got it now. Thanks, ramses2nd! Syntax is:

ALTER TABLE dbo.bat_Batches
DROP CONSTRAINT DF_bat_Batches_Version;
ALTER TABLE dbo.bat_Batches ADD CONSTRAINT
DF_bat_Batches_Version DEFAULT 2 FOR Version;


Mile Higher Than Sea Level
Mile Higher Than Sea Level
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 465
SQL Server 2005
Trying to add a default value of A to a field with no existing constraint, that allows nulls (the current default).
(this first command does not work because there is no constraint by this name)
ALTER TABLE dbo.TMGSAR
DROP CONSTRAINT DF_TMGSAR_B_DATA_QUALITY;

alter Table
dbo.TMGSAR Add constraint DF_TMGSAR_B_DATA_QUALITY DEFAULT 'A' for B_Data_Quality

This fails becuse the default of Null is considered a default - although it is not listed as a constraint:
Msg 1781, Level 16, State 1, Line 1
Column already has a DEFAULT bound to it.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

If I manually add the 'A' as the default using Enterprise manager - the DF_TMGSAR_B_DATA_QUALITY constraint was added.
After that point.. there is no problem using this code.

The problem is that I am restoring a DB that that needs this Default of 'A' added.
Greg Snidow
Greg Snidow
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1611 Visits: 2478
Richard Lesh (12/7/2005)
I can do it thru Enterprise Manager, but what is the proper SQL DDL statement to change it?


If you can do it through EM, instead of hitting the save button on the tool bar, hit the one that looks like a little scroll. It will show you the script that is generated by EM.

Greg

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
garima_janu
garima_janu
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 7
if you want to alter column with default value you delete its constraint using by this column and then alter the column with default value
MKKAMIL
MKKAMIL
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 1
For SQL Sever 2008 do the following-

ALTER TABLE dbo.bat_Batches DROP CONSTRAINT DF_bat_Batches_Version

ALTER TABLE dbo.bat_Batches ADD CONSTRAINT DF_bat_Batches_Version DEFAULT ((2)) FOR bat_Batches_Version
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search