Altering a Column Which has a Default Constraint
- by Dinesh Asanka
Setting up a default column is a common task for developers.
But, are we naming those default constraints explicitly?
In the below table creation, for the column, sys_DateTime the
default value Getdate() will be allocated.
CREATE TABLE SampleTable
(ID int identity(1,1),
Sys_DateTime Datetime DEFAULT getdate()
)
We can check the relevant information from the system
catalogs from following query.
SELECT
sc.name TableName,
dc.name DefaultName,
dc.definition,
OBJECT_NAME(dc.parent_object_id) TableName,
dc.is_system_named
FROM sys.default_constraints dc
INNER JOIN sys.columns sc
ON
dc.parent_object_id = sc.object_id
AND
dc.parent_column_id = sc.column_id
and results would be:
Most of the above columns are self-explanatory. The last
column, is_system_named, is to identify whether the default name was given by
the system. As you know, in the above case, since we didn’t provide any default
name, the system will generate a default name for you.
But the problem with these names is that they can differ from
environment to environment. If example if I create this table in different
table the default name could be DF__SampleTab__Sys_D__7E6CC920
Now let us create another default and explicitly name it:
CREATE TABLE SampleTable2
(ID int identity(1,1),
Sys_DateTime Datetime
)
ALTER
TABLE SampleTable2
ADD CONSTRAINT DF_sys_DateTime_Getdate
DEFAULT( Getdate()) FOR Sys_DateTime
If we run the previous query again we will be returned the
below output.
And you can see that last created default name has 0 for
is_system_named.
Now let us say I want to change the data type of the sys_DateTime
column to something else:
ALTER TABLE SampleTable2 ALTER COLUMN
Sys_DateTime Date
This will generate the below error:
Msg 5074, Level 16, State 1, Line 1
The object ‘DF_sys_DateTime_Getdate’ is dependent on column
‘Sys_DateTime’.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN Sys_DateTime failed because one or more
objects access this column.
This means, you need to drop the default constraint before
altering it:
ALTER TABLE [dbo].[SampleTable2]
DROP CONSTRAINT [DF_sys_DateTime_Getdate]
ALTER TABLE SampleTable2 ALTER COLUMN Sys_DateTime Date
ALTER TABLE [dbo].[SampleTable2]
ADD CONSTRAINT [DF_sys_DateTime_Getdate]
DEFAULT (getdate()) FOR [Sys_DateTime]
If you have a system named default constraint that can differ
from environment to environment and so you cannot drop it as before, you can
use the below code template:
DECLARE @defaultname VARCHAR(255)
DECLARE @executesql VARCHAR(1000)
SELECT @defaultname = dc.name
FROM sys.default_constraints dc
INNER JOIN sys.columns sc
ON dc.parent_object_id = sc.object_id
AND dc.parent_column_id = sc.column_id
WHERE OBJECT_NAME (parent_object_id) = 'SampleTable'
AND sc.name ='Sys_DateTime'
SET @executesql = 'ALTER TABLE SampleTable DROP CONSTRAINT ' + @defaultname
EXEC( @executesql)
ALTER TABLE SampleTable ALTER COLUMN Sys_DateTime Date
ALTER TABLE [dbo].[SampleTable] ADD DEFAULT (Getdate()) FOR [Sys_DateTime]
