Help for solving drop column default value of a table error

Question

I have a table and I want to drop or set the default value of one of columns. I use below scripts :

ALTER TABLE AccAccountGroup ALTER COLUMN Name DROP DEFAULT

ALTER TABLE AccAccountGroup ALTER COLUMN Name SET DEFAULT 'default value'

when I run the scripts, below errors appears :

Incorrect syntax near the keyword 'DEFAULT'. => for drop script

Incorrect syntax near the keyword 'SET'. => for add script

these scripts are from msdn.

What is the problem? I use SQL Server 2008 R2.

Answer 1

I believe that your reference is for SQL Server Compact Edition. Use this one instead.

You need to use the CONSTRAINT syntax and you need to use the default's name. Even though you didn't assign a name (and I suggest that you do in the future as it's a good practice), SQL Server will assign one, which you can find using EXEC sp_help AccAccountGroup.

Try these syntaxes:

ALTER TABLE AccAccountGroup
DROP CONSTRAINT <default name>

ALTER TABLE AccAccountGroup
ADD CONSTRAINT DF_AccAccountGroup_name DEFAULT 'default value' FOR name

Answer 2

The link you provided refers only to SQL Server Compact Edition, not the 'full' SQL Server. The above statements are only valid in SQL Server Compact Edition.

In the 'full' SQL Server, default values for columns are implemented using constraints. You can add a default constraint to a table using something like

ALTER TABLE AccAccountGroup ADD CONSTRAINT AccAcctGrp_Name_Def DEFAULT 'default value' FOR name;

and drop it using

ALTER TABLE AccAccountGroup DROP CONSTRAINT AccAcctGrp_Name_Def;

Note that you need to provide the name of the constraint when you drop it. If you don't know the name of the constraint, the following query will look it up. Change the schema name dbo, and also the table and column names, if/as necessary:

SELECT object_name(default_object_id)
  FROM sys.columns
 WHERE object_id = object_id('dbo.AccAccountGroup')
   AND name = 'Name';

Once you've got the name of the constraint, you can drop it. (Acknowledgement: this query was taken from a comment posted here.)