dont know why i get this relationship error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

dont know why i get this relationship error

Author

Topic

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-08-18 : 04:21:13

Hi everyone, how are you all?

I get the following error when i try to create a relationship between two tables:

'tblProductTypes' table saved successfully
'tblProducts2Displays2Stores' table
- Unable to create relationship 'FK_tblProducts2Displays2Stores_tblProductTypes'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_tblProducts2Displays2Stores_tblProductTypes". The conflict occurred in database "intranet", table "dbo.tblProductTypes", column 'productTypesID'.

..but i dont understand why? The datatypes are both the same, so surely this should not happen?

Any help would be great

Thanks and regards
MG

Sachin.Nand

2937 Posts

Posted - 2009-08-18 : 05:22:43

It is because u are tryin to create the relationship where the existing data conflicts the definition of the relationship you r tryin to create.

PBUH

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-08-18 : 06:26:02

Here are my three tables, but i cant work out what is wrong:

3 tables are, tblproducts2displays2stores (1), tblproductTypes (2) and tblDisplayTypes (3)

The DDL scripts are as follows:

(1)

USE [intranet]
GO
/****** Object: Table [dbo].[tblProducts2Displays2Stores] Script Date: 08/18/2009 10:46:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblProducts2Displays2Stores](
[Products2Displays2StoresID] [int] IDENTITY(1,1) NOT NULL,
[fk_storeID] [int] NULL,
[fk_productTypesID] [int] NULL,
[noOfWindow] [int] NULL,
[gondola] [int] NULL,
[canon] [int] NULL,
[sony] [int] NULL,
[nikon] [int] NULL,
[olympus] [int] NULL,
[camcorders] [int] NULL,
[lenses] [int] NULL,
[binoculars] [int] NULL,
[printerDisplays] [int] NULL,
[memoryCard] [int] NULL,
[liveMemory] [int] NULL,
[filters] [int] NULL,
[cameraAccessories] [int] NULL,
[tripods] [int] NULL,
[darkroom] [int] NULL,
[studioLighting] [int] NULL,
[digitalPaper] [int] NULL,
[batteryDisplays] [int] NULL,
[gadetBagDisplay] [int] NULL,
[digitalBag] [int] NULL,
[digitalPicture] [int] NULL,
[recordableMedia] [int] NULL,
[sonyHDDisplay] [int] NULL,
[canonHDDisplay] [int] NULL,
[busStopSign] [int] NULL,
[energizerLithiumDisplay] [int] NULL,
[canonHX1CounterStand] [int] NULL,
[storeBand] [int] NULL,
[penPlinth] [int] NULL,
[giottoTripodStand] [int] NULL,
[batteryCharger] [int] NULL,
CONSTRAINT [PK_tblProducts2Displays2Stores] PRIMARY KEY CLUSTERED
(
[Products2Displays2StoresID] 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].[tblProducts2Displays2Stores] WITH CHECK ADD CONSTRAINT [FK_tblProducts2Displays2Stores_tblStores] FOREIGN KEY([fk_storeID])
REFERENCES [dbo].[tblstores] ([storeID])
GO
ALTER TABLE [dbo].[tblProducts2Displays2Stores] CHECK CONSTRAINT [FK_tblProducts2Displays2Stores_tblStores]

(2)

USE [intranet]
GO
/****** Object: Table [dbo].[tblProductTypes] Script Date: 08/18/2009 10:49:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblProductTypes](
[productTypesID] [int] IDENTITY(1,1) NOT NULL,
[productName] [varchar](50) NULL,
CONSTRAINT [PK_tblProductTypes] PRIMARY KEY CLUSTERED
(
[productTypesID] 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
SET ANSI_PADDING OFF

(3)

USE [intranet]
GO
/****** Object: Table [dbo].[tblDisplayTypes] Script Date: 08/18/2009 10:50:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblDisplayTypes](
[displayTypesID] [int] IDENTITY(1,1) NOT NULL,
[displayTypesName] [varchar](50) NULL,
CONSTRAINT [PK_tblDisplayTypes] PRIMARY KEY CLUSTERED
(
[displayTypesID] 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
SET ANSI_PADDING OFF

Display types is to link to prodcutTypes, and product types to products2displays2stores. There is a fourth table but that seems to be ok in its relationship.

Really cant work out whats wrong.

Any help would be great.

Regards
MG

Sachin.Nand

2937 Posts

Posted - 2009-08-18 : 06:29:52

Do the table have data in it?

PBUH

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-08-18 : 06:41:42

No, I have only just created the tables and thought i shoul dbuild the relationship before i populate it. Could htis be the reason?

Regards
MG

Sachin.Nand

2937 Posts

Posted - 2009-08-18 : 06:58:38

No that is not the reason.
But when I tested on my side with your script it is working properly.

PBUH

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-08-18 : 07:03:41

Really, i was on another forum and the guy said that a foreign key is dulplicated or something, thats why its not working??

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-08-18 : 07:04:16

The relationships are all ok?

quote:
Originally posted by Idera

No that is not the reason.
But when I tested on my side with your script it is working properly.

PBUH

Sachin.Nand

2937 Posts

Posted - 2009-08-18 : 07:11:00

Yes they r ok except for the
ALTER TABLE [dbo].[tblProducts2Displays2Stores] WITH CHECK ADD CONSTRAINT [FK_tblProducts2Displays2Stores_tblStores] FOREIGN KEY([fk_storeID])
REFERENCES [dbo].[tblstores] ([storeID])
GO
ALTER TABLE [dbo].[tblProducts2Displays2Stores] CHECK CONSTRAINT [FK_tblProducts2Displays2Stores_tblStores]
part because there is no definition for tblstores in ur script.

PBUH

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-08-18 : 07:57:16

the tblstores is another table that i'm joining on to. How do i rectify this problem, perhaps if i delete the relationships and start again?

Any ideas would be great

Regards
MG

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-08-18 : 09:38:37

Hi all,

I have amended the relationships and I'm no longer getting the error.

Thanks to everyone that replied.

Kind regards
MG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources