Cannot connect foreign key to table in SQL Server database

vm0i2vca 于 2023-11-16 发布在 SQL Server

关注(0)|答案(1)|浏览(146)

For all tables in the db the fk are working but for this one.

CREATE TABLE Sales.SpecialOfferProduct
(
    SpecialOfferID INT NOT NULL,
    ProductID INT NOT NULL,
    rowguid uniqueidentifier NOT NULL,
    ModifiedDate DATETIME NOT NULL
    PRIMARY KEY(SpecialOfferID, ProductID)
)

I'm trying to connect to the next table:

CREATE TABLE Sales.SalesOrderDetail
(
    SalesOrderID INT FOREIGN KEY REFERENCES
    Sales.SalesOrderHeader(SalesOrderID),
    SalesOrderDetailID INT NOT NULL,
    ProductID INT 
        FOREIGN KEY REFERENCES Sales.SpecialOfferProduct(ProductID),
    SpecialOfferID INT 
        FOREIGN KEY REFERENCES Sales.SpecialOfferProduct(SpecialOfferID),
    CarrierTrackingNumber NVARCHAR(25),
    OrderQty SMALLINT NOT NULL,
    UnitPrice MONEY NOT NULL,
    UnitPriceDiscount MONEY NOT NULL,
    LineTotal INT NOT NULL,
    rowguid uniqueidentifier NOT NULL,
    ModifiedDate DATETIME NOT NULL

    PRIMARY KEY(SalesOrderID, SalesOrderDetailID)
)

This is the message I get when i try to run the code

Msg 1776, Level 16, State 0, Line 132
There are no primary or candidate keys in the referenced table 'Sales.SpecialOfferProduct' that match the referencing column list in the foreign key 'FK__SalesOrde__Produ__5812160E'.

sql-server

来源：https://stackoverflow.com/questions/77464124/cannot-connect-foreign-key-to-table-in-sql-server-database

1条答案

按热度按时间

nfs0ujit1#

The error message is quite clear. You try to reference the column ProductID only in your first foreign key, but there is no corresponding primary key that only uses this column.

There is only one for the combination of the two columns SpecialOfferID and ProductID .

Then the same issue appears for your attempt to create a foreign key for the column SpecialOfferID only.

You can fix this by first adding the two columns without keys and then afterwards apply a foreign key on both columns:

CREATE TABLE SalesOrderDetail(
               SpecialOfferID INT,
               ProductID INT,
               -- ...further columns... ---
               CONSTRAINT yourConstraint FOREIGN KEY (SpecialOfferID,
               ProductID) REFERENCES
               SpecialOfferProduct(SpecialOfferID, ProductID))

Pay attention to use the same order of the affected columns in both primary key and foreign key. If your primary key puts the SpecialOfferID first: PRIMARY KEY(SpecialOfferID, ProductID) , your foreign key must also do that.

If you try to do something like this: FOREIGN KEY (ProductID, SpecialOfferID) REFERENCES SpecialOfferProduct(ProductID, SpecialOfferID) , you will get an error message again.

That's why I also changed the order of the columns in the create table command, so they match the order in the create table command of the referenced table.

赞(0）回复(0）举报 2023-11-16

我来回答

Cannot connect foreign key to table in SQL Server database

1条答案

相关问题

热门标签

最新问答