asked
cannot drop and recreate foreign key
Hi all, i've gotten into a bit of a mess. I'm trying to recreate some id's that i deleted without realising this has impacted someone else. To do this i need to drop foreign keys set identity insert on insert recreate foreign keys set identity insert off. I'm not quite sure what's happened but i've gotten into the situation where I CANNOT RECREATE MY FOREIGN KEY and it is driving me insane. Please if this stupid error means anything to anyone let me know! ALTER TABLE dbo.Issuer DROP CONSTRAINT [FK_Issuer_AgentPrompt] Msg 3728, Level 16, State 1, Line 1 'FK_Issuer_AgentPrompt' is not a constraint. Msg 3727, Level 16, State 0, Line 1 Could not drop constraint. See previous errors. ALTER TABLE Issuer ADD CONSTRAINT [FK_Issuer_AgentPrompt] FOREIGN KEY ([AgentPromptID]) REFERENCES [dbo].[AgentPrompt] ([AgentPromptID]) Msg 547, Level 16, State 0, Line 1 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Issuer_AgentPrompt". The conflict occurred in database "CRM", table "dbo.AgentPrompt", column 'AgentPromptID'. I've tried everything dropping table recreating etc.!
Does select * from sys.objects where name ='FK_Issuer_AgentPrompt' return anything?
can you post the DDLs of the 2 tables, just to make sure theres no strange goings on.....
I've found the problem. When I originally dropped the constraint and inserted the records i inserted with an agentpromptid of '0'. The ID's start at 1 in the agentprompt table. so when i tried to recreate the constraint of course it wouldn't do it because there is no ID of 0 in the agentprompt table! Doh. Thanks for your help anyway guys...
Are you sure [`FK_Issuer_AgentPrompt`] is a constraint? check: select * from sys.foreign_keys where name = 'FK_Issuer_AgentPrompt'
i think your foreign key is not created in the first place. As it can't be created due to the data issue. You must have value in AgentPromptID that does not exists in AgentPrompt. Verify your data by running the following query select * from dbo.Issuer i where not exists ( select * from AgentPrompt x where x.AgentPromptID = i.AgentPromptID )
First of all, you need to check the existence of the object before dropping it to avoid the error. So the first error could be avioded by executing the following IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Issuer_AgentPrompt]') AND parent_object_id = OBJECT_ID(N'[dbo].[Issuer]')) ALTER TABLE [dbo].[Issuer] DROP CONSTRAINT [FK_Issuer_AgentPrompt] The second error states that there is referential integrity problem with your data which means your foreign key was created with the option WITH NOCHECK. You can again create your foreign key with this option. i.e. ALTER TABLE Issuer WITH NOCHECK ADD CONSTRAINT [FK_Issuer_AgentPrompt] FOREIGN KEY ([AgentPromptID]) REFERENCES [dbo].[AgentPrompt] ([AgentPromptID])
8 People are following this question.
