Change DEFAULT value for a column

Question

Change DEFAULT value for a column

Richard Lesh

Right there with Babe

Points: 761
More actions
December 7, 2005 at 8:42 am

#95973

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?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ramses2nd Hall of Fame Points: 3720 More actions · Answer 1

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 Right there with Babe Points: 761 More actions · Answer 2

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 Right there with Babe Points: 761 More actions · Answer 3

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 SSCarpal Tunnel Points: 4828 More actions · Answer 4

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 SSCoach Points: 16106 More actions · Answer 5

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 SSC Journeyman Points: 84 More actions · Answer 6

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 SSC Rookie Points: 46 More actions · Answer 7

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