Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135

    Unanswered: Why do i get this relationship error?

    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_tblProductType s". 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

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can you post the full DDL creation scripts? We can't really say for sure without knowing what the definitions of the objects are. It sounds like the foreign key is defined already.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi flump, you'll have to forgive me, im new to the world of SQL and dont know where and how I'd begin to do that?

    I assume the DDL scripts are held in a place I can retrieve the info?

    Searching web to work out how to do this.

    Any pointers will be great, will check back soon

    regards
    MG

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi, thanks for that, not as diificult as i thought it would be, lol. ok will post them shortly....

  6. #6
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Ok, so there are 3 tables, they 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.

    Any help would be great.

    Reegards
    MG

    Quote Originally Posted by pootle flump

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - search what you posted for "FK_tblProducts2Displays2Stores_tblStores". Tell me then what you think the problem is now.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    this is interesting too (notably point 5)
    http://www.intelligententerprise.com...PCKH4ATMY32JVN
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    You'll have to forgive me, im really confused, is it becuase its not there or something, or that name doesn't exist? i really dont know. What you mean by point 5 - d'you mean thread 5?

    Regards
    MG

  10. #10
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Oh, you think that the names that have been given are out dated?? as i say, really new to this world, and ive been following a standard set by my former manager. But point taken, will keep it in mind for furture development. But im guessing this is not the reason the relationships is not working??

    regards
    MG

  11. #11
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Will it be a problem if two of the tables do not have data in them?

    MG

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Post #8 is some background info.
    Yes, your manager has used a rather "quaint" convention.

    regarding the specifics, if you followed the instructions in #7 you would find this:
    Code:
    ALTER TABLE [dbo].[tblProducts2Displays2Stores] WITH CHECK ADD CONSTRAINT [FK_tblProducts2Displays2Stores_tblStores] FOREIGN KEY([fk_storeID])
    REFERENCES [dbo].[tblstores] ([storeID])
    The point is - the foreign key already exists. Either it existed already, or you tried to create it twice and the second time failed.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi, I think he became farmiliar with doing things in a set fashion, and as i had no idea how to do it, i followed that way. But you have opened my eyes on that matter.

    As for the other issue, I have just removed all the links and will start again - small steps i suppose. I think it may have been the second thing you mention. As these three tables are all new, so i must have started to create it and the second time it failed.

    I assume its best just to delete the links and do it again?

    Regards
    MG

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Personally I do all this sort of stuff via drop\ create scripts (like what you posted). It makes things repeatable, auditable, possible to check into source control & versionise(??versionise??), moveable, quick deployment etc.

    I use the GUI for as little as I possibly can. My recommendation would be to get used to scripting this sort of stuff. YMV.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    You're probably right, but due to my lack of knowledge I simply dont know the syntax, what needs to be written where etc.

    I'd love to get used to scripting, I think it makes people better at their jobs, but, Im fresh in a new job and currently working on something that managment want "yesterday".

    Im learning on the fly, so these forums, and you wonderful people are my best friends. Need to complete this section by end of Sep, and once done, I'll have to read more into T-SQL, as well as ASP, VBScript et al.

    I assume I can use the design view and do it through that in the intrim?

    Keeping my head up and struggling on flump, lol

    Kind regards
    MG

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •