CODEWITHSUNDEEP
sqlsql-servert-sql
Ang Yi Khoon
Ang Yi Khoon

Reputation: 11

REFERENCING ISSUE

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.

Upvotes: 1

Views: 41

Answers (2)

Chris Schaller
Chris Schaller

Reputation: 16574

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
);

Upvotes: 1

NickW
NickW

Reputation: 9788

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

Upvotes: 0

Related Questions