When i inserted data, it always says that foreign key mismatch.I can't find the reason for it?

Question

CREATE TABLE Student 
(
    Registration_No INTEGER  NOT NULL ,
    NIC  varchar(25),
    Student_Name varchar(255),
    Address varchar(255),
    Home_Telephone_No varchar(25),
    Mobile_Telephone_No varchar(25),
    Email varchar(255),
    Education varchar(255),

    PRIMARY KEY (Registration_No, NIC)
);

CREATE TABLE Payment 
(
    Registration_No INTEGER NOT NULL,
    Registration_fee decimal(15,2),
    Annual_subscription decimal(15,2),

    FOREIGN KEY(Registration_No) 
        REFERENCES Student(Registration_No)
);

Error:

Foreign key mismatch - "Payment" referencing "Student" 1:]1INSERT INTO Payment(Registration_No,Registration_fee,Annual_subscription )VALUES(10,100.50,500);

Foreign key mismatch - "Payment" referencing "Student"

A FK has to be able to point to a unique record in a different table. That's why you have to point to a PK or UNIQUE constraint column. For the same reason you cannot point to *part* of a PK, it is not identifiable enough. The PK of `Student` is `Registration_No,NIC`, so you have to point to both attributes. — HoneyBadger, May 28 '21 at 14:32

score 3 · Accepted Answer · answered May 28 '21 at 14:33

your primary key in table Student is not Registration_No alone but combination of Registration_No and NIC
now when you are referring it from other table you have referred only PART of the combination which is not unique causing the error, the solution is to match the FK to PK or at least a unique column of parent table for it to work as expected, read more here -> https://stackoverflow.com/a/18435114/8057127

When i inserted data, it always says that foreign key mismatch.I can't find the reason for it?

1 Answers1