Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'dbo.Products'
that match the referencing column list in the foreign key 'FK_Sales_Products'.
Msg 1750, Level 16, State 0, Line 1
Have you seen this error before? Hah, you just noticed it? Cool! Let's see
where the error is coming from.
I created two tables as Products and Sales. There is
a foreign key that forces the sales table to use only valid values for
ProductAbbreviation in Products table. To create a foreign key
constraint I can simply run this script:
ALTER
TABLE [dbo].[Sales]
WITH CHECK
ADD CONSTRAINT
[FK_Sales_Products] FOREIGN
KEY([ProductAbbreviation])
REFERENCES [dbo].[Products]
([Abbreviation])
GO
ALTER
TABLE [dbo].[Sales]
CHECK CONSTRAINT
[FK_Sales_Products]Here is the point. This foreign key
refers to a field called
Abbreviation. There is just one simple rule on this game. You
cannot make a reference to a field that is not unique. In better word your
foreign key can only refer to a field that has a unique constraint like Primary
Key or Unique Index. Now read the error again and you can simply understand what
is going on.
To have this script work you need to create a unique index on
Abbreviation field in Products table or make it a primary key that
guarantees uniqueness. So simply go to index management and create a unique
index on that field or run this script to create it like old fashioned
developers:
CREATE
UNIQUE NONCLUSTERED
INDEX unq_abbr ON
dbo. Products
(Abbreviation)
Then you can create your foreign key happily ever after.
Enjoy
Alireza