December 7, 2005 at 8:42 am
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?
December 7, 2005 at 8:47 am
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.
December 7, 2005 at 9:14 am
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.
February 25, 2008 at 10:52 am
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.
February 25, 2008 at 7:12 pm
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.
May 2, 2009 at 3:46 am
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
December 18, 2011 at 7:00 pm
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply