moonrisesystems
asked on
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
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
SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
AND so.xtype = ‘D'
AND sc.colid =
(SELECT colid FROM syscolumns
WHERE id = object_id(‘dbo.YOURTABLENA
name = ‘THECOLUMNNAME'))
SET @cmd = ‘ALTER TABLE Customers DROP CONSTRAINT ‘
+ @defname
exec(@cmd)
SET @defname =
(SELECT name
FROM sysobjects so JOIN sysconstraints sc
ON so.id = sc.constid
WHERE object_name(so.parent_obj)
AND so.xtype = ‘D'
AND sc.colid =
(SELECT colid FROM syscolumns
WHERE id = object_id(‘dbo.YOURTABLENA
name = ‘THECOLUMNNAME'))
SET @cmd = ‘ALTER TABLE Customers DROP CONSTRAINT ‘
+ @defname
exec(@cmd)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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())
Would this be correct?
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())
Would this be correct?
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())
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())
ASKER
Great thanks, Ill get this sent out.
sp_helpConstraint tableName
wil list you the existingconstrainst