constraint supposedly disabled, but still won't allow alter

Question

constraint supposedly disabled, but still won't allow alter

Sailor

SSCertifiable

Points: 5246
More actions
October 3, 2012 at 1:46 pm

#268745

I'm trying to alter a column after I disabled the constraints, but it still says the constraint is there. What gives?
Table
CREATE TABLE [dbo].[DASHREPORT](
[REPORT_KEY] [char](60) NULL,
[BUS_DIV] [char](20) NULL,
[UPDATE_DT] [char](10) NULL,
[UPDATE_TM] [char](8) NULL,
[ARCH_FLAG] [char](1) NULL,
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[DASHREPORT] ADD CONSTRAINT [DF_DASHREPORT_ARCH_FLAG] DEFAULT ('') FOR [ARCH_FLAG]
GO
ALTER TABLE [dbo].[DASHREPORT] ADD CONSTRAINT [DF_DASHREPORT_WEB_PICK_FLAG] DEFAULT ('') FOR [WEB_PICK_FLAG]
GO
exec sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL PRINT'? constraint altered'"
ALTER TABLE [DASHREPORT] ALTER COLUMN [ARCH_FLAG] nchar(1) NULL
Output:
[dbo].[BWDASHREPORT] constraint altered
Msg 5074, Level 16, State 1, Line 1
The object 'DF_DASHREPORT_ARCH_FLAG' is dependent on column 'ARCH_FLAG'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN ARCH_FLAG failed because one or more objects access this column.
I wonder if it's only disabling only of the one constraints? I even tried ALTER TABLE [DASHREPORT] NOCHECK CONSTRAINT ALL , but still same error. Is there an alternate method to disable constraints?
This is version Microsoft SQL Server 2008 (SP2) - 10.0.4332.0 (X64) May 2 2012 20:39:13 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

Disabled or not, it's still a dependency. The error messages in 2k5 were a little more useful than in 2k8 but do have the same meaning...

[font="Courier New"]Msg 5074, Level 16, State 1, Line 4

The object 'DF_DASHREPORT_ARCH_FLAG' is dependent on column 'ARCH_FLAG'.

Msg 4922, Level 16, State 9, Line 4

ALTER TABLE ALTER COLUMN ARCH_FLAG failed because one or more objects access this column.[/font]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

OTF SSCertifiable Points: 6104 More actions · Answer 2

I think "Disabling" Constraints is only relevant for DML, for example, you could disable a Constraint if

you sought to insert data into a table, bypassing checks.

If, however, you wish to change the schema of the table (DDL) then you will have to take the Constraints

into consideration regardless of their "enabled" state.