Add foreign key constraint

Question

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.

error msg seems quite clear. Referenced foreign keys must either be a PK or a unique column — Mitch Wheat, Jun 18 '22 at 14:51
[How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] — philipxy, Jun 18 '22 at 18:05
(Clearly,) This is a faq. Please before considering posting read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & sometimes limited to titles, but read the help. Google re googling/searching, including in Q&A at [meta] & [meta.se]. [ask] [Help] — philipxy, Jun 21 '22 at 03:35
Does this answer your question? [There are no primary or candidate keys in the referenced table that match the referencing column list in the foreign key](https://stackoverflow.com/questions/17879735/there-are-no-primary-or-candidate-keys-in-the-referenced-table-that-match-the-re) — philipxy, Jun 21 '22 at 03:39

score 0 · Answer 1 · answered Jun 18 '22 at 14:52

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)

score 0 · Answer 2 · edited Jun 18 '22 at 15:08

0

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.

edited Jun 18 '22 at 15:08

marc_s

732,580
175
1,330
1,459

answered Jun 18 '22 at 15:02

nbk

45,398
8
30
47

But if your column `ProductId` is unique already - then this begs the question of why the primary key has to be on *both* columns (as defined now). Most likely, `ProductId` alone would also make a valid (and probably better) primary key in this case ! – marc_s Jun 18 '22 at 15:09
@marc_s there a countless possibilities, why there is only one column refrenced and ProductId is probably not unique, that is why the user has a combined key, but the referenced table could only linked to the product – nbk Jun 18 '22 at 15:22
You've omitted `unique`. – allmhuran Jun 23 '22 at 19:34
If `ProductId` is not unique (per your comment) then it can't be referenced as a foreign key, because it's not a key. – allmhuran Jun 23 '22 at 19:40
a key i an index it must not be unique to be a reference as foreign key – nbk Jun 23 '22 at 19:52

score 0 · Answer 3 · answered Jun 18 '22 at 21:17

0

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)

answered Jun 18 '22 at 21:17

fillrate

1
1

Add foreign key constraint

3 Answers3