CODEWITHSUNDEEP
sql-servert-sqlforeign-keysconstraints
afri
afri

Reputation: 49

How can Reference Foreign key to Multiple tables?

I am trying to create a BOM structure i have 6 product tables which contains different attributes and a BOMHEADER and BOMDETAIL tables. Before creating the BOM structure i like to Validate or check the existence of the bomitem in either of the six tables. So i tried to creating BOMHEADER field as shown below using multiple constraints, but i get the following error message "The INSERT Statement conflicted with the FOREIGN KEY constraint

What is the best way to resolve the issue.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[BOMHEAD](
    [bomItem] [int] NOT NULL,
    [bomRev] [nvarchar](6) NOT NULL,
    [rollup] [bit] NULL,

 CONSTRAINT [PK_BOMHEAD_KEY_0] PRIMARY KEY CLUSTERED 
(
    [bomItem] ASC,
    [bomRev] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[BOMHEAD]  WITH CHECK ADD FOREIGN KEY([bomItem])
REFERENCES [dbo].[parts] ([itemId])
GO

ALTER TABLE [dbo].[BOMHEAD]  WITH CHECK ADD FOREIGN KEY([bomItem])
REFERENCES [dbo].[Tires] ([titemId])
GO

ALTER TABLE [dbo].[BOMHEAD]  WITH CHECK ADD FOREIGN KEY([bomItem])
REFERENCES [dbo].[Discs] ([itemId])
GO

ALTER TABLE [dbo].[BOMHEAD]  WITH CHECK ADD FOREIGN KEY([bomItem])
REFERENCES [dbo].[Rims] ([itemId])
GO

ALTER TABLE [dbo].[BOMHEAD]  WITH CHECK ADD FOREIGN KEY([bomItem])
REFERENCES [dbo].[Wheel] ([wheelItemId])
GO

ALTER TABLE [dbo].[BOMHEAD]  WITH CHECK ADD FOREIGN KEY([bomItem])
REFERENCES [dbo].[Assemblies] ([itemId])
GO

Upvotes: 0

Views: 108

Answers (2)

Persk
Persk

Reputation: 669

The structure you post is not check that bomItem is exists in any of that given table but it is required that bomItem must exists in ALL TABLES.

You should do it the other way round by making BOMHEAD to primary key table and set fk of other table to refer pk on BOMHEAD.

This way it will guarantee that every other part table will have BOMHEAD.

The way you did is to guarantee that BOMHEAD will have every other parts.

But if you insist that bomItem need to check for existence in either of the six tables(maybe to prevent unwanted reference from other table?),You can't use fk what you need is check constrain with user defined function or create association table which maintain the relation between BOMHEAD and others.

Upvotes: 1

nimdil
nimdil

Reputation: 1381

You should have general table with BOMs that are referenced via Foreign Key by all these tables.

Upvotes: 0

Related Questions