error: Column already has a DEFAULT bound to it.

Good afternoon Experts

We are attempting to update a default value that is a date. when we attempt to alter this default value we get the following error.

Server: Msg 1781, Level 16, State 1, Line 1  
Column already has a DEFAULT bound to it.  
Server: Msg 1750, Level 16, State 1, Line 1  
Could not create constraint. See previous errors.

I read that this problem is caused when trying to update a default value that is all ready a date/time to a different date/time.  But I did not find a fix for it.

Anyone run into this problem before and have a solution to how we can alter this default value?

Thanks.
-Eric
LVL 1
moonrisesystemsAsked:
Who is Participating?
 
pauljk1619Connect With a Mentor Commented:
If you already know the name of it...

ALTER TABLE Customers DROP CONSTRAINT 'Name'
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you have first to remove the existing default constraint, and then you can add the new default constraint.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
you need to delete the constraint and recreate a new one with the default values

sp_helpConstraint tableName

wil list you the existingconstrainst
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
pauljk1619Commented:
DECLARE @defname VARCHAR(100), @cmd VARCHAR(1000)
SET @defname =
(SELECT name
FROM sysobjects so JOIN sysconstraints sc
ON so.id = sc.constid
WHERE object_name(so.parent_obj) = ‘YOURTABLENAME'
AND so.xtype = ‘D'
AND sc.colid =
 (SELECT colid FROM syscolumns
 WHERE id = object_id(‘dbo.YOURTABLENAME') AND
 name = ‘THECOLUMNNAME'))
SET @cmd = ‘ALTER TABLE Customers DROP CONSTRAINT ‘
+ @defname
exec(@cmd)
0
 
f_o_o_k_yConnect With a Mentor Commented:
As angelIII  said you must drop existing constraint

This can help you:

create table test(
a int,
b datetime default '2006-05-16')

alter table test add constraint ble default '2006-05-17' for b

sp_help test

--search on the bottom of output for apropriate constraint name for example  DF__test__b__239E4DCF

alter table test drop constraint DF__test__b__239E4DCF

alter table test add constraint ble default '2006-05-17' for b


0
 
moonrisesystemsAuthor Commented:
Ok so my script will need to function like this then if I understand this correctly

edit DEVDB
ALTER TABLE Users
DROP CONSTRAINT 'DF_DEVDB_ExpiryDateTime'
/*with this done, now we can reasing a new default value*/

ALTER TABLE Users
ADD DEFAULT (dateadd(day,90,getdate())) FOR ExpiryDateTime

Would this be correct?
0
 
f_o_o_k_yCommented:
No.

edit DEVDB
ALTER TABLE Users
DROP CONSTRAINT DF_DEVDB_ExpiryDateTime
/*with this done, now we can reasing a new default value*/

without ' '


ALTER TABLE Users
ADD DEFAULT (dateadd(day,90,getdate())) FOR ExpiryDateTime
0
 
moonrisesystemsAuthor Commented:
Great thanks, Ill get this sent out.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.