I try to drop a Non Clustered index on a column and get the following
Message
Server: Msg 3723, Level 16, State 6
An Explicit DROP INDEX is not allowed on index
'dbo.Jobs.nci_Jobs_StartTime'. It is being used for FOREIGN KEY constraint
Enforcement.
Ummm...No It's not...!
I scripted the entire DB and searched for any foreign key referencing this
column and it simply does not exist in the script.
I just wanna drop the index named nci_Jobs_StartTime (is that so wrong?)
Here is the Jobs Table Script:
CREATE TABLE [dbo].[Jobs] (
[JobUID] [uniqueidentifier] NOT NULL ,
[ParentJobUID] [uniqueidentifier] NULL ,
[JobTypeID] [tinyint] NOT NULL ,
[JobStatusID] [tinyint] NOT NULL ,
[CNCSystemID] [tinyint] NULL ,
[JobStep] [tinyint] NULL ,
[HasSubJobs] [bit] NOT NULL ,
[CurrentSubJobStep] [tinyint] NULL ,
[JobStartTime] [datetime] NULL ,
[JobStopTime] [datetime] NULL ,
[JobPickedUpAtTime] [datetime] NULL ,
[JobPickedUpByMachine] [varchar] (50) COLLATE SQL_Latin1_General_CP1
_CI_AS
NULL ,
[JobData] [varbinary] (4096) NULL ,
[JobRawData] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Retry] [tinyint] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Jobs] WITH NOCHECK ADD
CONSTRAINT [PK_Jobs] PRIMARY KEY CLUSTERED
(
[JobUID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Jobs] WITH NOCHECK ADD
CONSTRAINT [DF__Jobs__Retry__4AB81AF0] DEFAULT (0) FOR [Retry]
GO
CREATE INDEX [nci_Jobs_StartTime] ON [dbo].[Jobs]([JobStart
Time]) ON
[PRIMARY]
GO
ALTER TABLE [dbo].[Jobs] ADD
CONSTRAINT [FK_Jobs_CNCSystem] FOREIGN KEY
(
[CNCSystemID]
) REFERENCES [dbo].[CNCSystem] (
[CNCSystemID]
),
CONSTRAINT [FK_Jobs_Jobs] FOREIGN KEY
(
[ParentJobUID]
) REFERENCES [dbo].[Jobs] (
[JobUID]
),
CONSTRAINT [FK_Jobs_JobStatus] FOREIGN KEY
(
[JobStatusID]
) REFERENCES [dbo].[JobStatus] (
[JobStatusID]
),
CONSTRAINT [FK_Jobs_JobType] FOREIGN KEY
(
[JobTypeID]
) REFERENCES [dbo].[JobType] (
[JobTypeID]
)
GOYou have to drop foreign key, below is the syntax:
ALTER TABLE tablename
DROP CONSTRAINT foreign key
Hope this will help you|||there is no foreign key.
that is the problem.
GAJ
"Tony-ICW Group" <tmangahas@.icwgroup.com> wrote in message
news:9FBD7415-0E78-4968-B205-ADD80526B5F2@.microsoft.com...
> You have to drop foreign key, below is the syntax:
> ALTER TABLE tablename
> DROP CONSTRAINT foreign key
> Hope this will help you|||Strange... What does sp_helpconstraint say?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:O1BKuojBEHA.3748@.tk2msftngp13.phx.gbl...
> there is no foreign key.
> that is the problem.
>
> GAJ
>
> "Tony-ICW Group" <tmangahas@.icwgroup.com> wrote in message
> news:9FBD7415-0E78-4968-B205-ADD80526B5F2@.microsoft.com...
>|||does not list any FKEY on the JobStartTime Column.
I look in SQL
I look in Toad
I look everywhere
no sign of any FKEY on jobs.JobStartTime
The weird thing is, I can "Change" the index definition. I modified the
index to use a different column, etc. No issues.
I just cant drop the stupid thing.
GAJ|||Strange... Can you produce a repro? If not, I'd suspect some type of
corruption somewhere. Try DBCC CHECKDB and DBCC CHECKCATALOG.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uJ$OTasBEHA.3524@.TK2MSFTNGP10.phx.gbl...
> does not list any FKEY on the JobStartTime Column.
> I look in SQL
> I look in Toad
> I look everywhere
> no sign of any FKEY on jobs.JobStartTime
> The weird thing is, I can "Change" the index definition. I modified the
> index to use a different column, etc. No issues.
> I just cant drop the stupid thing.
>
> GAJ
>|||what's a "Repro"
I ran dbcc checkdb and all seems well.
this is very very weird
GAJ|||"Repro" = Code with which we can reproduce the error.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uCPqr9sBEHA.3064@.tk2msftngp13.phx.gbl...
> what's a "Repro"
> I ran dbcc checkdb and all seems well.
> this is very very weird
>
> GAJ
>|||the ddl is in my first post.
I wish there was something else I could give you, I'd do it in a heartbeat.
Thanks for the feedback up to now anyway
GAJ|||I'm sorry, but that is not a complete repro. There are tables that you
reference in your FK constraint which are lacking. The point of a repro is
for use to reproduce the same error message. In this case, I didn't find
anything obvious, but sometime you see the problem if you have it in front
of you. Hence the request for a repro. If you can't produce such, it might
be time for opening a case with MS...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uSIEoBvBEHA.3344@.tk2msftngp13.phx.gbl...
> the ddl is in my first post.
> I wish there was something else I could give you, I'd do it in a
heartbeat.
> Thanks for the feedback up to now anyway
>
> GAJ
>
sql
