question

jhowe avatar image
jhowe 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.!
sql-server-2008sql-server-2008-r2
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Does select * from sys.objects where name ='FK_Issuer_AgentPrompt' return anything?
0 Likes 0 ·
no. I've just recreated the symptoms it seems that once i drop it, it removes it, but then i cannot create it. The foreign key references a primary key in the agent prompt table but i wouldn't have thought this mattered, if i drop it i should be able to recreate it!?!?
0 Likes 0 ·
can you post the DDLs of the 2 tables, just to make sure theres no strange goings on.....
0 Likes 0 ·
jhowe avatar image
jhowe answered
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...
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wilfred van Dijk avatar image
Wilfred van Dijk answered
Are you sure [`FK_Issuer_AgentPrompt`] is a constraint? check: select * from sys.foreign_keys where name = 'FK_Issuer_AgentPrompt'
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Squirrel avatar image
Squirrel answered
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 )
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

robbin avatar image
robbin answered
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])
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.