none
The relationship 'xxx' has columns that are not part of the key of the table on the primary side of the relationship. RRS feed

  • Question

  • I create two tables Product and ProductCategory. And add a FK to the two table. Please look at below picture.

    The primary key of Product is : EffectiveStartTime and ProductId.

    The primary key of ProdcutCategory is: EffectiveStartTime and ProductCategoryId

    Then I make ProductCategoryId to a unique key.

    Drag the ProductCategroryId from Product table to ProductCategory to create its relationship, and the result is like upper picture.

    I did test input data to the two tables, and they works well with the FK constraint.

    My issue is: after I create a EF data model file from this database, the FK is missing, and there is a warning in the edmx file:

     <!--Errors Found During Generation:
          warning 6035: The relationship 'FK_Product_ProductCategory' has columns that are not part of the key of the table on the primary side of the relationship. The relationship was excluded.
          -->

    I copy the two tables from our company's Erwin project, so i think this kind design is useful, also i tried to insert data to tables, and works well. How can I get this kind relationship information in EF model file(edmx)? Why not EF model support this kind relationship?

    Tuesday, November 12, 2013 7:04 AM

All replies

  • Hello,

    >>How can I get this kind relationship information in EF model file(edmx)?

    All the relationship information in EF model file will in the association section if we open it with XML like below:

    >> Why not EF model support this kind relationship

    Since I do not know the detail table structure, I made a sample with images like below:

    CREATE TABLE [dbo].[ProdcutCategory] (
    
        [EffectiveStartTime]  DATETIME      NOT NULL,
    
        [ProductCategoryId]   INT           NOT NULL,
    
        [ProductCategoryName] NVARCHAR (50) NOT NULL,
    
        CONSTRAINT [PK_ProdcutCategory] PRIMARY KEY CLUSTERED ([ProductCategoryId] ASC, [EffectiveStartTime] ASC)
    
    );
    
    
    CREATE TABLE [dbo].[Product] (
    
        [EffectiveStartTime] DATETIME      NOT NULL,
    
        [ProductId]          INT           NOT NULL,
    
        [ProductName]        NVARCHAR (50) NOT NULL,
    
        [ProductCategoryId]  INT           NOT NULL,
    
        CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED ([EffectiveStartTime], [ProductId]),
    
        CONSTRAINT [FK_P_P] FOREIGN KEY ([ProductCategoryId], [EffectiveStartTime]) REFERENCES [dbo].[ProdcutCategory] ([ProductCategoryId], [EffectiveStartTime]) ON DELETE CASCADE
    
    );
    

    When I update them from database to designer, it worked fine.

    There is no warning.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, November 13, 2013 5:44 AM
    Moderator
  • Hello, I am missing a key point step to tell you. 

    When i create the FK via drag action in SQL management studio, i only drag the ProductCategoryId! Our design is

    only use this column and missing EffectiveStartTime.

    Friday, November 15, 2013 9:16 AM
  • Could you please share the desinger codes regading the tables after creating the FK relationship? 


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, November 18, 2013 8:26 AM
    Moderator
  • OK, it looks visual studio remove this warning after i update to the no.4 package. But there still a problem.

    The script is below

    USE [Test]
    GO

    /****** Object:  Table [dbo].[ProductCategory]    Script Date: 11/25/2013 17:34:59 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Product]') AND type in (N'U'))
    DROP TABLE [dbo].[Product]
    GO

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProductCategory]') AND type in (N'U'))
    DROP TABLE [dbo].[ProductCategory]
    GO


    CREATE TABLE [dbo].[ProductCategory](
    [EffectiveStartTime] [datetime] NOT NULL,
    [ProductCategoryId] [int] NOT NULL,
    [ProductCategoryName] [nchar](10) NULL,
     CONSTRAINT [PK_ProductCategory] PRIMARY KEY CLUSTERED 
    (
    [EffectiveStartTime] ASC,
    [ProductCategoryId] 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

    IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ProductCategory]') AND name = N'IX_ProductCategory')
    DROP INDEX [IX_ProductCategory] ON [dbo].[ProductCategory] WITH ( ONLINE = OFF )
    GO


    /****** Object:  Index [IX_ProductCategory]    Script Date: 11/25/2013 17:54:09 ******/
    CREATE UNIQUE NONCLUSTERED INDEX [IX_ProductCategory] ON [dbo].[ProductCategory] 
    (
    [ProductCategoryId] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[Product](
    [EffectiveStartTime] [datetime] NOT NULL,
    [ProductId] [int] NOT NULL,
    [ProductName] [nchar](10) NULL,
    [ProductCategoryId] [int] NULL,
    [ProductCategoryEffectiveStartTime] [datetime] NULL,
     CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
    (
    [EffectiveStartTime] ASC,
    [ProductId] 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].[Product]  WITH CHECK ADD  CONSTRAINT [FK_Product_ProductCategory] FOREIGN KEY([ProductCategoryId])
    REFERENCES [dbo].[ProductCategory] ([ProductCategoryId])
    GO

    ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_ProductCategory]
    GO

    =============================

    There is no any relationship(Association node).

    ===========================================

    And if the FK is create like:

    ALTER TABLE [dbo].[Product]  WITH CHECK ADD  CONSTRAINT [FK_Product_ProductCategory] FOREIGN KEY([ProductCategoryEffectiveStartTime],[ProductCategoryId])
    REFERENCES [dbo].[ProductCategory] ([EffectiveStartTime],[ProductCategoryId])
    GO

    Then the EF designer can load it correct.

    And the edmx contains the relationship informaiton:

      <Association Name="FK_Product_ProductCategory">
              <End Role="ProductCategory" Type="Model2.Store.ProductCategory" Multiplicity="0..1" />
              <End Role="Product" Type="Model2.Store.Product" Multiplicity="*" />
              <ReferentialConstraint>
                <Principal Role="ProductCategory">
                  <PropertyRef Name="EffectiveStartTime" />
                  <PropertyRef Name="ProductCategoryId" />
                </Principal>
                <Dependent Role="Product">
                  <PropertyRef Name="ProductCategoryEffectiveStartTime" />
                  <PropertyRef Name="ProductCategoryId" />
                </Dependent>
              </ReferentialConstraint>
            </Association>

    --------------------


    • Edited by Klaus Zou Monday, November 25, 2013 10:15 AM
    Monday, November 25, 2013 10:13 AM
  • Yes, the second way is correct.

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, November 26, 2013 7:43 AM
    Moderator
  • I have this erro message when I was try to update the model from database. The problem was that I had a table with two primary key and I was doing the relationship only with one of this primary key.
    Thursday, April 5, 2018 7:14 PM