When you try alter column in MSSQL 2005 from one datatype to other with some default value by ALTER TABLE xxx ALTER COLUMN column_name you get error about existing constraint. Unfortunately there is nothing like CASCADE CONSTRAINT. You must DROP constraint first and then you can ALTER column and finally you have to create CONSTRAINT again. I will show you easy way, how to do it.

Are you familiar with error message similar like this one?

Msg 5074, Level 16, State 1, Line 1
The object 'DF__Exampl__DateM__61F8A504' is dependent on column 'DateModified'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN DateModified failed because one or 
more objects access this column.

Steps you must do in T-SQL code

1. get default constraint name
2. create alter table command to drop it
3. run created command
4. alter table to alter your column
5. re-create constraint

Typical example, we have column with smalldatetime and we want enhanced it to datetime

Here you have code example for drop column with default value

-- first define variables
declare @default sysname, @sql nvarchar(max)
-- get name of default constraint
select @default = name 
from sys.default_constraints 
where parent_object_id = object_id('TABLE_NAME')
AND type = 'D'
AND parent_column_id = (
  select column_id 
  from sys.columns 
  where object_id = object_id('TABLE_NAME')
  and name = 'COLUMN_NAME'
)
-- create alter table command as string and run it
set @sql = N'alter table TABLE_NAME drop constraint ' + @default
exec sp_executesql @sql
-- now we can alter column
ALTER TABLE [TABLE_NAME]
ALTER COLUMN COLUMN_NAME datetime -- here you can have any datatype you want of course
-- last step, we need to recreate constraint
-- DEFAULT getdate() is just for example, you can have any constraint you need of course
ALTER TABLE [TABLE_NAME]
ADD CONSTRAINT [YOUR_CONSTRAINT_NAME] DEFAULT getdate() For COLUMN_NAME 

That's it. If you don't know default constraint name you haven't any shorter way how to do it.

Tweet