Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Change DEFAULT value for a column Expand / Collapse
Author
Message
Posted Wednesday, December 7, 2005 8:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:02 PM
Points: 31, Visits: 97

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? 

Post #242552
Posted Wednesday, December 7, 2005 8:47 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, May 30, 2012 8:02 AM
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.


Post #242555
Posted Wednesday, December 7, 2005 9:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:02 PM
Points: 31, Visits: 97

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.

Post #242566
Posted Wednesday, December 7, 2005 9:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:02 PM
Points: 31, Visits: 97

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;

Post #242568
Posted Monday, February 25, 2008 10:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 20, 2014 10:36 AM
Points: 123, Visits: 349
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.

Post #459833
Posted Monday, February 25, 2008 7:12 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 9, 2014 2:39 PM
Points: 1,563, Visits: 2,395
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.
Post #460033
Posted Saturday, May 2, 2009 3:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 25, 2010 5:58 AM
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
Post #708886
Posted Sunday, December 18, 2011 7:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 14, 2012 9:37 AM
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
Post #1223599
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse