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

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

Answer 1

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

Answer 2

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]

 

Answer 3

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

Answer 4

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

 

Answer 5

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

Answer 6

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.

Answer 7

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