[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4491
  • Last Modified:

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
0
moonrisesystems
Asked:
moonrisesystems
  • 2
  • 2
  • 2
  • +2
3 Solutions
 
Guy Hengel [angelIII / a3]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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
pauljk1619Commented:
If you already know the name of it...

ALTER TABLE Customers DROP CONSTRAINT 'Name'
0
 
f_o_o_k_yCommented:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now