Help for default solution for column column column

Question

I have a table and I want to reset or set the default value for one of the 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 the errors below appear:

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

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

these scripts are taken from msdn .

What is the problem? I am using SQL Server 2008 R2.

Answer 1

I believe your link to SQL Server Compact Edition. Use this one instead.

You need to use the CONSTRAINT syntax and you need to use the default name. Although you have not assigned a name (and I suggest that you do so in the future, as this is good practice), SQL Server will assign one that you can find with 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

This link applies only to SQL Server Compact Edition, not to full SQL Server. The above statements apply only to SQL Server Compact Edition.

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

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

and lower it using

 ALTER TABLE AccAccountGroup DROP CONSTRAINT AccAcctGrp_Name_Def; 

Please note that you need to specify the name of the constraint when deleting it. If you do not know the name of the constraint, the next query will look for it. Change the dbo name as well as the names of tables and columns if / if necessary:

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

Once you get the name of the constraint, you can drop it. (Confirmation: this request was taken from a comment posted here .)