您的位置: 首页  >  技术问答  >  不包含与引用匹配的主键或候选键

不包含与引用匹配的主键或候选键

分类: 技术问答 • 2022-09-16 15:55:05
问题描述：

SQL71516 ::被引用表'[dbo]。[MSTransaction]'不包含与外键中的引用列表匹配的主键或候选键。如果被引用的列是一个计算列，它应该被保留。不包含与引用匹配的主键或候选键

这是我收到的错误。任何解决方案下面是两个表的SQL代码：

CREATE TABLE [dbo].[MSOrderline] 
(
    [PurchaseID] NCHAR (200) NOT NULL, 
    [ProductID] NCHAR (200) NOT NULL, 
    [Quantity] INT   NULL, 

    CONSTRAINT [DoubleMS_PK] 
     PRIMARY KEY CLUSTERED ([PurchaseID] ASC, [ProductID] ASC), 
    FOREIGN KEY ([PurchaseID]) REFERENCES [dbo].[MSTransaction] ([PurchaseID]), 
    FOREIGN KEY ([ProductID]) REFERENCES [dbo].[MSProducts] ([ProductID]) 
); 

CREATE TABLE [dbo].[MSTransaction] 
(
    [TransactionID] NCHAR (200) NOT NULL, 
    [EmployeeID] NCHAR (200) NULL, 
    [CustomerID] NCHAR (200) NULL, 
    [PurchaseID] NCHAR (200) NOT NULL, 
    [Amount]  INT   NULL, 
    [TotalAmount] INT   NULL, 
    [TimeOfSale] NCHAR (200) NULL, 
    [DiscountID] NCHAR (200) NULL, 

    PRIMARY KEY CLUSTERED ([TransactionID] ASC), 

    FOREIGN KEY ([DiscountID]) REFERENCES [dbo].[MSDiscount] ([DiscountID]), 
    FOREIGN KEY ([EmployeeID]) REFERENCES [dbo].[MSEmployee] ([EmployeeID]), 
    FOREIGN KEY ([CustomerID]) REFERENCES [dbo].[MSCustomer] ([CustomerID]) 
);

我知道这已经被问过，但既没有在我的情况下工作或我不能就工作到了我的情况。

+3

长字符字段是主键这样一个坏主意。 –

答

正如Gordon Linoff所说，这些NCHAR(200)是集群密钥的可怕选择。

您必须先创建要引用的表。要使用PurchaseId作为关键字，它必须是unique。

在SQL Server实例：

CREATE TABLE [dbo].[MSTransaction] (
    [TransactionID] NCHAR (200) NOT NULL, 
    [EmployeeID] NCHAR (200) NULL, 
    [CustomerID] NCHAR (200) NULL, 
    [PurchaseID] NCHAR (200) NOT NULL unique, 
    [Amount]  INT   NULL, 
    [TotalAmount] INT   NULL, 
    [TimeOfSale] NCHAR (200) NULL, 
    [DiscountID] NCHAR (200) NULL, 
    PRIMARY KEY CLUSTERED ([TransactionID] ASC) 
    --,FOREIGN KEY ([DiscountID]) REFERENCES [dbo].[MSDiscount] ([DiscountID]), 
    --FOREIGN KEY ([EmployeeID]) REFERENCES [dbo].[MSEmployee] ([EmployeeID]), 
    --FOREIGN KEY ([CustomerID]) REFERENCES [dbo].[MSCustomer] ([CustomerID]) 
); 
CREATE TABLE [dbo].[MSOrderline] (
    [PurchaseID] NCHAR (200) NOT NULL, 
    [ProductID] NCHAR (200) NOT NULL, 
    [Quantity] INT   NULL, 
    CONSTRAINT [DoubleMS_PK] PRIMARY KEY CLUSTERED ([PurchaseID] ASC, [ProductID] ASC), 
    FOREIGN KEY ([PurchaseID]) REFERENCES [dbo].[MSTransaction] ([PurchaseId]), 
    --FOREIGN KEY ([ProductID]) REFERENCES [dbo].[MSProducts] ([ProductID]) 
);

rextester演示：http://rextester.com/ZVZD33282

参考有关聚集键进行更好的选择：

相关推荐