CODEWITHSUNDEEP
sqlsql-serverdatabase
SyracuseDave
SyracuseDave

Reputation: 3

Why is my foreign key constraint not working?

/*Create Purchased Products Table*/
create table Purchased_Products
(
School_ID INT not null,
Product_ID INT not null,
Purchased_Product_ID INT IDENTITY (1,1) not null,
Quantity INT,
Product_Status VARCHAR(1)

  constraint Purchased_Products_PK PRIMARY KEY (School_ID, Product_ID, Purchased_Product_ID),
  constraint Purchased_Products_FK1 FOREIGN KEY (School_ID) REFERENCES School(School_ID),
  constraint Purchased_Products_FK2 FOREIGN KEY (Product_ID) REFERENCES Product(Product_ID)
);

create table Credentials
(
Credential_ID INT IDENTITY (1,1) not null,
Purchased_Product_ID INT not null,
Username VARCHAR (30),
PPassword VARCHAR (30),
URL VARCHAR (100),
Remote_Username VARCHAR (30),
Remote_Password VARCHAR (30),
Remote_URL VARCHAR (100),
Notes VARCHAR (200),

  constraint Credentials_PK PRIMARY KEY (Credential_ID, Purchased_Product_ID),
  constraint Credentials_FK FOREIGN KEY (Purchased_Product_ID) REFERENCES Purchased_Products(Purchased_Product_ID)

);

Ok so I want to create this table, Credentials, but it gives me the error:

Msg 1776, Level 16, State 0, Line 1 There are no primary or candidate keys in the referenced table 'Purchased_Products' that match the referencing column list in the foreign key 'Credentials_FK'. Msg 1750, Level 16, State 0, Line 1 Could not create constraint. See previous errors.

It seems that it would allow me to do this but I cannot think of what I am doing. Maybe I am overlooking something?? Help??!!??

Upvotes: 0

Views: 2639

Answers (2)

StuartLC
StuartLC

Reputation: 107237

Foreign keys can only be created on columns with UNIQUE coumns like a primary key or unique index.

Table Purchased_Products currently only has one unique constraint, being the compound PK:

constraint Purchased_Products_PK 
   PRIMARY KEY (School_ID, Product_ID, Purchased_Product_ID)

And table Credentials is trying to enforce the foreign key on only one of these columns, viz

constraint Credentials_FK 
  FOREIGN KEY (Purchased_Product_ID) 
  REFERENCES Purchased_Products(Purchased_Product_ID)

Technically, you would need to repeat all the compound key columns in Credentials (i.e. School_ID, Product_ID, Purchased_Product_ID) and create the compound foreign key on these.

However, it seems that something is wrong in your table design - your choice of Primary Keys seems unfortunate, e.g. Purchased_Products has a candidate for a simple surrogate primary key: 

Purchased_Product_ID INT IDENTITY (1,1) not null

Similarly for Credentials, given that Credential_ID is already unique through the identity, there is no need to add Purchased_Product_ID to the PK. What you probably want is:

Table : Products
Primary Key : ProductId

Table : Purchased_Products
Primary Key : Purchased_Product_ID
Foreign Key : ProductID references Products(ProductId)

Table : Credentials
Primary Key : Credential_ID
Foreign Key : Purchased_Product_ID references Purchased_Products(Purchased_Product_ID)

etc.

Upvotes: 0

Aushin
Aushin

Reputation: 1208

Purchased_Products.Purchased_Product_ID needs to be explicitly UNIQUE or explicitly your primary key in order for it to be the reference column for your foreign key.

CREATE UNIQUE NONCLUSTERED INDEX IndexName 
ON Purchased_Products(Purchased_Product_ID)

Creating the unique index enforces uniqueness on the column (or set of columns if you have more than one column in your index) and SQL will let you use it as a reference for a foreign key.

Upvotes: 2

Related Questions