Set Default Value Error On SQL SERVER

Question

I Cannot Set Default Value For the existing columns, even though there are no constraint or trigger depending on it, please see attachment below

Answer 1

hi all, thanks for all your reply, it seems this bugs happened because the other user created database trigger , so when change that table scheme, there was a trigger that execute on backgroud that make this bugs occured. case closed

Answer 2

Have you read the error message?

Column already has a DEFAULT bound to it

Answer 3

The Table Designer is a glorious piece of junk. Stay way from it. There are a lot worse issues with it than this one.

   ALTER TABLE tbl ADD CONSTRAINT tbl_default_status DEFAULT 0 FOR Status  

Answer 4

Hi @Wendy Liang ,

May be there is one default constraint that you did not find. Please try to use below T-SQL to check and drop the default constraint for this column. Then create a new one for this column. Quote from this thread How to drop SQL default constraint without knowing its name;

declare @schema_name nvarchar(256)  
declare @table_name nvarchar(256)  
declare @col_name nvarchar(256)  
declare @Command  nvarchar(1000)  
  
set @schema_name = N'MySchema'  
set @table_name = N'Department'  
set @col_name = N'ModifiedDate'  
  
select @Command = 'ALTER TABLE ' + @schema_name + '.[' + @table_name + '] DROP CONSTRAINT ' + d.name  
 from sys.tables t  
  join sys.default_constraints d on d.parent_object_id = t.object_id  
  join sys.columns c on c.object_id = t.object_id and c.column_id = d.parent_column_id  
 where t.name = @table_name  
  and t.schema_id = schema_id(@schema_name)  
  and c.name = @col_name  
  
--print @Command  
  
execute (@Command)  

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 5

I think it was created with CREATE DEFAULT command, which is deprecated.

I tried to find the link quickly, but only found https://www.sqlservercentral.com/forums/topic/default-value-or-binding-right-clicking-tables-column-and-properties-vs-table-designer-different-values-show - you may start your research from there.

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-default-transact-sql?view=sql-server-ver16

You would need to use sp_unbinddefault command to fix this issue.