Add foreign key constraint-CodePudding

Those are the two tables:

create table sales.SpecialOfferProduct 
(
    SpecialOfferID int not null,
    ProductID int not null,
    rowguid uniqueidentifier not null,
    ModifiedDate datetime not null,
    primary key (specialofferid, productid)
)

create table sales.SalesOrderDetail 
(
    SalesOrderID int not null,
    SalesOrderDetailId int not null,
    CarrierTrackingNumber nvarchar(25),
    OrderQty smallint not null,
    ProductId int not null,
    SpecialOfferId int not null,
    UnitPrice money not null,
    UnitPriceDiscount money not null,
    LineTotal as (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty], (0.0))),
    rowguid uniqueidentifier not null,
    ModifiedDate datetime not null,
    primary key (SalesOrderID, SalesOrderDetailId)
)

I'm trying to add a foreign key :

alter table sales.SalesOrderDetail
    add foreign key (ProductId) 
        references sales.SpecialOfferProduct(ProductId)

I get this error :

Msg 1776, Level 16, State 0, Line 180
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__4E88ABD4'.

Msg 1750, Level 16, State 1, Line 180
Could not create constraint or index. See previous errors.

CodePudding user response：

Very simple - a foreign key constraint must always reference the whole primary key of the table - you cannot reference "half a PK".....

Since your primary key on that table is defined as:

create table sales.SpecialOfferProduct 
(
    ....
    primary key (specialofferid, productid)
)

then obviously your foreign key must also include BOTH columns:

alter table sales.SalesOrderDetail
    add foreign key (SpecialOfferId, ProductId) 
        references sales.SpecialOfferProduct(SpecialOfferId, ProductId)

CodePudding user response：

Your primary key has two columns so it can't be used to reference by just one column from the second table.

You can either make a dual column reference, or you add a new index for only ProductId:

create index idx_SpecialOfferProduct_ProductID 
on sales.SpecialOfferProduct (ProductID )

And then add the new foreign key as you have it.

CodePudding user response：

Foreign key must reference on primary/secondary keys or unique index. Please, try something like this:

alter table sales.SalesOrderDetail
add foreign key (specialofferid, ProductId) 
references sales.SpecialOfferProduct(specialofferid, ProductId)

CodePudding user response：

I think we can't able to add foreign key for composite key column. So you need to create primary key with only one column and reference it to second table.