'No primary or candidate keys in the referenced table?

I keep getting an error:

There are no primary or candidate keys in the referenced table 'TProducts' that match the referencing column list in the foreign key 'TProducts_TCategories_FK'."

When trying to establish a relationship between my products table and categories table. I have checked the spelling multiple times but can't seem to figure out why I keep getting this error. The rest of my tables are fine, and I used the same method.

CREATE TABLE TCategories
(
    intCategoryID   INTEGER      NOT NULL,
    strCategoryName VARCHAR(255) NOT NULL,

    CONSTRAINT TCategories_PK PRIMARY KEY (intCategoryID)
)

CREATE TABLE TProducts
(
    intProductID     INTEGER      NOT NULL,
    intVendorID      INTEGER      NOT NULL,
    intCategoryID    INTEGER      NOT NULL,
    strProductName   VARCHAR(255) NOT NULL,
    monCostofProduct MONEY        NOT NULL,
    monRetailCost    MONEY        NOT NULL,
    intInventory     INTEGER      NOT NULL,

    CONSTRAINT TProducts_PK PRIMARY KEY (intProductID)
)

ALTER TABLE TProducts    
    ADD CONSTRAINT TProducts_TCategories_FK 
        FOREIGN KEY (intCategoryID) REFERENCES TProducts (intCategoryID)
sqlsql-server


Solution 1:[1]

You are referencing to TProducts table and not TCategories table:

ALTER TABLE TProducts    ADD CONSTRAINT TProducts_TCategories_FK 
FOREIGN KEY ( intCategoryID ) REFERENCES TCategories( intCategoryID )

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Meyssam Toluie

Related Questions