alter column - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

alter column

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-05-19 : 07:29:09

hi,
i am having a table
CREATE TABLE [dbo].[EMREchartCategoriesLkup] (
[ECHART_CATEGORY_ID] [NUMERIC](20,0) NOT NULL,
[ECHART_CATEGORY_NAME] [VARCHAR](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GROUP_ID] [NUMERIC](20,0) NULL,
[ECHART_STATUS] [BIT] NULL)
ON [PRIMARY]

GO

and having constraints
1)ALTER TABLE [dbo].[EMREchartCategoriesLkup] WITH NOCHECK ADD
CONSTRAINT [PK_echartcategorieslkup] PRIMARY KEY CLUSTERED
(
[ECHART_CATEGORY_ID]
) ON [PRIMARY]
GO

2)ALTER TABLE [dbo].[EMREchartCategoriesLkup] ADD
CONSTRAINT [DF_EMREchartCategoriesLkup_ECHART_STATUS] DEFAULT (1) FOR [ECHART_STATUS]
GO

i want to change datatype from bit to numeric
when i use this
alter table EMREchartCategoriesLkup alter column ECHART_STATUS numeric(10,0)

error message:

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

how can i change the data type from bit to numeric

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-19 : 07:49:19

You have to drop the default on that column first, change the data type, then add the default constraint back.