Alter a Default Constraint in a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Alter a Default Constraint in a table

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-06-12 : 08:52:27

I have a default constratint on DateColumn getdate()-1

I have used enterprise manager to update it to yesterday's date everyday.

I would like to have a SQL which can check for the date in the system
or even a trigger which checks when the date changes the constraint is updated itself. If this is not possible I would like to have a stored procedure which I will schedule to run as a job everyday once.

So if today 6/12/2006, the default value in the Datecolumn should be
6/11/2006.

This gives me a error, i tried but could not fix the bug.

Alter Table TABLE_NAME
Alter Constraint DF_DATECOLUMN
Default getdate()-1

Ashley Rhodes

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-06-12 : 09:13:45

I do not think you can alter a CONSTRAINT. Try dropping and adding again.

ALTER TABLE TABLE_NAME
DROP CONSTRAINT DF_DateColumn
GO
ALTER TABLE TABLE_NAME
ADD CONSTRAINT DF_DateColumn DEFAULT DATEADD(day, -1 , DATEDIFF(day, 0, GETDATE())) FOR DateColumn
GO

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-06-12 : 09:13:48

I guess I have to do this everytime

alter table TABLE_NAME
drop constraint DFCONSTRAINT

alter table TABLE_NAME
add constraint DFCONSTRAINT
default getdate()-1
for COLUMN_NAME

Ashley Rhodes

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-06-12 : 09:15:27

IFOR you posted 3 seconds before me.

Question? is my default syntax getdate()-1 not perfect.

I tested and I don't get errors.

Ashley Rhodes

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-06-12 : 09:19:19

My version makes the time 00:00:00. Yours has the current time a day earlier. It depends what you want.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources