Can not connect two tables (one to many): error table contains no primary or candidate key

I’m doing task in Visual Studio and I’m trying to connect tables between each other. But I faced with error which would be described in the bottom of this section: I created three tables.

Item table:

CREATE TABLE [dbo].[Item]
(
    [Id] INT NOT NULL PRIMARY KEY,
    [ItemName] nvarchar(100) NOT NULL,
    [ProductName] nvarchar(100) NOT NULL,
    CONSTRAINT [SK_ItemLink] FOREIGN KEY ([ItemName]) REFERENCES [OrderDetail]([Item])    
)

Order table:

CREATE TABLE [dbo].[Order]
(
    [OrderNum] NVARCHAR(100) NOT NULL PRIMARY KEY,
    [OrderDate] DATETIME,
    [OrderStatus] INT
)

OrderDetail table:

CREATE TABLE [dbo].[OrderDetail]
(
    [Order] NVARCHAR(100) NOT NULL PRIMARY KEY,
    [Item] nvarchar(100) NOT NULL, 
    [OrderDetailQuantity] INT,
    [OrderDetailPrice] FLOAT,
    CONSTRAINT [SK_OrderLink] FOREIGN KEY ([Order]) REFERENCES [Order]([OrderNum])
    
)

The relation between Order and OrderDetail is one to many. But I have an error message in Item table: SQL71516: The referenced table ‘[dbo].[OrderDetail]’ contains no primary or candidate keys that match the referencing column list in the foreign key. If the referenced column is a computed column, it should be persisted.

I tried to follow the recommendations that I found on the internet but none of them worked for me. Thank you in advance.

Answer

Your OrderDetail schema is wrong, you have the Order column which you are attempting to make the primary key and foreign key.

This table needs its own primary key and then a field for the foreign key, these should be separate. Something like

CREATE TABLE [dbo].[OrderDetail]
(
    [OrderDetailID] NVARCHAR(100) NOT NULL PRIMARY KEY,
    [OrderID] NVARCHAR(100) NOT NULL
    [Item] nvarchar(100) NOT NULL, 
    [OrderDetailQuantity] INT,
    [OrderDetailPrice] FLOAT,
    CONSTRAINT [SK_OrderLink] FOREIGN KEY ([OrderID]) REFERENCES [Order]([OrderNum])
    
)