REFERENCING ISSUE

Question

CREATE TABLE Loan
(
    Customerid    Char(9)  NOT NULL,
    EquipmentCode Char(5)  NOT NULL,
    StartDate     DateTime NOT NULL,
    EndDate       DateTime NULL,
    
    CONSTRAINT CHK_ID   
        CHECK (Customerid LIKE '[ST][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]'),
    CONSTRAINT CHK_Date CHECK (EndDate >= StartDate),

    CONSTRAINT Loan_PK PRIMARY KEY(Customerid),

    CONSTRAINT CUST_FK 
        FOREIGN KEY(Customerid) REFERENCES CUST(CustomerID)
                ON UPDATE CASCADE
                ON DELETE CASCADE
);

CREATE TABLE EQUIPMENT
(
    EquipmentCode    CHAR(5)      NOT NULL,
    EquipmentName    VARCHAR(50)  NOT NULL,
    Description      VARCHAR(255) NULL,
    RentalRatePerDay DECIMAL(4,2) NOT NULL,

    CONSTRAINT EQP_PK PRIMARY KEY(EquipmentCode),
    CONSTRAINT CHK_Rate CHECK (RentalRatePerDay BETWEEN 4 AND 50),
    CONSTRAINT LOAN_FK 
        FOREIGN KEY(EquipmentCode) REFERENCES Loan(EquipmentCode)
                ON UPDATE CASCADE
                ON DELETE CASCADE
);

I was able to reference earlier on in the above loan.table but for the equipment table it states

Msg 1776, Level 16, State 0, Line 49
There are no primary or candidate keys in the referenced table 'Loan' that match the referencing column list in the foreign key 'LOAN_FK'.

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

Please advise.

Add a `KEY or INDEX` (does not need to be primary) on `EquipmentCode` in the `Loan` table, to satisfy the `... or candidate keys` portion of the error message. — Paul T., Sep 03 '21 at 19:15
`Loan_PK PRIMARY KEY(Customerid)` , a single row only for a Customer in the Loan, really? — Serg, Sep 03 '21 at 19:26
I'll guess that both StartDate and EndDate should be DATE and not DATETIME. And I will guarantee you that the PK definition for Loan is incorrect. You WANT good customers that will make multiple loans over time. But you DO want to prevent the lending of the same equipment in overlapping periods. — SMor, Sep 03 '21 at 21:02

score 1 · Answer 1 · answered Sep 08 '21 at 04:23

When we create a ForeignKey on a dependent table, it MUST refer back to the PrimaryKey (or a Unique Key see: https://stackoverflow.com/a/18435114/1690217) on the principal table.

In your case, EQUIPMENT is the principal end of the relationship, and Loan is the dependent. What this means is that the FK needs to be on the Loan table instead, so you should have this:

CREATE TABLE EQUIPMENT
(
    EquipmentCode    CHAR(5)      NOT NULL,
    EquipmentName    VARCHAR(50)  NOT NULL,
    Description      VARCHAR(255) NULL,
    RentalRatePerDay DECIMAL(4,2) NOT NULL,

    CONSTRAINT EQP_PK PRIMARY KEY(EquipmentCode),
    CONSTRAINT CHK_Rate CHECK (RentalRatePerDay BETWEEN 4 AND 50),
);

CREATE TABLE Loan
(
    Customerid    Char(9)  NOT NULL,
    EquipmentCode Char(5)  NOT NULL,
    StartDate     DateTime NOT NULL,
    EndDate       DateTime NULL,
    
    CONSTRAINT CHK_ID   
        CHECK (Customerid LIKE '[ST][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]'),
    CONSTRAINT CHK_Date CHECK (EndDate >= StartDate),

    CONSTRAINT Loan_PK PRIMARY KEY(Customerid),

    CONSTRAINT CUST_FK 
        FOREIGN KEY(Customerid) REFERENCES CUST(CustomerID)
                ON UPDATE CASCADE
                ON DELETE CASCADE,

    CONSTRAINT EQUIPMENT_FK 
        FOREIGN KEY(EquipmentCode) REFERENCES EQUIPMENT(EquipmentCode)
                ON UPDATE CASCADE
                ON DELETE CASCADE
);

score 0 · Answer 2 · answered Sep 03 '21 at 19:34

0

A foreign key references a primary key on another table. You have the FK on the wrong table

answered Sep 03 '21 at 19:34

NickW

8,430
2
6
19

Not only PKs, but FKs can reference unique constraints as well. – The Impaler Sep 03 '21 at 23:44

REFERENCING ISSUE

2 Answers2