로그인
 
홈라이브러리학습다운로드지원커뮤니티포럼
Resources for IT Professionals >
> 
> 
The columns in table do not match an existing primary key or unique constraint

Proposed The columns in table do not match an existing primary key or unique constraint

  • 2008년 11월 5일 수요일 오후 12:00
     
     
    로그인하여 투표
    0

    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

     

모든 응답

  • 2008년 11월 5일 수요일 오후 2:40
    답변자
     
     
    로그인하여 투표
    0

    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
     
  • 2008년 11월 6일 목요일 오전 1:33
     
     
    로그인하여 투표
    0

    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]

     

     
  • 2008년 11월 6일 목요일 오전 9:04
    답변자
     
     
    로그인하여 투표
    0

    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
     
  • 2008년 11월 6일 목요일 오후 10:35
     
     
    로그인하여 투표
    0

    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

     

     
  • 2008년 11월 7일 금요일 오후 12:15
    답변자
     
     제안된 답변
    로그인하여 투표
    1

    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
     
  • 2012년 4월 5일 목요일 오후 7:10
     
     제안된 답변
    로그인하여 투표
    1

    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.

    • 편집됨 Ross Hammer 2012년 4월 5일 목요일 오후 7:23 Added further explanation and suggested answer
    • 답변으로 제안됨 Ross Hammer 2012년 4월 5일 목요일 오후 7:25
    •  
     
  • 2012년 5월 16일 수요일 오전 8:34
     
     
    로그인하여 투표
    0

    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