January 18, 2012 at 10:34 am
Hi All,
I am getting an error when I try to create a foreign key.
Scenario:
I have tables A, B and C. Table A has a prim key on Aid column and Table B has a prim key on Bid column and Table C has a prim key on Cid column.
Relationship:
Aid is the foreign key for tables B and C and there is a relationship between A and B and another relation between A and C.
Issue:
Table C has a column Bid and I want to create a foreignkey relationship between B and C. and below is my sql
ALTER TABLE C
ADD Constraint FK_C_B FOREIGN KEY (BId) REFERENCES B(BId)
Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_C_B". The conflict occurred in database "Test", table "dbo.B", column 'BId'.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
January 18, 2012 at 10:35 am
Will be thankful if you can shed some light on me.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
January 18, 2012 at 10:54 am
I believe you may be creating a FK on itself, BId could be ambiguous.
Also ensure B.BID has an associated primary key constraint.
You can use NOCHECK to skip constraint checking...
Try:
ALTER TABLE C
ADD Constraint FK_C_B FOREIGN KEY (BId) REFERENCES B(BId)
WITH NOCHECK
And can you post the table definition too, and any current constraints on the table - and whether any entries exist in the table.
Thanks.
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
January 18, 2012 at 11:54 am
Table A was:
CREATE TABLE [dbo].[Workspaces](
[WorkspaceId] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[IsMultiLanguage] [bit] NOT NULL,
[IsMultiUseCase] [bit] NOT NULL,
[IsAllowAlternateMapStrings] [bit] NOT NULL,
[AccountId] [uniqueidentifier] NULL,
CONSTRAINT [PK_Workspaces] PRIMARY KEY CLUSTERED
(
[WorkspaceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Table B was:
USE [PPMOnlineWebSiteDB]
GO
/****** Object: Table [dbo].[FlexDefs] Script Date: 01/18/2012 13:55:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FlexDefs](
[FlexDefId] [uniqueidentifier] NOT NULL,
[WorkspaceId] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[DataTypeModifierId] [uniqueidentifier] NULL,
[FlexListModeId] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_FlexDefs] PRIMARY KEY CLUSTERED
(
[FlexDefId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[FlexDefs] WITH CHECK ADD CONSTRAINT [FK_FlexDef_DataTypeModifier] FOREIGN KEY([DataTypeModifierId])
REFERENCES [dbo].[DataTypeModifiers] ([DataTypeModifierId])
GO
ALTER TABLE [dbo].[FlexDefs] CHECK CONSTRAINT [FK_FlexDef_DataTypeModifier]
GO
ALTER TABLE [dbo].[FlexDefs] WITH CHECK ADD CONSTRAINT [FK_FlexDef_FlexListMode] FOREIGN KEY([FlexListModeId])
REFERENCES [dbo].[FlexListModes] ([FlexListModeId])
GO
ALTER TABLE [dbo].[FlexDefs] CHECK CONSTRAINT [FK_FlexDef_FlexListMode]
GO
ALTER TABLE [dbo].[FlexDefs] WITH CHECK ADD CONSTRAINT [FK_FlexDef_Workspace] FOREIGN KEY([WorkspaceId])
REFERENCES [dbo].[Workspaces] ([WorkspaceId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[FlexDefs] CHECK CONSTRAINT [FK_FlexDef_Workspace]
GO
Table C was:
USE [PPMOnlineWebSiteDB]
GO
/****** Object: Table [dbo].[FlexDefUseCases] Script Date: 01/18/2012 13:55:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FlexDefUseCases](
[FlexDefUseCaseId] [uniqueidentifier] NOT NULL,
[FlexDefId] [uniqueidentifier] NOT NULL,
[WorkspaceId] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[AnnouncementMethodID] [uniqueidentifier] NOT NULL,
[VoiceID] [uniqueidentifier] NOT NULL,
[ScriptUseModeId] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_FlexDefUseCases] PRIMARY KEY CLUSTERED
(
[FlexDefUseCaseId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[FlexDefUseCases] WITH CHECK ADD CONSTRAINT [FK_FlexDefUseCase_Workspace] FOREIGN KEY([WorkspaceId])
REFERENCES [dbo].[Workspaces] ([WorkspaceId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[FlexDefUseCases] CHECK CONSTRAINT [FK_FlexDefUseCase_Workspace]
GO
ALTER TABLE [dbo].[FlexDefUseCases] WITH CHECK ADD CONSTRAINT [FK_FlexDefUseCases_AnnouncementMethod] FOREIGN KEY([AnnouncementMethodID])
REFERENCES [dbo].[AnnouncementMethods] ([AnnouncementMethodID])
GO
ALTER TABLE [dbo].[FlexDefUseCases] CHECK CONSTRAINT [FK_FlexDefUseCases_AnnouncementMethod]
GO
ALTER TABLE [dbo].[FlexDefUseCases] WITH CHECK ADD CONSTRAINT [FK_FlexDefUseCases_ScriptUseMode] FOREIGN KEY([ScriptUseModeId])
REFERENCES [dbo].[ScriptUseModes] ([ScriptUseModeId])
GO
ALTER TABLE [dbo].[FlexDefUseCases] CHECK CONSTRAINT [FK_FlexDefUseCases_ScriptUseMode]
GO
ALTER TABLE [dbo].[FlexDefUseCases] WITH CHECK ADD CONSTRAINT [FK_FlexDefUseCases_Voices] FOREIGN KEY([VoiceID])
REFERENCES [dbo].[Voices] ([VoiceID])
GO
ALTER TABLE [dbo].[FlexDefUseCases] CHECK CONSTRAINT [FK_FlexDefUseCases_Voices]
GO
Yes there is data in all the tables and I doubt that it could be the issue
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
January 18, 2012 at 12:16 pm
You have a value for BID in your C table that doesn't have a corresponding value for BID in your B table. The best way to resolve this is to add the necessary values for BID into the B table before creating your foreign key.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 18, 2012 at 7:05 pm
The issue is because of data in the tables. Identified the bad data and deleted it and created the foreign key and it got created without error.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply