locked
The columns in table do not match an existing primary key or unique constraint RRS feed

  • Question

  • Dear Sir:

    I made two tables as following:

    Table A: OrderID (int,PK),OrderName (Nvarchar(50)

    Table B: DetailID (int,PK),OrderID (int)

    When set foreign key between these two tables with OrderID, it always shows: "The columns in table B do not match an existing primary key or unique constraint"

    Would you please tell me how to solve this problem?

     

    Thanks

     

    Shell

    Wednesday, November 5, 2008 12:00 PM

All replies


  • Hi,

    I am assuming you are using SMO to do the mentioned actions.
    Can you please post the code snippet which is causing the issue.

    Regards,
    Alok Parmesh
    Wednesday, November 5, 2008 2:40 PM
    Answerer
  • Dear Alok:

    The details are as below:

    USE [BMPSystem]
    GO
    /****** Object:  Table [dbo].[Item]    Script Date: 11/06/2008 09:23:38 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Item](
     [ItemID] [int] IDENTITY(1,1) NOT NULL,
     [Item] [varchar](200) COLLATE Chinese_PRC_CI_AS NOT NULL,
     CONSTRAINT [FK_Item] PRIMARY KEY CLUSTERED
    (
     [ItemID] ASC
    )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    Alter Table [dbo].[Item] with check add constraint [FK_Item_LoginRight] Foreign key ([ItemID])
    References [dbo].[LoginRight] ([ItemID])
    Go
    Alter Table [dbo].[Item] check constraint [FK_Item_LoginRight]

     

    Thursday, November 6, 2008 1:33 AM

  • Hi,

    Can you post the Script of LoginRight table also.
    It seems to me there is a missing primary key or unique constraint in the referenced table.
    http://technet.microsoft.com/en-us/library/aa937349(SQL.80).aspx

    Regards,
    Alok Parmesh
    Thursday, November 6, 2008 9:04 AM
    Answerer
  • Dear Alok:

    The script of LoginRight table is as below:

    USE [BMPSystem]
    GO
    /****** Object:  Table [dbo].[LoginRight]    Script Date: 11/07/2008 06:31:43 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[LoginRight](
     [RightID] [int] IDENTITY(1,1) NOT NULL,
     [UserID] [int] NOT NULL,
     [UserName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
     [ItemID] [int] NOT NULL,
     [Item] [varchar](200) COLLATE Chinese_PRC_CI_AS NOT NULL,
     CONSTRAINT [PK_LoginRight] PRIMARY KEY CLUSTERED
    (
     [RightID] ASC
    )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF

     

    Shell

     

    Thursday, November 6, 2008 10:35 PM

  • Hi,

    The issue seems to arising from the fact that the itemID that you are trying to reference does not has a unique or primary key on it. What you need to do is to make it unique and then you will be able to reference it.

    CREATE TABLE [dbo].[LoginRight](
     [RightID] [int] IDENTITY(1,1) NOT NULL,
     [UserID] [int] NOT NULL,
     [UserName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
     [ItemID] [int] unique NOT NULL,
     [Item] [varchar](200) COLLATE Chinese_PRC_CI_AS NOT NULL,
     CONSTRAINT [PK_LoginRight] PRIMARY KEY CLUSTERED
    (
     [RightID] ASC
    )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    Regards,
    Alok Parmesh
    • Proposed as answer by Papy Normand Tuesday, April 10, 2012 9:19 PM
    Friday, November 7, 2008 12:15 PM
    Answerer
  • I stumbled upon this because I am having a similar issue.

    The proposed (and incorrectly accepted) answer looks flawed to me. The initial sample logic is setting up a standard linking table between two lists (users and items), such that [User] - 1 to * - [LoginRight] - * to 1 [Item].

    Setting the ItemID field in the LoginRight table to be unique breaks this structure, forcing many records in the [Item] table to have duplicate Item varchar values in order to provide a unique ItemID for each User that references the item.

    The correct answer for the posters issue is to make the Primary Key for the LoginRight table be BOTH the UserID and the ItemID fields, e.g.

    CONSTRAINT [PK_LoginRight] PRIMARY KEY CLUSTERED ([UserID],[ItemID])

    This has the added benefit of making the RightID field unnecessary and it may be removed.

    • Edited by Ross Hammer Thursday, April 5, 2012 7:23 PM Added further explanation and suggested answer
    • Proposed as answer by Ross Hammer Thursday, April 5, 2012 7:25 PM
    Thursday, April 5, 2012 7:10 PM
  • Hi please check the below given link, it may help you out.

    http://asp.net.bigresource.com/SQL-server-the-column-in-table-do-not-match-an-existing-primary-key-or-unique-constraint--S7EYmlJ38.html

    Wednesday, May 16, 2012 8:34 AM