I'm trying to create an Associative Entity (N:N) in SQLite like this:
[ Pet —< VaccinePet >— Vaccine ]
And, I have the follow code of my associative entity:
CREATE TABLE VACINAPET (
vp_date TEXT NOT NULL,
vp_is_applied INTEGER DEFAULT 0,
fk_pet INTEGER,
fk_vaccine INTEGER,
FOREIGN KEY (fk_pet) REFERENCES pet (pet_id),
FOREIGN KEY (fk_vaccine) REFERENCES vaccine (vaccine_id),
PRIMARY KEY (fk_pet, fk_vaccine)
);
BUT, I'm getting an error:
foreign key mismatch – "VACCINEPET" referencing "vaccine": INSERT INTO
VACCINEPET (vp_date, vp_is_applied, fk_pet, fk_vaccine) VALUES
('23/05/2018', 0, 1, 1);
When I try to use the INSERT command:
INSERT INTO VACINAPET (vp_data, vp_is_aplicada, fk_pet, fk_vacina)
VALUES ('23/05/2018', 0, 1, 1);
What could be wrong? I'm not so good in database… 🙁
MASTER DETAIL: I have data in Pet table and Vaccine table, they are not empty
Best Answer
The error message "foreign key mismatch" does not indicate a constraint violation, but that your database schema is wrong.
Assuming that the
vaccine
table and thevaccine_id
column actually exist, the most likely reason is that the required index is missing, i.e., thatvaccine_id
is not the primary key (or at least unique).